How to call an applescript application from Office 2011 Excel VBA

Question

Level 1

0 points

How to call an applescript application from Office 2011 Excel VBA

All:

I have been pounding my head how to implement a working VBA application into the Mac environment. I have tried a number of approaches calling applescript from VBA. I would like to call my applescript application and pass one argument. My applescript application is as follows:

<code>
on run argv
set theURL to (item 1 of argv)
set p_path to do shell script "echo ~"
set m_path to POSIX file p_path
set theName to text -((offset of "/" in (reverse of characters of theURL) as text) - 1) thru -1 of theURL
set theFile to (p_path & "/Downloads/" & theName)
set macPath to (POSIX file theFile)
do shell script "/usr/bin/curl " & theURL & " -o " & quoted form of POSIX path of macPath
end run
</code>

My last attempt within VBA is:

<code>
Sub SaveMetaDataFile(URL As String, shortFileName As String)
Dim scriptToRun As String
Dim posixcmd As String

posixcmd = ThisWorkbook.Path

scriptToRun = "tell application " & posixcmd & ":MetaDataFileDownloadScript.app " & URL
MsgBox "[" & scriptToRun & "]"
MacScript (scriptToRun)
</code>

I first tried to call the script directly without the "tell application" and I still obtain an error. I also tried the following in VBA and I get the same error with the last lane of code:

<code>
scriptToRun = "set theURL to " & """" & URL & """" & Chr(13)
scriptToRun = scriptToRun & "set p_path to do shell script " & """" & "/bin/echo ~ " & """" & Chr(13)
scriptToRun = scriptToRun & "set m_path to " & """" & posixcmd & """" & Chr(13)
scriptToRun = scriptToRun & "set theName to " & """" & shortFileName & """" & Chr(13)
scriptToRun = scriptToRun & "set theFile to (p_path & " & """/Downloads/""" & " & theName)" & Chr(13)
scriptToRun = scriptToRun & "set macPath to (POSIX file theFile)" & Chr(13)
scriptToRun = scriptToRun & "do shell script " & """" & "/usr/bin/curl " & """" & " & " & " theURL " & " & " & """" & " -o " & """" & " & " & " quoted form of POSIX path of macPath"
MsgBox scriptToRun
'scriptToRun = scriptToRun & "do shell script " & """/usr/bin/curl """ & " & " & " theURL " & """ -o """ & " & " & " quoted form of POSIX path of macPath"
MacScript (scriptToRun)
</code>

The above code is from the applescript application. The applescript application works well using the terminal via osascript. For example:

xenas-imac:Census Work Xena$ osascript MetaDataFileDownloadScript.app http://www2.census.gove/acs20095yr/summaryfile/Sequence_Number_and_Table_numberLookup.xls
xenas-imac:Census Work Xena$

The whole goal is to download a file into the Download directory from Excel to allow the next step to import the file into Excel for the user. Any help here would be great!

Thank you;

Lori

Mac OS X (10.6.5), iMac9,1; Intel Core 2 Duo 3.06 GHz

Posted on Dec 29, 2010 11:42 AM

Reply

Answer 1

codexena Author

Level 1

0 points

Dec 30, 2010 4:20 AM in response to codexena

Update:

Ok have the module almost working and matches the syntax within the test applescript I had created, but the VBA code via the MacScript() call is generating an invalid procedure or argument call. The following is the test applescript to compare the created string to be passed to VBA MacScript() function:

<code>
set appPath to quoted form of POSIX path of "Macintosh HD:Users:Xena:Desktop:Census Work:"
do shell script "/usr/bin/osascript " & appPath & "MetaDataFileDownloadScript.app " & "http://www2.census.gove/acs2009 5yr/summaryfile/Sequence_Number_and_Table_numberLookup.xls"
</code>

The following is the VBA module that generates the above word for word as far as I can tell:

<code>
Sub SaveMetaDataFile(URL As String, shortFileName As String)
Dim scriptToRun As String
Dim posixcmd As String

posixcmd = ThisWorkbook.Path
posixcmd = posixcmd & ":"

scriptToRun = "set appPath to quoted form of POSIX path of " & Chr(34) & posixcmd & Chr(34) & Chr(13)
scriptToRun = scriptToRun & "do shell script " & Chr(34) & "/usr/bin/osascript " & Chr(34) & " & appPath & " & Chr(34) & "MetaDataFileDownloadScript.app " & Chr(34) & " & " & Chr(34) & URL & Chr(34)
MsgBox "[" & scriptToRun & "]"
MacScript (scriptToRun)
End Sub
</code>

The message box in the above VBA code matches my test applescript file and I have taken the output of the MsgBox and used it in a test applescript and it works great, but VBA is throwing "Run-time error '5': Invalid procedure call or argument".

Looking at Microsoft's definition it takes a string argument and I have made sure that scriptToRun is a string type, so at this point I suspect there is something wrong with the MacScript() funciton, or I am missing something above. Is there another way to call an applescript application from VBA outside the MacScript() function? Or is there something I am missing? Any help or suggestions is needed.

Thank you;

Lori (CodeXena)

Reply

Answer 2

codexena Author

Level 1

0 points

Jan 5, 2011 1:58 PM in response to codexena

After many days of work and feedback from Peter Jamieson, I got the code to work within VBA to pass a variable to an Applescript application and download a file based upon the passed URL variable. The code is as follows:

<code>
Sub SaveMetaDataFile(URL As String, shortFileName As String)
Dim scriptToRun As String
Dim posixcmd As String
Dim results As String

Err.Clear
On Error GoTo scriptError

scriptToRun = ""
posixcmd = ThisWorkbook.Path
posixcmd = posixcmd & ":"

scriptToRun = "set thePath to " & """" & posixcmd & """" & Chr(13)
scriptToRun = scriptToRun & "set appPath to thePath as alias" & Chr(13)
scriptToRun = scriptToRun & "tell application " & Chr(34) & "Finder" & Chr(34) & Chr(13)
scriptToRun = scriptToRun & "set appUserPath to quoted form of POSIX path of appPath" & Chr(13)
scriptToRun = scriptToRun & "end tell" & Chr(13)
scriptToRun = scriptToRun & "do shell script " & Chr(34) & "/usr/bin/osascript " & Chr(34) & " & appUserPath & " & Chr(34) & "MetaDataFileDownloadScript.app " & Chr(34) & " & " & Chr(34) & URL & Chr(34)

MacScript (scriptToRun)
Exit Sub

scriptError:
Dim Msg As String
Msg = "Error # " & Str(Err.Number) & " from " _
& Err.Source & ": " & Err.Description & vbNewLine _
& "Macscript = " & scriptToRun
MsgBox Msg, , "SaveMetaDataFile"

End Sub
</code>

Note: You will have to pass a valid URL with the above code. I have not implemented error checking if the URL is invalid yet, but I thought I get this out there for others who like to follow this same approach.

Reply