Excel Automation using Bat file + VBA

Hello all,

I have a question but i am not even sure if this can be done. Here is what i currently have:

1. An outside program executes a bat file prompting the user for a name. Once you type the name, it saves a text file with a bunch of info on it to a specific folder.
2. The user then manually opens a specific excel file. During open, it asks the user to select the text file they just saved. It opens that file in another session of excel and sets it as the wbsource.
3. The main excel file then runs its VBA gathering information from that text file it opened, once finished it closes that text file, continues doing stuff, saves itself to a name thats in a specific cell's value, then closes excel.


Is there a way to avoid the manual opening and selecting of that text file by possibly using a batch file of some sort? That way, it will all be done automatically once you type the name and press enter in your batch file. I know how to tell it to open the specific excel file, but have no idea how to tell excel which text file to load after it opens.

Is this even possible?

check out this link it imports file based on specified location directly into excel without opening it.
Let me know if you get your additional requests to work.

http://www.cpearson.com/excel/ImpText.aspx

That sounds like something I might be able to use. The only thing i am worried about is if two users are running it at the same time, one user might grab the text file of the other user and the data will be incorrect. That's why i was trying to specify a file name rather than file location of the text file.


But just to try it out, I tried copying the "Importing A Text File" and put it into a new module in order to see what it does. But my computer just makes the error sound when i try to step into it using F8. Does using 2007 have anything to do with that?

Thanks for the info, guess i didnt read far enough.

All in all, it sounds like no matter what, i have to either have the user select/input the specified text file being imported or set up a batch file to dump the text file to a folder and excel can grab that text file. Instead of having a batch file to some how tell excel which file to choose before the VBA actually runs.

This process described above will be done by more than one user at any given time so there is no way to guarantee they will be grabbing the correct text file if i dump the file in a folder and have excel grab whatever file is in that folder at that moment.

The only other way around this is trying to convert my file to work in 2003 which i am NOT looking forward to because i am a VBA noob enough as it is much less trying to convert one file to another.

At work, we have a metal building design program up on a server that upon request, it dumps all the job data onto a text file (rep-fld.out).

The process is basically like this (all ran from a batch file):

1. The user logged into the server executes the batch file.
2. The batch file dumps data from that user's particular job run inside the design program to a text file (rep-fld.out)
3. The same batch file then executes an excel file that automatically imports that specific text file from its pre-determined location into excel replacing text so it creates a generic contract.
4. The user can then save that excel file to whatever name they choose.

The problem is, we do not have a multi-user version of excel installed onto the server. Also, multiple users will be performing this file dump process at the same time which could possibly contaminate the contract building process if two tried at the same time. One user could possibly generate the contract of another user's text file dump.

So as a way around the above, i have edited the batch file to force the user to name that text file before it dumps that file to a folder. My idea after that is to somehow tell the single user excel on the server to open excel calling up the contract excel file, running the text import on the file the user made, saving itself to that shared folder, then exiting excel. I have the excel file fixed so its all automatic except for the part about telling it which text file to import without user interface during the VBA process.


My last resort is to take my excel file and edit all the vba to work with excel 2003 then each user can use the excel on their own PC to run the vba on the text file of their choice. But i am not looking forward to that.

Still working on it (still writing it and testing it) but i may have a solution for the majority of it to make it more automated atleast. The bat file on the server will dump the file the user is working on into their own folder based on the username (if it doesnt exist, it will create it).

Bat line to copy: copy rep-fld.out \\mbsts01\Rep_Fld_Files\%USERNAME%\rep-fld.out>nul

And the excel file will pickup that file using the username as well.
VBA line: strfilename = "\\mbsts01\Rep_Fld_Files\" & Environ("USERNAME") & "\Rep-fld.out"""

It still doesnt solve telling the server to run it instead of a logged-in user but its closer.