help

  • Thread starter Thread starter suneel
  • Start date Start date
S

suneel

hi
i want to know if there is a way to make versions of a
worksheet and if we can find out the date when they were
created and modified
Regards
Suneel
 
Suneel,

You could create a workbook name and increment that when opening the file,
and append it as a version number like so

Dim sFile As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ActiveWorkbook
sFile = Left(.Name, Len(.Name) - 4) 'remove .xls
sFile = Left(sFile, InStrRev(sFile, "V")) &
Evaluate(Names("version").RefersTo)
.SaveAs Filename:=sFile
End With
End Sub

Private Sub Workbook_Open()
ActiveWorkbook.Names.Add Name:="version", _
RefersTo:=Evaluate(Names("version").RefersTo) + 1
End Sub


And here is a function to get created and modified dates.


Function DocProperty(DocType As String)
With ActiveWorkbook
Select Case LCase(DocType)
Case "created": DocProperty =
Format(.BuiltinDocumentProperties("Creation Date"), "dd mmm yyyy")
Case "modified": DocProperty =
Format(.BuiltinDocumentProperties("Last Save Time"), "dd mmm yyyy")
Case Else: DocProperty = CVErr(xlErrValue)
End Select
End With
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Just save as _A, _B, or a date

MsgBox ActiveWorkbook.BuiltinDocumentProperties("Creation Date")
MsgBox ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
 
Bob,

Nice one, I've added it to my Code Librarian...

However, the code may cause a problem for some as "Names" in
Evaluate(Names... needs the ActiveWorkbook qualifier.

Regards,
Jim Cone
San Francisco, CA
*****************
Bob Phillips said:
Suneel,
You could create a workbook name and increment that when opening the file,
Click to expand...
and append it as a version number like so
Dim sFile As String
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ActiveWorkbook
sFile = Left(.Name, Len(.Name) - 4) 'remove .xls
sFile = Left(sFile, InStrRev(sFile, "V")) &
Evaluate(Names("version").RefersTo)
.SaveAs Filename:=sFile
End With
End Sub
Private Sub Workbook_Open()
ActiveWorkbook.Names.Add Name:="version", _
RefersTo:=Evaluate(Names("version").RefersTo) + 1
End Sub
Click to expand...

-snip-
 
Thanks for the heads-up Jim. You're absolutely right, and to be even more
resilient we should test for it's existence in the BeforeClose event, in
case it was deleted since opening, and if so, re-create it based upon a
version number from the filename.

Regards

Bob
 
Back
Top