View Full Version : Executing an external application with Excel

07-21-2009, 05:12 AM
Basically I need to use Excel to construct an XML file (using data that is manually entered into a spreadsheet), then have it run a windows batch file which will execute some other program and send this XML file as a request to a server and save the response in a textfile. Excel will then read the textfile, parse it, and format the results into the spreadsheet.

I have this 3rd party synchronous (gotta wait till the response as arrived before attempting to read the it) shell function that I got here:

bigresource dot com slash VB-How-to-make-execution-of-shell-function-synchronous-dsw1DEv96.html#JwHIKSFrrc

I've verified that this works by doing a simple:

Sub test()
MsgBox "Done"
End Sub

When I run this macro, the calculator pops up and Excel still says the macro is running. Only when I close the calculator does the message box come up and say "Done." So the synchronous shell command works.

I have a batch file called "runClient.bat" which takes 1 command line argument which is the path to the XML request file and then runs a java command using a bunch of jars that are in the local directory and that handles the client/server interaction. The response gets put on the console output. "runClient.bat" was given to me by my IT team and if I create an XML request file and type the command "runClient.bat myXMLFile.xml" then I see the appropriate response (after a few seconds) in the console.

To have excel automate this process and save it to a file, I made my own batch file called "runClientWrapper.bat" which has a single line in it:

H:\LiquidityClient\runClient.bat H:\LiquidityClient\closePrintRequest.xml > H:\LiquidityClient\closePrintResponse.txt

When I double click runClientWrapper.bat in Windows explorer, or if I just run the command "runClientWrapper.bat" in a command prompt, I get my closePrintResponse.txt output file after a few seconds (a few seconds with the command prompt open before closing when it finishes) and it contains the appropriate response.

HOWEVER, when I use ExecCmd("runClientWrapper.bat"), the command prompt flashes up really quickly and then closes. I get a closePrintResponse.txt but it's incomplete and it only has the first line of what the output file is supposed to have which is "Processing: H:\LiquidityClient\closePrintRequest.xml". I guess Excel is not giving the application enough time to run completely, or something like that.

Anyone have any ideas? I would appreciate it very much.


- dshap

EDIT: Windows XP, Excel 2003

EDIT 2: Wow, I guess this ended up not being an Excel issue at all. There are 2 ways to reference the path to the network drive I'm working out of and one of them isn't allowed via command prompt - I realized it was defaulting to the Windows directory on the local drive. My bad. I guess this post can be deleted. Thanks anyways.

Simon Lloyd
07-21-2009, 01:13 PM
dshap welcome to VBAX!
This may help you!
Ex mysite!
'Don't forget to replace MyExe.exe with the actual exe you want to launch
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Sub Launch_It()
ShellExecute 0, vbNullString, "MyExe.exe", vbNullString, "C:\", 1
End Sub