aravindhan_3
11-17-2009, 02:11 AM
Hi,
I have the below code which saves the sheets as new workbook with the password.However the macro overwrites the file if already exists.
How do we make changes on this to save as new file with version # say filel v.1.xls, file v.2 etc.xls. each time the file is saved new should be created and not overwrite the exisiting file.
code
Sub Save_Archive()
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Dim The_Filename As String 'Name of file to save
Dim The_Dir As String 'Name of directory in which to save the file
Sheets("Blank BCS Wk1").Visible = True
Sheets("Blank BCS Wk1").Select
ActiveSheet.Copy
Range("M1").Select
'The_Filename = (Range("d5")) & " " & (Range("f5")) & ".xls"
The_Filename = (Range("M1")) & ".xls"
'Set directory within C drive
The_Dir = "C:\Documents and Settings\MT45\Desktop\16.11.09\"
ActiveWorkbook.SaveAs Filename:=The_Dir & The_Filename, FileFormat:=xlNormal _
, Password:="country", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
With ActiveSheet.UsedRange
.Value = .Value
End With
ActiveWorkbook.Save
ActiveWorkbook.Close False
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
Sheets("Blank BCS Wk1").Visible = False
End Sub
thanks for you help
arvind
I have the below code which saves the sheets as new workbook with the password.However the macro overwrites the file if already exists.
How do we make changes on this to save as new file with version # say filel v.1.xls, file v.2 etc.xls. each time the file is saved new should be created and not overwrite the exisiting file.
code
Sub Save_Archive()
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Dim The_Filename As String 'Name of file to save
Dim The_Dir As String 'Name of directory in which to save the file
Sheets("Blank BCS Wk1").Visible = True
Sheets("Blank BCS Wk1").Select
ActiveSheet.Copy
Range("M1").Select
'The_Filename = (Range("d5")) & " " & (Range("f5")) & ".xls"
The_Filename = (Range("M1")) & ".xls"
'Set directory within C drive
The_Dir = "C:\Documents and Settings\MT45\Desktop\16.11.09\"
ActiveWorkbook.SaveAs Filename:=The_Dir & The_Filename, FileFormat:=xlNormal _
, Password:="country", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
With ActiveSheet.UsedRange
.Value = .Value
End With
ActiveWorkbook.Save
ActiveWorkbook.Close False
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
Sheets("Blank BCS Wk1").Visible = False
End Sub
thanks for you help
arvind