How to pass variables from VBA to applescript?

Scripting Forums AppleScript | Mac OS X
#1

Complete newbie on applescript, know enough to survive in VBA. Looking for a way to pass variables from VBA to applescript. In particular a file name as a sting and the contents for a text file as a string. (I have VBA code in excel that fills a text box on a form with a bunch of stuff.) I want to save it as a text file with an extension of *.NC. All of my code in excel works and I am able to run applescripts to open files and write a string to them. What I am unable to crack is how to pass strings from VBA to applescript. I can only find two examples on the web but can’t figure out how they work.

Looking for examples of code in both VBA and Applescript.

#2

By using the MacScript function. You could ask Ron de Bruin who is specialized in VBA and AppleScript (with Excel), you can check out his website about excel automation

#3

Thanks for the response. I know about the MacScript function and have used that. But I still don’t understand how to make it do what I want. Some test VBA code below…

Private Sub btnSave_Click()
Dim strNCCodeFileName As String
Dim strNCCode As String
strNCCodeFileName = “NiceFileName.NC”
'the real file name would be determined by code
strNCCode = " A big bunch of text"
'this string would be the contents of a text box, couple thousand characters
Call callScript(strNCCodeFileName, strNCCode)
End Sub

Sub callScript(strNCCodeFileName As String, strNCCode As String)
Dim scriptToRun As String
scriptToRun = “display alert " & “”” & strNCCodeFileName & strNCCode & “”"
MacScript (scriptToRun)
End Sub

The display alert pops up, but instead of the text being (without the quotes) “NiceFileName.NC A big bunch of text” I get “& strNCCodeFileName & strNCCode &” (without the quotes). I am thinking I need to have an actual applescript file (or app) in the script library that I can pass the two arguments to? I am just not sure how to make that work.

My project is using Excel to program CNC machines. I have a user form that adds to a text box as the user does their work, at the end hit save and have it stuff the contents of the text box into a text file and save it.

#4

I have a lot of examples in the Mac section of my site, you can see how I do it there.
http://www.rondebruin.nl/mac.htm

Test the script string string the VBA create in the scrip editor so you are sure it is correct

#5

Update

Trying this now in VBA…

Sub callScript(strNCCodeFileName As String, strNCCode As String)
Dim scriptToRun As String
scriptToRun = “run script (” & Chr(34) & “/Users/Library/Scripts/XLNC/XLNC 3.scpt” & Chr(34) & " as POSIX file)"
MsgBox scriptToRun 'just to check the string format
MacScript (scriptToRun)
End Sub

calling this applescript


on run {strNCCodeFileName, strNCCode}

	set theBody to strNCCode
	
	set outputFile to POSIX file (POSIX path of ("Macintosh HD:Users:dreeng001:Dropbox:_NC:_jobs:" & strNCCodeFileName))
	try
		set fileReference to open for access file outputFile with write permission
		write theBody to fileReference
		close access fileReference
	on error
		try
			close access file outputFile
		end try
	end try
	tell application "Finder" to open POSIX file (POSIX path of ("Macintosh HD:Users:dreeng001:Dropbox:_NC:_jobs:" & strNCCode))	
end run

but still getting a runtime error 5 on the MacScript line. The core section of the applescript (without the on run, end run) by setting the strNCCode and strNCCodeFileName. It will create the file, write to it, and open for viewing

#6

Update II

I figured out how to pass variables from applescript to applescript as in the example below


set strNCCodeFileName to "Testy.NC" as text
set strNCCode to "Lorem ipsum dolor sit amet..." as text
run script {"/Library/Scripts/XLNC/XLNC3.scpt" as POSIX file} with parameters {strNCCodeFileName, strNCCode}

That passes the variables to the following script (XLNC3.scpt), which works fine


on run {strNCCodeFileName, strNCCode}
	set theBody to strNCCode
	set outputFile to ((path to desktop as text) & strNCCodeFileName)
	try
		set fileReference to open for access file outputFile with write permission
		write theBody to fileReference
		close access fileReference
	on error
		try
			close access file outputFile
		end try
	end try
	tell application "Finder" to open outputFile
end run

My VBA MacScript call still blows up though. The scriptToRun string below matches the run script line above. I think it is not passing the variables.

Private Sub btnSave_Click()
Dim strNCCodeFileName As String
Dim strNCCode As String
strNCCodeFileName = “Testy.NC”
strNCCode = “Lorem ipsum dolor sit amet…”
Call callScript(strNCCodeFileName, strNCCode)
End Sub

Sub callScript(strNCCodeFileName As String, strNCCode As String)
Dim scriptToRun As String
scriptToRun = “run script {” & Chr(34) & “/Library/Scripts/XLNC/XLNC3.scpt” & Chr(34) & " as POSIX file} with parameters {strNCCodeFileName, strNCCode}"
MsgBox scriptToRun 'just to check the string format
MacScript (scriptToRun)
End Sub

Ron, anybody, any ideas? I’ve looked through most of the Mac part of your site, especially the Files / Folders section, but too much of a newbie to make any sense out of it. (Your stuff did help me out earlier when I was building the user form)