PDA

View Full Version : Solved: Incorporating a version number (incremental) in filename



satyen
05-28-2008, 02:51 PM
Can anyone help with incorporating a version number in the filename everytime the macro is run- the Master sheet is saved.

Here is the code im currently using:


Public Sub Save()

Dim fname As string


Sheets("Master").copy

Format(Date, "dd-mm-yy") + "_" + Format(Time, "hh.mm")+ "xls"

ActiveWorkbook.saveas Fname:="C:\Saved\" &FName
MsgBox "Your file has been saved"
ActiveWorkbook.Close

End Sub


Many thanks

Bob Phillips
05-28-2008, 02:57 PM
Just use date and time, it will be unique



Public Sub Save()
Dim fname As String

'Sheets("Master").Copy

fname = Format(Now, "dd-mm-yy_hh.mm.ss") & ".xls"

ActiveWorkbook.SaveAs Filename:="C:\Saved\" & fname
MsgBox "Your file has been saved as C:\Saved\" & fname
ActiveWorkbook.Close

End Sub

satyen
05-28-2008, 03:09 PM
A version number is needed , if it can be done. If not I will stick with time. Thanks for the extra bits and editing on the current code.

Bob Phillips
05-29-2008, 12:48 AM
Okay add this code to ThisWorkbook code module



Public Sub Save()
Dim fname As String
Dim myVersion As Long

myVersion = 1 ' in case it doesn't already exist
On Error Resume Next
myVersion = Evaluate(ThisWorkbook.Names("_Version").RefersTo) + 1
ThisWorkbook.Names.Add Name:="_Version", RefersTo:="=" & myVersion
ThisWorkbook.Save

'Sheets("Master").Copy

fname = Format(Now, "dd-mm-yy_hh.mm.ss") & " v" & myVersion & ".xls"

ActiveWorkbook.SaveAs Filename:="C:\Saved\" & fname
MsgBox "Your file has been saved as C:\Saved\" & fname
ActiveWorkbook.Close

End Sub

satyen
05-29-2008, 12:49 PM
works great thanks vmuch!