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.