So I had this small problem that I wanted to share my solution in case anyone else had a similar problem. I was receiving a bunch of worksheets on a a weekly basis and I needed them all converted into one workbook while still retaining their titles.
I tried to script the whole procedure but couldn’t get passed one part, so I had to use Visual basic within Excel.
I was trying to get AS to do the macro, but couldn’t figure it out fast enough.
Hope that helps someone!
display dialog "Did you open the file Text.xls first and then all your other workbooks?"
set numberTimes to text returned of (display dialog "How many worksheets do you have?" with title "Number of Worksheets" default answer "")
activate application "Microsoft Excel"
tell application "System Events"
tell process "Microsoft Excel"
repeat numberTimes times
--accesses the macros window
tell menu item "Macro" of menu 1 of menu bar item "Tools" of menu bar 1
click menu item 1 of menu 1
end tell
keystroke return
--access window other than the new worksheet
click (get 1st menu item of menu 1 of menu bar item "Window" of menu bar 1 whose name begins with "1")
end repeat
end tell
end tell
===This is the VBA code - Plug it into a module within Excel. You may have to Insert>Insert New Module===
Sub Mover2()
ActiveSheet.Move Before:=Workbooks(“Test.xls”).Sheets(1)
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
End Sub
set sourceFiles to (choose file with prompt "Choose source files" of type {"XLS6", "XLS7", "XLS8"} with multiple selections allowed without invisibles)
tell application "Microsoft Excel"
activate
set targetBook to make new workbook
repeat with oneFile in sourceFiles
set sourceBook to open workbook workbook file name (oneFile as text)
repeat with copy_sheet in (get worksheets of sourceBook)
if value of used range of copy_sheet is not "" then
copy worksheet (contents of copy_sheet) after last sheet of targetBook
end if
end repeat
close sourceBook saving no
end repeat
end tell
Whew, thats brilliant. Thats what I was trying to do in the beginning, but couldn’t figure it all out.
The only thing I had to change was this:
set sourceFiles to (choose file with prompt "Choose source files" of type {"XLS6", "XLS7", "XLS8", "XLS"} with multiple selections allowed without invisibles)
I presume the files that I’m importing are of a different version of what my version is, so that fixes my little problem.
My hat goes off to you StefanK!
I have to ask, how long have you been scripting and what did you read/how did you learn?
oh yea, i forgot to ask, but can you explain how this works and how you came to this conclusion?
I figure the more I can understand of this stuff, the more I can help and also, the less I’ll have to ask.
Thanks again!
about one and a half year, but very intensely,
I read (and studied) Matt Neuburg’s The Definitive Guide and Hanaan Rosenthal’s The Comprehensive Guide
and a lot of trial & error
It opens the files one by one and copies the sheets (which are not empty) to the destination file
I read the dictionary of Excel and the Excel 2004 AppleScript Reference
