PDA

View Full Version : Receiving variables from a batch file



dcs28
07-10-2008, 07:35 AM
I am new to VBA, vbscript, or anything like that, so please have patience if I struggle with both the posing of this question and the understanding of any responses that I receive.

I have a batch command level file, that in the background, opens up an excel spreadsheet.
Let us assume that my excel spreadsheet resides on
my drive C: under the name of: "test_workbk.xls".
My batch file therefore contains these commands:

"c:\program files\microsoft office\office11\excel.exe" "c:\test_workbk.xls".

On top of this, I have embedded in the "test_workbk.xls" file, a VBA macro that gets initiated whenever the file is open. This macro, simply, saves the .xls file as a tab-delimited text-file. So - essentially, by using the aforementioned batch file, I am able to do this 'save-as' tab-delimited text conversion in the background. We want to automate the conversion, as a preliminary step that is needed in order to transfer the spreadsheet data to our mainframe computer (using connect:direct).

My question is: Can I determine the name of the tab-delimited text file that gets created at run-time; by using a variable for the file-name that gets passed in from the batch file? If so, how do I pass from the batch file this variable, and receive it in the VBA script ?

An additional thought to ponder:
=========================
Would it have been possible for me to accomplish the tab-delimited conversion without having to embed a VBA script inside the excel file
that gets initiated upon an open of the file.
Instead could I have written the tab-delimited conversion entirely in vbscript - and then my question becomes:
Can I pass a variable representing the tab-delimted file name, from the batch file to the vbscript?

regards and thanks in advance to anyone who can assist me.

dcs28

figment
07-10-2008, 07:50 AM
if you batch file is generating the name for the tab-delimited c file, then you could just add this file name generation code to the embedded vb macro.

dcs28
07-10-2008, 08:06 AM
Hi Figment
Thanks for replying.

It is a good thought. But regretfully, the tab-delimted file-name will not always be generated by a purely programmatic process. Otherwise, I would have done what you suggest - namely, just place the file-name generating code, inside the VBA script.

Rather, we have to allow for a human being to decide the file-name just before the file is submitted. He/She would enter that file-name into the mainframe job that initiates the windows batch file. The file-name that the human entered, would then be passed from the mainframe job as a variable, to the windows batch file (we can do this already), and from the windows batch file, into the VBA script (this latter step is what I need assistance on).

So I am still in need of a solution.

regards
dcs28

figment
07-10-2008, 08:10 AM
i am sure there is an easier way, but you could write the name to a temp file, then have the vb open the file and retrieve the name. or you could have the batch file rename the excel sheet to include the name you wish to have the delimited file saved with, then have the vba pull the name from its own file name. i have not worked with batch files so i am not sure of there limitations.