Yes, I realize you cannot do what the subject stipulates. However, given that very unfortunate fact, I have constructed the usual:
on open theFiles
if ((count of theFiles) > 1) then
beep
display dialog "Drop only one Microsoft Excel Spreadsheet on me!" with icon stop ¬
buttons {"OK"} default button "OK"
else
set theWorkbook to item 1 of theFiles
set fileCreator to file creator of (info for theWorkbook)
set fileType to file type of (info for theWorkbook)
if (fileCreator is not equal to "XCEL" or fileType does not contain "XLS") then
beep
display dialog "Drop only a Microsoft Excel Spreadsheet on me!" with icon stop ¬
buttons {"OK"} default button "OK"
else
calculateWorksheet(theWorkbook)
end if
end if
end open
on quit
continue quit
end quit
Nothing unusual there … what I am looking for is the calculateWorksheet(theWorkbook) procedure in which I currently have the following:
on calculateWorksheet(theWorkbook)
my getWorkbook(theWorkbook) -- opens theWorkbook in Excel
my getNamedRanges() -- loads string values into global range names, e.g., gBilledByProvider and gOwedByPatient
(*
VBA equivalent:
=IF(NOT(ISBLANK(Billed_by_Provider)), 'Personal Macro Workbook'!TotalOwedBill(Billed_by_Provider, Accept_Prime_Assignment, Allowed_by_Prime), "")
*)
set ifNotBlank to "=if (not isMissing(GetValue(GetCallerRow(), gBilledByProvider))) then " & return
set elseBlank to return & "else" & return & "" & return & "end if" & return
my setRangeFormula(ifNotBlank & "CumByCY(gDate, gType, \"D\", gOwedByPatient, R[-1]C)" & elseBlank, gCumMedPaidCY)
-- etc
end calculateWorksheet
on setRangeFormula(theFormula, inRange)
tell application "Microsoft Excel"
set thisRange to get range inRange of active sheet
tell thisRange
repeat with r from 1 to count rows
set formula of row r to theFormula
end repeat
end tell
end tell
end setRangeFormula
on GetValue(inRow, rangeName)
tell application "Microsoft Excel"
set rng to my GetRange(rangeName)
tell rng to return contents of row inRow
end tell
end GetValue
on GetRange(rangeName)
tell application "Microsoft Excel"
return (get range rangeName of active sheet)
end tell
end GetRange
on GetCallerRow()
tell application "Microsoft Excel"
tell active sheet
-- return Application.Caller.Row [VBA]
end tell
end tell
end GetCallerRow
I know that the GetCallerRow function is still not solved, as a matter of fact, I’m not sure if it is solvable in AppleScript. Nevertheless, let’s continue. My overall worksheet consists of 19 columns, with the first 6 consisting of user input text and numeric data. The remaining 13 columns consist of formulae that generate numeric results based on the info in the first 6 columns.
As I stipulated at the very top, I know that these formulae cannot be AppleScript formulae … so does anyone have any suggestions, such as iterate over all of the last 13 columns, one cell at a time. If I do that, speed wise I would need to wait big time.