Tuesday, September 29, 2020

#1 2020-08-05 04:25:50 pm

MitchBVI
Member
Registered: 2006-02-14
Posts: 197

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.

Applescript:


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.

Applescript:


--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

Offline

 

#2 2020-08-05 04:42:50 pm

MitchBVI
Member
Registered: 2006-02-14
Posts: 197

Re: Run AppleScript from Excel

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.

Offline

 

#3 2020-08-06 01:23:02 am

Fredrik71
Member
Registered: 2019-10-23
Posts: 399

Re: Run AppleScript from Excel

MitchBVI wrote:

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



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

Applescript:

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


I could teach you to cook but I couldn't do anything if you do not have desire or commitment for it.

Offline

 

#4 2020-08-06 11:08:17 am

MitchBVI
Member
Registered: 2006-02-14
Posts: 197

Re: Run AppleScript from Excel

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,

Applescript:


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

Offline

 

#5 2020-08-06 02:06:22 pm

Fredrik71
Member
Registered: 2019-10-23
Posts: 399

Re: Run AppleScript from Excel

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.


I could teach you to cook but I couldn't do anything if you do not have desire or commitment for it.

Offline

 

#6 2020-08-06 02:29:41 pm

MitchBVI
Member
Registered: 2006-02-14
Posts: 197

Re: Run AppleScript from Excel

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.

Offline

 

#7 2020-08-06 02:33:54 pm

Fredrik71
Member
Registered: 2019-10-23
Posts: 399

Re: Run AppleScript from Excel

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.

Last edited by Fredrik71 (2020-08-06 02:43:05 pm)


I could teach you to cook but I couldn't do anything if you do not have desire or commitment for it.

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)