Go Back   MacRumors Forums > Apple Systems and Services > Programming > Mac Programming

Reply
 
Thread Tools Search this Thread Display Modes
Old Feb 18, 2012, 05:39 AM   #1
Caezar
macrumors 6502
 
Join Date: Jun 2004
Location: Between a rock and a hard place
Run Applescript from Excel with VBA

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

Last edited by maflynn; Feb 18, 2012 at 10:37 AM.
Caezar is offline   0 Reply With Quote
Old Feb 18, 2012, 01:52 PM   #2
Mark FX
macrumors member
 
Join Date: Nov 2011
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
Mark FX is offline   0 Reply With Quote

Reply
MacRumors Forums > Apple Systems and Services > Programming > Mac Programming

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Similar Threads
thread Thread Starter Forum Replies Last Post
AppleScript: How do I save multiple Excel sheets to one pdf using Applescript sbtaylor1 Mac Programming 0 Oct 28, 2013 11:38 PM
AppleScript: How do I save multiple Excel sheets to one pdf using Applescript sbtaylor1 Mac Applications and Mac App Store 0 Oct 28, 2013 11:32 PM
Applescript to Export text from Indesign and import to Microsoft Excel. alissat Mac Programming 1 Mar 28, 2013 11:21 AM
Applescript. Copy filtered cells in Excel Palkan Mac Programming 2 Jul 3, 2012 10:34 AM
Applescript with Excel tayjayt Mac Programming 1 Jun 27, 2012 05:44 PM

Forum Jump

All times are GMT -5. The time now is 08:38 AM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC