Hello,
I’ve recently begun writing applescripts and have run into a puzzle that I can’t seem to solve.
I’ve written the following code to apply formatting to multiple ranges in Excel 2008. The ranges are named in a list at the beginning of the script, when it’s run, it’s supposed to check to see if the named range exists, if it does, then it calls a specific area inside the range, applies the formatting and repeats. If the range does not exist on the worksheet, then it’s supposed to just move on to the next range.
I’m able to get the repeat loop to run successfully on the first run, however, as soon as the script runs the second iteration, it returns an error.
I thought the problem was occurring when applescript tried to identify the second item (range name) in the list, however, the log of the script shows that it is successfully moving to the next list item, it just won’t complete the same commands inside of the repeat loop.
I know it’s not the prettiest script, but is it something about the way I’ve written it? or is it something about apple and microsoft trying to play nice together?
here is the code i’ve written:
set mylist to {"one", "two", "three", "four", "five"}
log mylist
tell application "Microsoft Excel"
activate
activate top
try
repeat with i in mylist
if exists named item i in active workbook then
set nRange to union range1 (column 4 of range i) range2 (column 5 of range i) range3 (column 6 of range i) range4 (column 7 of range i)
clear contents nRange
set value of first row of first column of nRange to "First"
set value of first row of second column of nRange to "Second"
set noteRange to union range1 (column 6 of range i) range2 (column 7 of range i)
select noteRange
(* This bit here tells the computer to activate a shortcut in Excel that I've programmed because I could not get the "merge with across" command to work in Applescript! *)
tell application "System Events"
keystroke "m" using {command down, option down}
end tell
tell application "Microsoft Excel"
set value of first row of first column of noteRange to "Notes"
log "variable values at the end of repeat loop"
log nRange
log noteRange
end tell
end if
end repeat
on error
set screen updating to true
display dialog "there was an error creating your document. Please try again" buttons {"Ok"}
end try
end tell
end
Thanks for any help!
Model: Macbook
AppleScript: 2.2.1
Browser: Firefox 3.0.5
Operating System: Mac OS X (10.5)
Ok, I’ve been messing with this script a bit and I think I can explain why the script is failing, but I don’t understand why it’s doing what it’s doing.
when I isolated the individual instances of the repeat loop, applescript would run through the first time no problem. However, when it started on the second loop, it failed to store a value to the variable “nRange”
I changed the script so that each individual instance would assign values to a new variable like this:
tell application "Microsoft Excel"
activate
activate top
try
-- First Loop
set nRange to union range1 (column 4 of range "first") range2 (column 5 of range "first") range3 (column 6 of range "first") range4 (column 7 of range "first")
log nRange
clear contents of nRange
set value of first row of first column of nRange to "From"
set value of first row of second column of nRange to "Pulled"
set noteRange to union range1 (column 6 of range "first") range2 (column 7 of range "first")
select noteRange
-- This part of the code is to merge two columns of the range across because I could not get the command "merge range with across" to work
tell application "System Events"
keystroke "m" using {command down, option down}
end tell
tell application "Microsoft Excel"
set value of first row of first column of noteRange to "Notes"
end tell
-- End First Loop
-- Second Loop
set sRange to union range1 (column 4 of range "second") range2 (column 5 of range "second") range3 (column 6 of range "second") range4 (column 7 of range "second")
log sRange
clear contents of sRange
set value of first row of first column of sRange to "From"
set value of first row of second column of sRange to "Pulled"
set sNoteRange to union range1 (column 6 of range "second") range2 (column 7 of range "second")
select sNoteRange
tell application "System Events"
keystroke "m" using {command down, option down}
end tell
tell application "Microsoft Excel"
set value of first row of first column of sNoteRange to "Notes"
end tell
-- End Second Loop
on error
set screen updating to true
log nRange
log vRange
display dialog "there was an error creating your show notes. Please try again" buttons {"Ok"}
end try
end tell
end
When the script errors, it says that the variable “vRange” is not defined, however, the variable “nRange” is defined and the syntax for the two loops is literally identical, the only thing that changes are the ranges the loop calls. WTF?
I don’t understand why it’s not saving the value of the union of ranges the second time if it works on the first time.
Hi,
the variable vRange is not defined because it’s not mentioned at all in the script.
If you use a try block to catch errors it could help to display also the real error message, (although MS errors are often not very significant)
.
on error e
set screen updating to true
log nRange
log e
display dialog "there was an error creating your show notes. Please try again" buttons {"Ok"}
end try
.
Hi stefan,
that was a typo on my part… I changed the script to first and second to make it easier to read, i guess that has the opposite effect!
The two variables set to log on error are “nRange” and “sRange” and “sRange” is the name of the variable throughout the second loop. I corrected the typo in my script, however the same error occurs, applescript says “sRange” is not defined
I included the code
on error e
log e
in the script and now it returns an error of “Microsoft Excel got an error: Can’t continue union.”
any thoughts?
sorry, I’ve never used those union commands and the script doesn’t work with an empty Excel sheet
well,
setting the script to actually log the error when it happened helps because at least now I have a better idea of what it is I’m trying to fix!
Thanks Stefan
For anyone who might be interested,
I was able to fix the script this afternoon by making some changes.
It looks like this now:
set mylist to {"audio", "Video", "Staging", "Lighting", "Scenery", "Miscellaneous", "Miscellaneous2", "Miscellaneous3"}
log mylist
tell application "Microsoft Excel"
activate
activate top
try
repeat with i in mylist
if exists named item i in active workbook then
set nRange to intersect range1 i range2 range "D:G"
activate nRange
clear contents nRange
set value of first row of first column of nRange to "From"
set value of first row of second column of nRange to "Pulled"
set noteRange to intersect range1 nRange range2 range "F:G"
select noteRange
set value of first row of first column of noteRange to "Notes"
tell application "System Events"
keystroke "m" using {command down, option down}
end tell
end if
end repeat
on error e
log e
log nRange
set screen updating to true
display dialog "there was an error creating your show notes. Please try again" buttons {"Ok"}
end try
end tell
end