Receiving variables from a batch file
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
Please give a coding example
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