MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Automating Excel to run a certain macro at startup


Posted by Ed Cupstid on January 17, 2002 1:01 PM

Is there a way to start Excel 2K from the dos prompt and have it run a predefined macro when it starts. We import several CSV files into Excel, these files are all different, but with unique file names. There are macros defined in Excel for each of the unique file names. I would like to automate the process by having a batch file run excel with a parameter telling it which macro to run as it stepped through the batch file. But I have not been to find any documentation saying this is possible. Any help would be appreciated.


Posted by Dan on January 17, 2002 1:49 PM

You can save a workbook with the macros that you want to run and set up the macros to start running when the workbook is open using the Open event.

In dos, you should be able to launch the workbook by using the excel.exe command followed by the path and workbook name in quotes. So if you are in the directory that excel.exe is in and you want to open excel and launch the workbook "test.xls" on your c: drive root directory, use the command:

excel.exe "c:\test.xls"

Your workbook will open and the macros should start running.

HTH

Posted by Ed Cupstid on January 18, 2002 1:03 PM

Thanks Dan, I may not have clearly stated what I would like to do. I have an application that generates csv files for several accounts. The format for each account/csv file varies. I have macros already set up in Excel for each account(the macro contains the account#,filename and path) that I manually run from within excel for each account that has a csv file in the current billing cycle. The macros imports the csv file, formats it correctly then saves it as and excel file. What I would like to do is create a .cmd file something like the following:

c:\office\excel.exe a123 (a123 being the macro to run, it would import the csv file named a123.csv, format it and save it with .xls ext then close excel)

c:\office\excel.exe b456 (b456 being the macro to run, it would import the csv file named b456.csv format it, save it with .xls ext then close excel)

The .cmd file would automate the whole process...
Hope this helps and hope there is a solution.

Posted by Ed Cupstid on January 18, 2002 7:10 PM

After giving your suggestion more thought I believe I have it figured out..I need to create a separate workbook for each account using the Auto_Open in the macros and assign it to the workbook for that account...Currently, I have all the different macros in one workbook. This is the problem. Thanks for pointing me in the right direction. Ed