dshap
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()
ExecCmd("calc")
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.
Thanks!
- 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.
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()
ExecCmd("calc")
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.
Thanks!
- 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.