Search Excel data and copy to another excel document.

I am new to applescript and have no idea how to make this happen so I was looking for some help.

I am exporting some information from a student information system to an excel file. In that export one of the columns is a course number. I need to take that number and lookup what the course name is in another excel file and place that information in the column next to the course number.

I would like to make an Automator workflow so one of our secretaries can quickly fix a file that needs to have the course names in it instead of the number. Unfortunately we can’t just export the name of the course initially, the only option we have is the number.

Any help with this would be great.

Hi there Avsfan77,

Welcome to MacScripter!

Just a quick question regarding the Excel Doc containing the course names.
Are all the course numbers in one column and the course names in another?

It might be a case that you don’t need to find the relevant course by the number.
The course could be read into a list and then a simple replace done in the student Excel doc.


Yes the course name is in Column A and the course number is in Column B in the course excel sheet.

In the Other Excel document we have the student ID in A, student name in B, course number in C and student grade in D. I either need to add the course name to column E or replace the data in column C to the name either way will work.

Hey There,

There are a fair number of Excel scripts on MacScripter.

Google: “Microsoft Excel” site:

Be prepared for some frustration as you learn how to do this.

I’ve spent many hours wrestling with the beast and have only scratched the surface.

A couple of examples to get you started:

# Some examples of working with a range.
tell application "Microsoft Excel"
	tell active sheet of active workbook
		value of column 2 of used range
		formula of column 2 of used range -- get numbers as text instead of reals.
		areas of column 2 of used range
	end tell
end tell

# Find some text and get the value of the cell in the next column.
set findText to "182234"
tell application id ""
	tell active workbook
		tell active sheet
			tell used range
				set resultRange to find what findText without match case
				set foundValue to value of (get offset resultRange column offset 1)
			end tell
		end tell
	end tell
end tell

Even though old this is worth having:


{ MacBookPro6,1 · 2.66 GHz Intel Core i7 · 8GB RAM · OSX 10.11.1 }

Hi there,

Don’t know if this is a bit late but here’s what I ended up with.

To keep it simple I’ve tested this with 2 Excel docs, courses.xlsx and pupils.xlsx.
In the courses.xlsx doc, column A had the course name and column B the course number on a sheet called Sheet1.
In the pupils.xlsx file I had the pupil name in column A and the course number in B, to keep it simple, on a sheet called Sheet1.

I did the find/replace outside of Excel. The result is copied to the clipboard and could be pasted back into Excel.
It’s only been tested in the scenario I had on my Mac which worked ok. Obviously this would need testing thoroughly in a new scenario prior to running on live data. A duplicate of the live data doc would be a good idea.

I wonder if this could be done with an Excel formula instead?

tell application "Microsoft Excel"
	activate object worksheet "Sheet1" of workbook "courses.xlsx"
	set courseList to formula of used range of active sheet
	activate object worksheet "Sheet1" of workbook "pupils.xlsx"
	set thePupilsList to formula of used range of active sheet
end tell

set theUpdatedList to ""
repeat with thisPupil in thePupilsList
	set thisCourseIndex to item 2 of thisPupil
	set theCourseName to getCourse of thisCourseIndex from courseList
	set theUpdatedList to theUpdatedList & item 1 of thisPupil & tab & theCourseName & return
end repeat

set the clipboard to theUpdatedList

to getCourse of i from l
	repeat with n from 1 to count l
		if (item 2 of (l's item n) is i) then return item 1 of (l's item n)
	end repeat
end getCourse


Awesome that worked for me.

Thank you for your help.