How to Transfer User Data Between Updates with VBA

When you update your protected workbook and distribute a new EXE, you might face challenges ensuring your users’ data is correctly transferred, especially if you use the Full Save mode. This mode creates a complete snapshot of the workbook at the time of saving. Consequently, when a user opens their old save file (.xlsc) with the new application, they will see their old data in the old workbook structure and will not benefit from your latest updates (such as new features or bug fixes in your VBA code).

This topic explains how to use a VBA-based export/import system to help your users migrate their data from an old version of your application to a new one.

Code Example #

This process involves two main steps: exporting data from the old version and importing it into the new one.

1. Export User Data to an Excel File #

The macro below will generate a normal .xlsx Excel file containing the user’s data. You should include this macro in your old application, linked to a button, so users can export their data before upgrading.

Sub GenerateData()
    Dim savePath As String
    'New workbook with 3 sheets
    Workbooks.Add xlWBATWorksheet
    ActiveSheet.Name = "SheetA"
    Sheets.Add(After:=Sheets(1)).Name = "SheetB"
    Sheets.Add(After:=Sheets(2)).Name = "SheetC"
    ActiveWorkbook.Sheets("SheetA").Range("A1:C3").Value = ThisWorkbook.Sheets("SheetA").Range("A1:C3").Value
    ActiveWorkbook.Sheets("SheetB").Range("B3").Value = ThisWorkbook.Sheets("SheetB").Range("B3").Value
    ActiveWorkbook.Sheets("SheetC").Range("B1:C3").Value = ThisWorkbook.Sheets("SheetC").Range("B1:C3").Value
    savePath = Application.GetSaveAsFilename("", "Excel workbook (*.xlsx),*.xlsx", 1, "Export User Data")
    If savePath <> "False" Then ActiveWorkbook.SaveAs savePath, FileFormat:=51
    ActiveWorkbook.Close False
End Sub

2. Upload the data to the new EXE file. #

The user needs to open the new EXE and run the 3rd macro below to upload the data (link the macro to a button). Once the user runs the macro, he will be prompted to select the file (1st macro), and the data will be copied across (2nd macro). The third macro will run both macros, and that’s the macro that needs to be linked to the button. Again, you will need to change the cells and tab names in the second macro, and you can also change the title in the first macro:

************* 1st macro:
Sub Open_Workbook_Dialog()
Dim my_FileName As Variant
my_FileName = Application.GetOpenFilename( _
FileFilter:="Excel Files,.xl;.xm", _
FilterIndex:=3, _
Title:="Select the old version of your file, where you will pull the data from", _
MultiSelect:=False)
If my_FileName <> False Then
Workbooks.Open Filename:=my_FileName
End If
End Sub

**************** 2nd macro:
Sub TransferData()
If Workbooks.Count > 1 Then
Workbooks(1).Sheets("SheetA").Range("A1:C3").Value = Workbooks(2).Sheets("SheetA").Range("A1:C3").Value
Workbooks(1).Sheets("SheetB").Range("B3").Value = Workbooks(2).Sheets("SheetB").Range("B3").Value
Workbooks(1).Sheets("SheetC").Range("B1:C3").Value = Workbooks(2).Sheets("SheetC").Range("B1:C3").Value
Workbooks(2).Close savechanges:=False
Else
MsgBox "The data hasn’t been transferred.", vbExclamation, "Error"
End If
End Sub

************ 3rd macro:
Sub TheTransfer()
Call Open_Workbook_Dialog
Call TransferData
End Sub

👉 See Also:

• Saving mode you selected for your XLS Padlock project