Applescript Loop/Repeat Functions in Excel with Firefox

Question

I'm new to applescript and any help on this would be highly appreciated. I think this would be a good challenge for someone with more skills than me. All that is described has been done in Snow Leopard with MS Office 2011.

I have a list of URLs (starting in cell Q2) and I've got applescript to execute the following series of tasks:

Open MS Excel
Create new workbook
Copy URL from MS Excel cell Q2.
Paste to Firefox address bar and go.
Click Firefox menu bar function 'View1'(from an add-on)
Click Firefox menu bar function 'View2'(from an add-on)
Click Firefox menu bar function 'Copy all Tables'(from an add-on)
Create new workbook in Excel
Paste copied text to new workbook cell A1
Save new workbook as workbook002.xlsx
Close workbook

I've put together the script below for this and it works. The trouble is that i cant make it repeat. The repeat function is required to repeat the execution of the whole script, first changing cell Q2 to Q3 and so on up to the point where the last cell contains the value 0 which is the signal to end the loop, and save each workbook with names in a sequential order (workbook002 then workbook003 etc). I don't think the firefox part needs to be changed because the steps are always the same.

Here's the script:

do shell script "open -a /Applications/Microsoft\\ Office\\ 2011/Microsoft\
\ Excel.app ~/Desktop/KospiSection2.xlsx"
tell application "Microsoft Excel"
set sourceBook to workbook "Section2.xlsx"
set sourceRange to get range "Q2" of sheet 1 of sourceBook
copy range sourceRange
end tell
tell application "Firefox"
activate
tell application "System Events"
tell process "Firefox"
click menu item "PasteGo" of menu "Tools" of menu bar 1
delay 3
click menu item "View1" of menu "View" of menu bar 1
delay 10
click menu item "View2" of menu "View" of menu bar 1
delay 2
click menu item "Copy all Tables (2)" of menu "Edit" of menu bar 1
delay 3
click menu item "Close Tab" of menu file of menu bar 1
end tell
end tell
end tell
tell application "Microsoft Excel"
make new workbook
delay 2
tell active sheet of active workbook
paste worksheet destination range "A1"
delay 2
end tell
end tell
do shell script "save as -a /Applications/Microsoft\\ Office\\ 2011/Microsoft\\
Excel.app ~/Desktop/workbook002.xlsx"

Sincere thanks if anyone can figure out how to do this. I've been breaking my head over this for a long long time. p.s. If anyone knows a good book on running excel with applescript, please tell me.

Thanks again!

Zitoun Zitoun · Accepted Answer · 2013-07-25T07:44:42

All right... you need to things :

identify the recursion, i.e. the place where the program changes... You mentionned it :

range "Q2" of sheet 1 of sourceBook should evolve in range "Q3" of sheet 1 of sourceBook

In applescript terms, you will write the line like this:

set sourceRange to get range ("Q" & i) of sheet 1 of sourceBook

and work on the "i" variable. Note that this "i" variable will also appear in the name of the workbook

"workbook002.xlsx" becomes ("workbook00" & i & ".xlsx")

you must also identify where the recursion stops. Meaning, in our case, the max value of "i". Let's say it's 20 for our example. Your code becomes then :

tell application "Microsoft Excel"

set sourceBook to workbook "Section2.xlsx"

repeat with i from 2 thru 20
   set sourceRange to get range ("Q" & i) of sheet 1 of sourceBook
   ...
   set outputFileName to "workbook00" & i & ".xlsx" -- Well there should be some work on the name so that it looks like what you expect, but that's another thing)
   save workbook as active workbook filename ({path to desktop folder as string, outputFileName} as string) with overwrite -- keep the 'save as' action within the loop, and within the "tell Excel"     
   end -- repeat

end tell

This should do the trick...

Applescript Loop/Repeat Functions in Excel with Firefox

1 Answers