name a backup file

P

PR

I am trying to create a backup copy of a current spreadsheet, and I want to
call it a backup copy and place the month in the title of the file.

Can anyone help me achieve this.

PR
 
T

Tom Ogilvy

With ActiveWorkbook
.SavecopyAs Left(.Fullname,len(.FullName)-4) & "_backup_" & _
format(date,"mmm") & ".xls"
End With
 
P

PR

Tom,
thanks for this, how do I put into this part of the code:

ActiveWorkbook.SaveAs Filename:="D:\Last Months.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=True
 
T

Tom Ogilvy

change this:

ActiveWorkbook.SaveAs Filename:="D:\Last Months.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=True


to

'Silently overwrite any existing file with this name
Application.DisplayAlerts = False
With ActiveWorkbook
.SavecopyAs "D:\Last Months.xls"
End With
Application.DisplayAlerts = True
 
P

PR

Tom,
I have just realised that I cannot save it just by the month, so I wish to
save the date and time, I have tried to use this:
.SaveCopyAs Left(.FullName, Len(.FullName) - 4) & "_backup_" &
Format(Now, "dd Mmm yy h:mm") & ".xls"
but I get an error, can you please tell me why.

Paul
 
T

Tom Ogilvy

I tested it an the Colon is not allowed. If I change it to an underscore,
it works fine:

Sub ABC()
With ActiveWorkbook
sStr = Left(.FullName, Len(.FullName) - 4) & "_backup_" & _
Format(Now, "dd Mmm yy h_mm") & ".xls"

Debug.Print sStr
.SaveCopyAs sStr
End With

End Sub

C:\Data\AA_Sample_backup_08 Nov 05 17_05.xls
 
C

Curt

This is close to my problem: Ask user to backup want to savecopyas. need to
return to active sheet to update for futheruse. When useing savefile as I
lose active sheet. Need to get location for and file name from user.Trying
to use input box for location and file name could be dialog box for save
copyas? useing xl2000
any Help greatly appreciated.
Thanks Curt
 
Top