I’m very very new to apple scripting. I found a script online and adapted it to suit my needs except I need some help in tweaking it a little further and it is way above my understanding.
I am using Lion, Version 10.7.5, with Office for Mac 08.
What I need this script to do is to take the header, which is always in the first row, from the first of the spreadsheets (theFiles) that this script combines and add it once to the new workbook before adding any of the spreadsheet data.
As a side note the same header is in every spreadsheet and is always the first row of the sheet. I just only need one instance of it. I assume it would be easier to pull it from the first since that is the first one that it imports to the new workbook.
Any help or advice would be greatly appreciated.
Here is my script:
set sourceFolder to (path to desktop folder as Unicode text) & “XLS_TESTING:”
set theOutputPath to (path to desktop folder as Unicode text) & “Combined.xls”
tell application “Finder” to set theFiles to (every file of folder sourceFolder whose name extension is “xls”) as alias list
tell application “Microsoft Excel”
set theWkbk to make new workbook
set view of active window to normal view
set the date 1904 of theWkbk to not the date 1904 of theWkbk
repeat with oneFile in theFiles
set thisName to name of (info for oneFile)
tell application "Microsoft Excel"
open oneFile
tell sheet 1 of theWkbk
set range_value to value of used range
if class of range_value is list then
set firstUnusedCellA to (count range_value) + 1
else
set firstUnusedCellA to 1
end if
--set value of cell 1 of row firstUnusedCellA to thisName
end tell
set destinationRange to "A" & (firstUnusedCellA)
copy range (range "A2:Z90" of sheet "Sheet1" of workbook thisName) destination range destinationRange of sheet 1 of theWkbk
close workbook thisName saving no
end tell
end repeat
--Trying to get it to save with the below code but saving as sheet one
--save active workbook in theOutputPath & "XLS"
end tell