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.
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.
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 "com.microsoft.Excel"
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
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
0
end getCourse