VB newbie, automating Excel process

[drace1111]

I have two separate pieces of a puzzle that I would like to tie together. We have a customer that FTPs drop-ship orders in text format. These orders are then moved using a VB script (thanks to Mark) from the FTP server to another location. A user then opens an Excel template that contains a macro to import the text file, format it, then email it to another person.

Here is what I would like to happen.
1) File arrives at FTP server and is moved via the VB script.
2) When and if a file is moved, another script will open Excel, run the import macro, save the file to xxxx.xls, where xxxx is the same filename as the original txt file.

Right now these orders only arrive once a day. The customer would like to start sending orders as soon as they are received. Getting this automated would eliminate the middle-man step of running the macro manually multiple times a day.

Any and all advice is apreciated.

 

[VBAjedi]

Well, if the import macro is written so that it doesn't require any user input (like filename, etc): you could put it into the workbooks Open event so that the import macro runs every time the workbook is opened. Have the macro close the workbook as its last step (this can be a bit tricky, but search this forum for code examples). Then have the script that moves the file also open your workbook.

So the FTP file arrives, the script fires to move it and open your workbook, and your workbook runs the import, saves the correct file, and closes itself back down.



VBAjedi

 

[drace1111]

I have most of this problem resolved. The only place that I am having trouble is in the opening and saving of files with variable names. An Excel template is started via vbscript, then a macro within the template is started automatically which formats the data and saves the file. This works fine as long as the filename is always the same, but the filenames will soon become variable. Can I set it up to open the most recent file? Or maybe the filename can be of some help. It will be formatted with the date and time like so: 200409031209.txt. Also, I want to be able to save the formatted data as an XLS file with the same name as the imported TXT data.

Too complicated?