VBA File Save if File is Changed

natezenmaster

New Member
Joined
May 1, 2006
Messages
11
Hello programmers better than me,

Let's say I want to create a VBA program (or sub) to:
(1) save a file to a directory each time the contents (any cell in a list) is changed
(2) generate a time stamp or some relative way to create multiple files when the file is saved
(3) allow the original file to stay put and incorporate the new/amended data

E.g. Book1.xls is a shared workbook, user A accesses the file and enters some data and your clever VBA saves a version off (Book1TimeStamp.xls) in addition to saving the entered data to the original version (Book1.xls). Then, user B accesses file and screws things up and saves data to original version (Book1.xls) - I would like to be able to go back to Book1TimeStamp and retrieve the data from user A. Make sense?

Hard or easy to keep version history like this???

Many advanced thanks and gold stars to those future responders.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

natezenmaster

New Member
Joined
May 1, 2006
Messages
11
Older, less clever me,

Here is the solution for those keeping tabs at home:

After my VBA routine runs its main program then it calls 'checkversion' sub in order to determine if new data warrants a version history saved off. If new data is present then it saves a new version off and saves the current version as the current file - that way each time a user enters data - I have the previous version saved and incorporate the new data in the current file... works as follows: (and yes, I will have to make sure the number of users doesn't get too large or too many files are created in my directory)

Sub checkversion()
' select dashboard and look at version marker
Sheets("Dashboard").Select
curver = Cells(1, 1).Value ' cell keeps track of version history
' take a look at current data in the entry list
Sheets("Task Entry").Select ' grab the current list of data
' set change notification to 0 (no change)
changeflag = 0
' read in data for comparison later
For counter = 1 To 32
currlist(counter) = Cells(counter + 1, 3)
Next counter
' open last known version
vername = "OG" & curver & ".xls"
lastknown = "\\directory\SharedWorkbook\History\" & vername
Workbooks.Open Filename:=lastknown ' open last known version
Windows(vername).Activate
' read in last known data in entry list for comparison
Sheets("Task Entry").Select
For counter = 1 To 32
lastlist(counter) = Workbooks(vername).Sheets("Task Entry").Cells(counter + 1, 3)
'MsgBox (currlist(counter) & " curr versus last " & lastlist(counter))
Next counter
' compare the data and alter change notification if a change is found
For counter = 1 To 32
If currlist(counter) = lastlist(counter) Then
changeflag = changeflag
Else
changeflag = 1
End If
Next counter
' if there is a change then save a new version off, close other workbook and save current file with new data
If changeflag = 0 Then ' no change
Windows(vername).Activate
ActiveWorkbook.Close False
Else ' list has changed
curver = curver + 1
Windows(vername).Activate
ActiveWorkbook.Close False
Windows("Task List to Calendar In Use.xls").Activate
Sheets("Dashboard").Select
Cells(1, 1).Value = curver ' cell keeps track of version history
vername = "OG" & curver & ".xls" ' append version history for opening
lastknown = "\\directory\SharedWorkbook\History\" & vername
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=lastknown
ActiveWorkbook.SaveAs Filename:="\\directory\SharedWorkbook\SharedWB.xls"
Application.DisplayAlerts = True
End If
'MsgBox ("Change Flag is " & changeflag)
End Sub

Works like a champ... hope others find it useful...
 

Watch MrExcel Video

Forum statistics

Threads
1,095,201
Messages
5,443,002
Members
405,212
Latest member
Arnie58

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top