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