Hello All,
First I apologize in advance for posting such a lengthy script here, but I’m having trouble figuring out why it’s hanging. You may skip this long first script and read down to the second script, which is just a small block. Here is the script as originally written, which works as it is supposed to:
global PayrollSheetNum
set PayrollSheetNum to 1
set MonthList to {January, February, March, April, May, June, July, August, September, October, November, December}
repeat with i from 1 to 12
set AccrueMonth to item i of MonthList
MonthlyZero(AccrueMonth)
end repeat
repeat with i from 1 to 12
set AccrueMonth to item i of MonthList
MonthlyAccrue(AccrueMonth)
end repeat
on MonthlyZero(AccrueMonth)
tell application "Microsoft Excel"
activate
set AccrueSheet to ("" & (AccrueMonth as text) & "")
set formula of range "C1, B14, B16, B18, B27, B29, B31, B35, B37" of sheet AccrueSheet to 0
end tell
end MonthlyZero
on MonthlyAccrue(AccrueMonth)
set AccrueWeeks to ""
set AccrueBase to ""
set AccrueVacation to ""
set AccrueHoliday to ""
set AccrueFedTax to ""
set AccrueFICA to ""
set AccrueMedicare to ""
set AccrueStateTax to ""
set AccrueSDI to ""
tell application "Microsoft Excel"
activate
set AccrueSheet to ("" & (AccrueMonth as text) & "")
repeat with i from PayrollSheetNum to 24
set PayrollSheet to ("Payroll (" & (i as text) & ")")
tell sheet PayrollSheet
if (get value of range "D42") is not "" then
set SheetMonth to month of (get value of range "D42")
if SheetMonth is equal to AccrueMonth then
set AccrueWeeks to AccrueWeeks + (get value of range "C1")
set AccrueBase to AccrueBase + (get value of range "B14")
set AccrueVacation to AccrueVacation + (get value of range "B16")
set AccrueHoliday to AccrueHoliday + (get value of range "B18")
set AccrueFedTax to AccrueFedTax + (get value of range "B28")
set AccrueFICA to AccrueFICA + (get value of range "B30")
set AccrueMedicare to AccrueMedicare + (get value of range "B32")
set AccrueStateTax to AccrueStateTax + (get value of range "B34")
set AccrueSDI to AccrueSDI + (get value of range "B36")
tell sheet AccrueSheet
set formula of range "C1" of sheet AccrueSheet to AccrueWeeks
set formula of range "B14" of sheet AccrueSheet to AccrueBase
set formula of range "B16" of sheet AccrueSheet to AccrueVacation
set formula of range "B18" of sheet AccrueSheet to AccrueHoliday
set formula of range "B27" of sheet AccrueSheet to AccrueFedTax
set formula of range "B29" of sheet AccrueSheet to AccrueFICA * 2
set formula of range "B31" of sheet AccrueSheet to AccrueMedicare * 2
set formula of range "B35" of sheet AccrueSheet to AccrueStateTax
set formula of range "B37" of sheet AccrueSheet to AccrueSDI
end tell
else
if SheetMonth is greater than AccrueMonth then
exit repeat
end if
end if
else
exit repeat
end if
end tell
end repeat
set PayrollSheetNum to i
end tell
end MonthlyAccrue
Now, I am trying to cause the script to exit the repeat if it encounters an empty date cell (“D42”) in the payroll sheet during the repeat that calls MonthlyAccrue(AccrueMonth). Here is the block that changes in the new script:
tell application "Microsoft Excel"
activate
repeat with i from 1 to 12
set AccrueMonth to item i of MonthList
set PayrollSheet to ("Payroll (" & (PayrollSheetNum as text) & ")")
if (get value of range "D42") is not "" then
run MonthlyAccrue(AccrueMonth)
else
exit repeat
end if
end repeat
end tell
Everything else in the script is identical. When I run the new script to test it, I now get the message “Microsoft Excel got an error: Can’t continue MonthlyAccrue.” One big difference is that I added a “tell application “Microsoft Exel” activate” statement. Does this mean that the call to the MonthlyAccrue subroutine is somehow being interpreted as a command to Excel instead? Do I need to end the “Microsoft Excel” tell earlier? I’m stumped.
Thanks in advance for any help. Sorry again for the lengthy script.
Tom Carlson