Saving Techniques in Excel

[lui]

Hello all,

I'm trying to write a macro to save a spreadsheet everyday according to it's date. For example, I want to save to spreadsheet as "spreadsheet_050303" on 5/03/03 and "spreadsheet_050403" on 5/04/03. I tried saving the document as "spreadsheet_&date" but didn't succeed. Can anyone provide some insight into this matter? Thanks very much!!

-Will

 

[Joe4]

Try this as your file name:

"spreadsheet_" & Format(Now(), "mmddyy")

 

[TommyGun]

Here's a sample code that will do what you wish.

Sub SaveMe()

ThisWorkbook.SaveAs FileName:=ThisWorkbook.Path & "spreadsheet_" & Format(Date,"mmddyy") & ".xls"

End Sub

 

[lui]

Thanks very much for your help. The techniques worked great without any complication. Another great tip retrieved from MrExcel.com !!!

 

[TommyGun]

Try this and see if it works for you. Let me know if it causes problems.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.EnableEvents = False

If Not SaveAsUI Then

    Me.SaveAs FileName:=Me.Path & "\spreadsheet_" & Format(Date, "mmddyy") & ".xls"

Else

    Dim strFile As Variant
    Cancel = True

    strFile = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel Files (*.xls),(*.xls)" _
              , initialfilename:="spreadsheet_" & Format(Date, "mmddyy") & ".xls")

    If strFile <> False Then Me.SaveAs FileName:=strFile

End If

Application.EnableEvents = True

End Sub

 

[lui]

Hello all,

I'm trying to save an Excel document that automatically attaches the date. For example, I would like to save a document as document_050303 on 5/3/03 and document_061503 on 6/15/03. I have used a macro successfuly but have not figured out how to do this when doing "save as" manually. Thanks very much!!

-Will

 

[lui]

Thanks TommyGun for the advice. Your macro worked very nicely. Out of curiosity, I tried saving the excel manually by typing document_$(date) for 5/20/02 and nothing happened. Is there another technique to be used when saving files manually through the "save as" textbox? Thanks again for your help

-Will

 

[shades]

You could add a line with an Inputbox:

myDate = InputBox("Enter the date in mm/dd/yy format")

then in the place where you would normally have the date in your macor, just use myDate.

 

[just_jon]

I'm not sure there is any way to use a function to generate the current date when using Save as manually. At least, never have heard of it.