PDA

View Full Version : Run Applescript from Excel with VBA




Caezar
Feb 18, 2012, 05:39 AM
Hi,

I am trying to run some Apple script from Excel using VBA. I know nothing about AppleScript, but I downloaded some code from another forum and when I run it in the AppleScript Editor it works perfectly well. However, when I try to run it in Excel through VBA, it invariably fails, even though I tried several ways already.

Purpose of the script: to create a text file, add some content and save the file

Applescript:
set theFile to ("Macintosh HD:Applications:Microsoft Office 2011:Office:Queries:Test1.txt")
set TheText to "This is the text" & return

open for access file theFile with write permission
set eof file theFile to 0
write TheText to file theFile starting at eof
close access file theFile

set ReadFile to read file theFile

VBA code to run the same Applescript:
Sub CreateTextFileBis()

Dim strPath As String
Dim strFileName As String
Dim strFullPath As String
Dim strFileContent As String

strPath = "Macintosh HD:Applications:Microsoft Office 2011:Office:Queries:"
strFileName = "test1.txt"
strFullPath = strPath & strFileName
strFileContent = "This is my text"

scriptToRun = scriptToRun & "open for access file " & Chr(34) & strFullPath & Chr(34) & " with write permission" & Chr(13)
scriptToRun = scriptToRun & "set eof file " & Chr(34) & strFullPath & Chr(34) & " to 0" & Chr(13)
scriptToRun = scriptToRun & "write " & Chr(34) & strFileContent & Chr(34) & " to file " & _
Chr(34) & strFullPath & Chr(34) & " starting at eof" & Chr(13)
scriptToRun = scriptToRun & "close access file " & Chr(34) & strFullPath & Chr(34) & Chr(13)

MacScript (scriptToRun)

End Sub

What am I doing wrong?



Mark FX
Feb 18, 2012, 01:52 PM
I know nothing about VBA or Excel, but you cannont call an Applescript in the
way you are doing, but for example if you wanted to use an external script
in Real Basic you would pass the parameters to the script like this.

Applescript code.

on run {parameter1, parameter2}
set parameter1 to parameter1 as text --could be any Aplescript type
set parameter2 to parameter2 as integer --could be any type again
--Do something in your code with the supplied Parameters
end run


Real Basic code.

Dim Parameter1 as String
Dim Parameter2 as Integer

Parameter1 = "Some Text"
Parameter2 = 100

NameOfMyApplescript (Parameter1, Parameter2)


Basically you can only send the information to the compiled script, via this type
of pattern, and if you want to get a return value, then you would do something
like this.

Applescript code.

on run {parameter1, parameter2}
set parameter1 to parameter1 as text --could be any Aplescript type
set parameter2 to parameter2 as integer --could be any type again
--Do something in your code with the supplied Parameters
return Result as text
end run


Real Basic code.

Dim Parameter1 as String
Dim Parameter2 as Integer
Dim Result as string

Parameter1 = "Some Text"
Parameter2 = 100

Result = NameOfMyApplescript (Parameter1, Parameter2)

//Do something with the returned Result


I Hope this is some help

Regards Mark