Save workbook versions

[joshuasq]

Does anyone know a macro which saves workbook versions with an
incrementing version number, a date, and user initials.

Any code, resources, web sites, etc. would be helpful.

I would like to also save version information in a text file.

thank you.

Josh

 

[Guest]

Create the following macro under the MyWorkbook object

Dim MyLoop As Integer

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If MyLoop = 1 Then Exit Sub
MyLoop = 1
MyFileName = "My File - " & Application.UserName & " " &
Application.WorksheetFunction.Text(Date, "mm") &
Application.WorksheetFunction.Text(Date, "dd")
ThisWorkbook.SaveAs (MyFileName)
MyLoop = 0
Cancel = True
End Sub

The above does not include a version number as I don't know where to get the
version number from. Note that it takes the username from the excel property
that can be found under Tools, Options, General, "User Name".

 

[Bob Phillips]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim mpFile As String
Dim mpVersion As Long

On Error Resume Next
mpVersion = Evaluate(ThisWorkbook.Names("__Version").RefersTo)
On Error GoTo 0
mpVersion = mpVersion + 1
Application.EnableEvents = False
Cancel = True
mpFile = "My File - " & Application.UserName & _
Application.Text(Date, " yyyymmdd ") & _
"v" & mpVersion
ThisWorkbook.SaveAs mpFile
ThisWorkbook.Names.Add Name:="__Version", RefersTo:=mpVersion
Application.EnableEvents = True

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[joshuasq]

I'm having a little difficulty getting these methods to work. I am
not sure if they are behaving as intended or if I have done somethign
incorrectly. To clarify what I am trying to accomplish: I am trying
to run a macro on command (i'll make a button for it on the toolbar),
I want it to save the current document as a new document with a
incremented version number, save date, and user initials.

Example:
Document 1

<run macro on Aug 6 results in creating:>

Document 1 -- Ver 01; 2007-08-06 (JQ)

<run macro again on Aug 7 results in creating:>

Document 1 -- Ver 02; 2007-08-07 (JQ)

Ideally, version information would be stored in a separate text file
so that if previous versions are moved or deleted the version number
continues to function as appropriate.

This is essentially an adaption of http://www.gmayor.com/save_numbered_versions.htm
for word documents, i just can't figure out how to make it work for
Excel.

Any additional thoughts?

Thanks in advance

Josh