VBA Code to save workbook as "filename" and current date

[chrissmarlow]

Hi guys,

I am looking for code to add to the end of my macro which will save the file as "SRS Sheet - " and then the current date. I want it to save in a folder within the path the current workbook is located, ie. T:\Macrosheet is where the file is now, I want it to save to T:\Macrosheet\Reports

Can anyone help me out with this?

 

[VoG]

Try like this

ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\Reports\SRS Sheet - " & Format(Date, ddmmyyyy) & ".xls"

 

[missgari]

I would try something like:

NewPath = Mid(ThisWorkbook.FullName, 1, Len(ThisWorkbook.FullName) - Len(ThisWorkbook.Name)) & "\Reports\" & "SRS Sheet - " & Format(Date, "dd-mm-yyyy") & ".xlsx"

ThisWorkbook.SaveAs(NewPath)

Please note that ".xlsx" is just for Excel 2007+, so change that according to your needs. Any questions plz ask

 

[chrissmarlow]

I would try something like:

NewPath = Mid(ThisWorkbook.FullName, 1, Len(ThisWorkbook.FullName) - Len(ThisWorkbook.Name)) & "\Reports\" & "SRS Sheet - " & Format(Date, "dd-mm-yyyy") & ".xlsx"
 
ThisWorkbook.SaveAs(NewPath)

Please note that ".xlsx" is just for Excel 2007+, so change that according to your needs. Any questions plz ask

Try like this

ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\Reports\SRS Sheet - " & Format(Date, ddmmyyyy) & ".xls"

Thanks for your help

 

[udarawic]

Hi,

If I want to save it in the same folder as the current one?
Can you suggest me the changes to this code?

Thx

I would try something like:

NewPath = Mid(ThisWorkbook.FullName, 1, Len(ThisWorkbook.FullName) - Len(ThisWorkbook.Name)) & "\Reports\" & "SRS Sheet - " & Format(Date, "dd-mm-yyyy") & ".xlsx"

ThisWorkbook.SaveAs(NewPath)

Please note that ".xlsx" is just for Excel 2007+, so change that according to your needs. Any questions plz ask

 

[Blade Hunter]

change \Reports\ to \

 

[udarawic]

Thx Blade for your quick reply.
I used the below code in a command button. But nothing happened and no errors either.


NewPath = Mid(ThisWorkbook.FullName, 1, _
Len(ThisWorkbook.FullName) - Len(ThisWorkbook.Name)) & "\" & "SRS Sheet - " & Format(Date, "dd-mm-yyyy") & ".xlsm"

 

[Blade Hunter]

Did you have ThisWorkbook.SaveAs(NewPath) in there too?

 

[Harry Flashman]

This is not exactly what you have asked for but it can be pretty easily modified for your purposes.

I like to make backups of my workbooks. So I usually create a folder in the same folder as the workbook called Backups. When I run this macro that is where my file is saved.

[COLOR=#000000][FONT=Courier New]Sub BackupMyWorkbook()[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]ThisWorkbook.SaveCopyAs _[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]Filename:=ThisWorkbook.Path & "\Backups\" & _[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]Format(Now(), "yyyy-mm-dd hh mm AMPM") & " " & _[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]ThisWorkbook.Name[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]End Sub[/FONT][/COLOR]

 

[udarawic]

I Didn't have it initially,

But when I include ha didn't help either.

Thx...