Run Applescript from Excel with VBA

Discussion in 'Mac Programming' started by Caezar, Feb 18, 2012.

  1. Caezar, Feb 18, 2012
    Last edited by a moderator: Feb 18, 2012

    macrumors 6502

    Joined:
    Jun 9, 2004
    Location:
    Between a rock and a hard place
    #1
    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:
    Code:
    [INDENT]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[/INDENT]
    
    VBA code to run the same Applescript:
    [INDENT]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[/INDENT]
    What am I doing wrong?
     
  2. macrumors member

    Mark FX

    Joined:
    Nov 18, 2011
    #2
    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.
    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.
    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.
    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.
    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
     

Share This Page