Run AppleScript from Excel

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

Just discovered it does not work when called from the menu bar. So only working when called from Script Editor. I restored my machine in case I had a problem there same issue . It does cycle through each stock just does not enter the data into Excel.

Should be… When you do GUI Scripting you need to tell the application to become frontmost.

on lastsaleprice()
	tell application "System Events"
		tell process "Safari"
                     set frontmost to true --> its make Safari frontmost, 
			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

Thanks for that suggestion but I am afraid it has not helped. To try and test it in a simplistic way I loaded a Safari web page in finance.yahoo and ran the script below which as you can see includes your recommendation. Run from a the menu bar as a complied scrip the dialog box returns 0 run from script editor it returns the correct value.In case you want to try and duplicate my results an example URL is https://finance.yahoo.com/quote/CHD/options?p=CHD. The CHD is a stock symbol,


set LastSale to 0
tell application "System Events"
	tell process "Safari"
		set frontmost to true --> its make Safari frontmost, 
		set y to 0
		repeat until LastSale > 0
			try
				set LastSale to 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
				exit repeat
			end try
			delay 0.5
			set y to y + 1
			if y = 10 then exit repeat
		end repeat
	end tell
end tell
display dialog LastSale

I test your script… and it return a value in dialog box, I was running from Script Editor.

I test if I could get the data using do JavaScript but the console.log in Safari complain
about content blocking… I will give it a try again…

Maybe not what you are looking for but scraping data is easy with this to library.

This is python library to scraping data.
https://pypi.org/project/beautifulsoup4/

This one has good documentation.

or

This is node library to scraping data.
https://github.com/puppeteer/puppeteer

This is new to me but its not hard to find how it works.

Yes that is the issue I had run from Script Editor it works. It is part of a longer script and I discovered I had the same issue running it. So I started to break it down and saved the first leg as an application through Automator and after I had gone though enabling it through assistive devices it worked.

Again thank you for your trouble hopefully I will be able to run the whole thing through Automator.

Maybe a good advice is to make a run handler when you use Script Menu… I do that.

But I also have script that only work in Script Menu if its applet, ex Takaaki WebKit Utilites.