Undo the changes even after closed the spreadsheet

There are many ways to retrieve the data even after closed the excel spreadsheet. One among those is using VBA Macro. Here I assume that you know at least how to write a macro.

So what is the task? I want to undo the changes whatever I have done before closing the saved workbook.

One idea which hit my mind is make a checkpoint. So in certain point, make a checkpoint and we can retrieve the checkpoint at any point of time. How excel stores the checkpoint? That is the core of this idea. When an user makes a checkpoint, the whole content of the active worksheet will be stored in a temporary folder as flat text file with the cell content, cell address and the sheet index. So when the user fetches the checkpoint, the macro will retrieve the data from the flat text file which was stored in the temporary directory.

Here is the source code:


Option Explicit

Sub StoreData(control As IRibbonControl)
 Dim sheetIndex As Integer
 Dim lastRow As Integer
 Dim lastCol As Integer
 Dim i As Integer
 Dim j As Integer

 Dim currentCell As String
 Dim strData As String
 Dim tmpPath As String

 Dim fso As FileSystemObject
 Dim folder As folder
 Dim txtStr As TextStream

 sheetIndex = ActiveSheet.Index
 lastRow = Sheets(sheetIndex).UsedRange.Rows.Count
 lastCol = Sheets(sheetIndex).UsedRange.Columns.Count
 currentCell = ActiveCell.Address

 tmpPath = Environ("TEMP")

 Set fso = New FileSystemObject
 Set folder = fso.GetFolder(tmpPath & "\")
 Set txtStr = folder.CreateTextFile(ActiveWorkbook.Name & ".txt", True)

 For i = 1 To lastRow
  For j = 1 To lastCol
   Cells(i, j).Activate
   strData = CStr(sheetIndex) + vbTab + ActiveCell.Address + vbTab + CStr(Cells(i, j).Value)
   txtStr.WriteLine strData
  Next
 Next

 txtStr.Close

 Set txtStr = Nothing
 Set folder = Nothing
 Set fso = Nothing

 Range(currentCell).Activate

 MsgBox "Check point is created successfully"
End Sub

Sub RetrieveData(control As IRibbonControl)
 Dim strData As String
 Dim sheetIndex As Integer
 Dim cellRange As String
 Dim cellValue As String
 Dim arrData

 Dim fso As FileSystemObject
 Dim txtStr As TextStream

 Set fso = New FileSystemObject
 Set txtStr = fso.OpenTextFile(Environ("TEMP") & "\" & ActiveWorkbook.Name & ".txt")

 ActiveSheet.UsedRange.Clear

 Do While Not txtStr.AtEndOfStream
  strData = txtStr.ReadLine
  arrData = Split(strData, vbTab)
  sheetIndex = CInt(arrData(0))
  cellRange = arrData(1)
  cellValue = arrData(2)
  Sheets(sheetIndex).Range(cellRange).Value = cellValue
 Loop

 txtStr.Close

 Set txtStr = Nothing
 Set fso = Nothing

 MsgBox "Check point in fetched successfully"
End Sub

In the above source code, the StoreData procedure will let the user to store the whole content of the active worksheet in a text file in the temporary directory. The RetrieveData procedure will help you to retrieve the data from the temporary folder.

I’ve attached a sample spreadsheet which will guide you easily how to work on it. There are two buttons ‘Make Check Point‘ and ‘Fetch Check Point‘ in the Check Point group under the Home tab.

So to experiment, open the attached sheet, click on Make Check Point button and make any changes, save and close it. Then re-open the sheet and click on Fetch Check Point button, there you will get the data whatever it had before the changes.

You can download the sheet from here: Undo Demo Spreadhseet

P.S: This demo sheet works only in Excel 2007 or Excel 2010.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s