[sims] Report output excel template - VBA for save as and quit?

[mike_sendrove]

I've created a report which needs to be run weekly. I've defined the excel output using macros in the spreadsheet to manipulate the data as I wish - at the moment the save and close is manual.

 

TO be able to automate the running of the report using command reporter, I've written windows scripts which work well in generating parameter files and calling command reporter - but I'm stuck at writing the VBA in a macro to save as a specified filename in a given format - using the SaveAs method generates an error (object required).

 

Does anybody have a report which generates excel output which automatically saves and closes ? May I take a look at the code which achieves this please?

 

Thanks for any advice.

[TLARWise]

I use the following code:

 

DestinationPath = "C:\Workbook.xlsx"
DestinationFile = "Workbook.xlsx"
ActiveWorkbook.SaveAs Filename:=DestinationPath, FileFormat:=xlOpenXMLWorkbook
Workbooks(DestinationFile).Saved = True
Workbooks(DestinationFile).Close

[mike_sendrove]

Thanks. Works a treat. I couldn't work out why my code wasn't working. I was using ThisWorkbook instead of ActiveWorkbook.

Thanks again.