Saving a file with date & time from sub

[Jimmy P]

<!--[if !mso]> <style> v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} </style> <![endif]--> I'm trying save my workbook after processing with the contents of a worksheet cell (cell has formula =Now() ) appended to the File Name. I've tried everything that I could find on his forum and from all over the web with no success.

Here's the code as is. I get Run-time Error "1004" as a return.

Sub SaveWithDateTime()
ActiveWorkbook.SaveAs Filename:= "C:\Users\Admin1\Desktop\Working Copies\test Working Copy.xlsm" & _ Sheets("Calc").range("i1"), _ FileFormat:= xlOpenXMLWorkbookMacroEnabled, CreateBackup:=True
End Sub

 

[Joe4]

You need that value before your extension and you typically want to remove all the special characters from your date (such as /,.
You can use the FORMAT function to format the date/time any way you want, i.e.

[COLOR=#333333]Sub SaveWithDateTime()[/COLOR]
[COLOR=#333333]    ActiveWorkbook.SaveAs Filename:= "C:\Users\Admin1\Desktop\Working Copies\test Working Copy_" & _ 
        Format(Sheets("Calc").Range("i1"),"yyyymmdd_hhmmss") & ".xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=True[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

Note, you also don't need to store the current date/time in a cell on your Excel sheet to use it. You can call NOW() directly from VBA, i.e.

[COLOR=#333333]Sub SaveWithDateTime()[/COLOR]
[COLOR=#333333]    ActiveWorkbook.SaveAs Filename:= "C:\Users\Admin1\Desktop\Working Copies\test Working Copy_" & _ 
        Format(Now(),"yyyymmdd_hhmmss") & ".xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=True[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[mole999]

Working Copies\test Working Copy.xlsm" & _ Sheets("Calc").range("i1"),

needs to be like this

Working Copies\test Working Copy Sheets("Calc").range("i1").xlsm" &

so the date is inside the name and extension

this is only a demo of something close

 

[Jimmy P]

Good Golly...That was fast. Works great. Used the second one since I really don't need to use the cell contents with it. Goes in my bag of tips.

"It's all easy when you know how"

Much thanks.

 

[Jimmy P]

Since you're a moderator. How do I mark a thread as "solved" or complete or something to indicate it's no longer active after a solution has been found.

 

[mole999]

just say thanks, threads stay live

 

[Joe4]

Your welcome.

How do I mark a thread as "solved" or complete or something to indicate it's no longer active after a solution has been found.

There is a discussion about that here: http://www.mrexcel.com/forum/about-board/618492-how-close-thread.html