Save Excel Spreadsheet as different name

[ulchm]

Hey guys, I'm coding a series of macros for Extra's Attachmate. Quite a few of them read data from an excel spreadsheet using the following code. (they are global for a reason)

Global xl_app as Object
Global xl_wb as Object
Global xl_file as String

in my main I have this to instantiate it

xl_file = "template.xls"
Set xl_app = CreateObject("Excel.Application")
Set xl_wb = xl_app.Workbooks.Open(xl_file)

now, after I've manipulated the data I wish to change etc, I would like to save it as report-073107.xls or whatever the current date is. currently I just call the xl_wb.Save and xl_wb.Close methods.

How would I go about saving the file as a differnet name so the template stays unmodified and I can generate reporst daily without having to clear the spreadsheet?

Thanks in advance

 

[VRoscioli]

How about:

xl_wb.SaveAs([i]filename[/i])

Where filename is a string containing the filename of the file to be saved to.


-V

 

[Fr33dan]

Format(Date, "mmddyy") will give you the date in the format you showed.

 

[ulchm]

great thanks guys!

 

[WalkerEvans]

If you want to be absolutely sure that your template does not acquire any extraneous data that would have to be removed, create a subroutine at opening that contains a "Save As", using Fr33dan's code. This will have you or your user always working in the file that you really want saved, with your template being used only for the initial creation ... no data will be entered into it at all.

Private Sub Workbook_Open()
xl_wb.SaveAs Format(Date, "mmddyy")
End Sub

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long