I updated my original workbook. What about existing saves made by end users?

 

 

If you choose the Full Save Mode:

When you deploy an updated EXE file, XLSC save files made by end users previously won’t be automatically upgraded. Instead, end users will see the last changes they made and not yours. That’s the expected behavior.

A solution consists in switching to the cell value saving mode.

You can also use VBA to save data entered by end users to a second workbook file and let them load this data back to your compiled workbook.

Example with code:

1) The macro below will generate a normal xls Excel file with the user data.

User needs to run the macro in the EXE file where the data will be copied from, so the initial EXE file must have the macro already. The user will run the macro below clicking on a button, which will copy the cells and ask the user to enter the file name. Obviously, you will need to replace the tab names and cells you would like to copy from in the macro below:

Sub GenerateData()

Dim strFile 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

strFile = Application.GetSaveAsFilename("", "Excel workbook (.xlsx),.xlsx", 1)

If strFile <> "False" Then ActiveWorkbook.SaveAs strFile, 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

 

Note that, in order to save normal workbooks, you may also see: Loading/Saving workbooks through VBA SetOption helper

 


Copyright © 2021 G.D.G. Software