You could store your excel vba code in a separate spreadsheet and export into the payrolltax.xls spreadsheet. I have an excel spreadsheet that I have several macros set up in that I can run different Monarch models and exports into different spreadsheets depending on what choice I make. This way, I keep all macros together where they can be easily managed and accessed.
I have created an excel model called Payrolltax.xls where the user pushes a button and the excel VBA code fires up a Monarch project file which opens some reports saved in a directory and loads the information in the model. However, I have not been able (using JetExport) to export the data directly from Monarch to Payrolltax.xls. The reason I can not go directly from Monarch to Payrolltax.xls is because the user is in the model when they push the button that starts the process. To get around this I created a separate Excel file called Data.xls that I use as a go between. Monarch exports the information to Data.xls and then the VBA code opens Data.xls copies the information and then paste it into Payrolltax.xls.
Is this the best way to do this?
I did a test to see if I could go directly into Payrolltax.xls and I did not get the results that I had expected. The process runs fine but it does not export the information. I had expected an error message that said Monarch could not access the Payrolltax.xls file but I did not get one.
Any helpful suggestions? /b[/quote]
Joe, if I understand you correctly, you have a macro in Payroll.xls that executes Monarch that is supposed to update the Payroll.xls file, correct?
Yes, Monarch cannot update a spreadsheet that is currently in use. What you have done is the best way to accomplish this, or as Ralph has said use a seperate macro/xls file to update the Payroll.xls file and use it to open the Payroll.xls file when done.