Running an Excel Macro from outside Excel

[heather from houston]

Hi,

I am running a data warehouse project and we have a funky system of record we need to read from. The "programmer" who created it has, instead of an RDBMS (or even Access), created a complex directory structure which contains thousands of .mst files.

To compile the mst files for us, he opens Excel, clicks a macro button, and then saves the file as a .csv for us to an ftp server.

My question is ... is there a way to "click" that macro button with some kind of batch file (Windows Script host, Java script, ??)? Then we can have the macro save as .csv and place it in the correct location. The goal is to remove the human intervention in the process.

Please help!!
This message was edited by heather from houston on 2002-09-26 08:31

 

[manney]

Crystal Reports (http://www.crystaldecisions.com) can generate reports against a directory structure. The version I run (v. needs human intervention to launch a report; the new version (v.9) does not. Their phone is 1-800-877-2340 or 1-604-681-3435.

For what it's worth, a text file of a DOS directory structure can be generated by the command

dir dirname /s >dirname.txt

...where
Dirname is the name and path of the directory
dirname.txt is the resultant text file name

This can be done by batch file, automatically.

The resultant file is non-delimited (spaces are used to line everything up). This can be sorted, have macros run on it, etc.

Hope this helps.