I know MacScript has been deprecated but as recently as January the following VBA routine worked, and at least in part it appears to, just cannot complete the whole routime.
Sub callApplescript8() ' in use part of Rebuild 01/28/2020
Dim sMyScript As String, CompN
sMyScript = "set userName to short user name of (system info)" & vbNewLine & "return userName"
CompN = MacScript(sMyScript)
Application.Wait (Now + TimeValue("0:00:05"))
MacScript ("run script file ""macintosh HD:library:scripts:Peter's Scripts in Test:GetUpDateDivPrices.scpt""")
End Sub
Now it stalls in the AppleScript routine when called from Excel but when run from Script Editor it seems to work perfectly . It gets as far as the format command "set number format of selection to “$.00"”. But then stalls in the repeat block.
The error returned in Excel is on the line " MacScript (“run script file ““macintosh HD:library:scripts:Peter’s Scripts in Test:GetUpDateDivPrices.scpt”””)" which is the caller for the AppleScript routine.
--GetUpdateDivPrices.scpt
--Get Current Prices & Calculate position
try
tell application "Safari" to activate
end try
--Set up Time Stamp
tell (current date) to get "" & (its month as integer) & "/" & its day & "/" & ((text -4 thru -1 of (its year as text) & " " & its hours as text) & ":" & its minutes as text)
set stamp to "Calculated on " & the result
tell application "Microsoft Excel"
activate object sheet "UpDateNotExercised"
set Colcnt to (((count of columns of used range of active sheet)))
set RowCnt to (first row index of (get end (last cell of column 1) direction toward the top))
--Add Heading
set value of cell 1 of column (Colcnt + 1) to stamp --Heading Text
set value of cell 2 of column (Colcnt + 1) to "Current Price"
set value of cell 2 of column (Colcnt + 2) to "Option Not Exercised"
set bold of font object of cell 1 of column (Colcnt + 1) to true
--Add Title & Center across Selection & Color interior Yellow
set Title1 to (get address of (cell 1 of column (Colcnt + 1))) & ":" & (get address of (cell 1 of column (Colcnt + 3)))
tell range (Title1) to set horizontal alignment to horizontal align center across selection
set color index of interior object of range (Title1) to 6
--Format Columns to Currency
set ColAddress1 to (get address of column (Colcnt + 1))
set ColAddress2 to (get address of column (Colcnt + 3))
range (ColAddress1 & ":" & ColAddress2) select
set number format of selection to "$.00"
repeat with i from 3 to (RowCnt - 38) -- 17
set SYM to value of cell i of column 1
set CurPrice to my CurrentPrice(SYM)
try
set value of cell i of column (Colcnt + 1) to CurPrice
if CurPrice < value of cell ("B" & i) and CurPrice > 0 then
set value of cell i of column (Colcnt + 2) to (CurPrice - (value of cell ("B" & i)) + (value of cell ("C" & i)) + (value of cell ("H" & i))) * (value of cell ("E" & i))
end if
end try
set CurPrice to ""
end repeat
end tell
on CurrentPrice(SYM)
tell application "Safari"
-- "Load Yahoo"
set URL of document 1 to "https://finance.yahoo.com/quote/" & SYM & "/options?p=" & SYM
--Loop until Web Page is loaded
set CheckURL to ""
repeat until CheckURL contains SYM
try
tell application "Safari" to set CheckURL to URL of current tab of window 1
if CheckURL contains SYM then
delay 3 --time page to load
end if
exit repeat
end try
end repeat
--try
set LSP to my lastsaleprice()
try
set CurPrice to LSP
return CurPrice
end try
end tell
end CurrentPrice
on lastsaleprice()
tell application "System Events"
tell process "Safari"
repeat 5 times
try
set LastSale to get value of static text 1 of group 1 of group 1 of group 1 of group 13 of group 1 of UI element 1 of scroll area 1 of group 1 of group 1 of tab group 1 of splitter group 1 of window 1
return LastSale
exit repeat
end try
delay 0.5
end repeat
end tell
end tell
end lastsaleprice
Any thoughts suggestions greatly appreciated
Peter