I have a spreadsheet that I locked with XLS Padlock that the end user updates with their individual information on 1 sheet. This sheet links to formulas throughout the spreadsheet.
If I make changes or upgrades to the spreadsheet, how can my end user update their spreadsheet, but keep their customized items on the one sheet?
Is there a macro that I can write to do that in a locked .exe file?
Other customers do this: they save cell values entered by their own customers to a third-party workbook (without any VBA nor formulas), just plain values. They implement some Load/Save Data mechanism.
Thus, you can update your secure workbook without the problem you mentioned.
I apologize, I have no idea what this means. I am not a programmer at all. I don’t need you to write code for me, but can you give me more of a step by step explanation of what needs to happen? I can then probably figure out the code to make it happen.
Hi,
I am new user here. I am planning to buy this padlock software so that I can commercialise my excel software easily.
However I want to know whether is it possible to update the exe files which are already distributed amongst the users if I update it on my PC?
As of now I do not care about the user data saved in it.
Further what is the best possible solution so that user data also remains saved?
Kindly reply so that I can decide whether this tool will solve my purpose or not.
Because in my excel utility, I have to update very frequently.
Thanks!
If you update your source workbook, you’ll have to recompile it as an EXE file and deploy this new EXE to your customers (generally, they just download it).
The new EXE will recognize existing license information without requiring end users to activate again.
For whoever is looking for a solution, once I deploy a new version of my EXE file, this is how I do it (divided in two steps):
---------- 1ST STEP:
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
------- 2ND STEP:
Upload the data to the new EXE file.
The user needs opens 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 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
------------ IMPORTANT: you need to advise your users to close any other spreadsheets when running the macros.
Cheers,
Thank you for your valuable contribution!
This is my code:
I want to transfer data from the backup file I create.
But error 1004 appear.
certain my .exe cells where the data should be transfer use dropdown and not lock.
please review my code below where the error:
Sub BackupDataInfo1()
Dim strFile As String
'Backup Info 1
Workbooks.Add xlWBATWorksheet
ActiveSheet.Name = “Info1”
ActiveWorkbook.Sheets(“Info1”).Range(“B9:S39”).Value = ThisWorkbook.Sheets(“Info1”).Range(“B9:S39”).Value
strFile = Application.GetSaveAsFilename(“Backup Info 1”, “Excel workbook (.xlsx),.xlsx”, 1)
If strFile <> “False” Then ActiveWorkbook.SaveAs strFile, FileFormat:=51
ActiveWorkbook.Close False
End Sub
Sub Open_Workbook_DialogInfo1()
Dim my_FileName As Variant
my_FileName = Application.GetOpenFilename( _
FileFilter:=“Excel Files,.xl;.xm”, _
FilterIndex:=3, _
Title:=“Find File named - Backup Info 1”, _
MultiSelect:=False)
If my_FileName <> False Then
Workbooks.Open Filename:=my_FileName
End If
End Sub
Sub TransferDataInfo1()
If Workbooks.Count > 1 Then
Workbooks(1).Sheets(“Info1”).Range(“I10,I12,I14,I16,I17,K17,O10,O15,O17,I20,K20,M20,O20,B10,B21,B25,I22,K22,M22,O22,I23,K23,M23,O23,I25,K25,M25,O25,I26,K26,M26,O26,I28,K28,M28,028,I30,M30,I32,I36,K36”).Value = Workbooks(2).Sheets(“Info1”).Range(“I10,I12,I14,I16,I17,K17,O10,O15,O17,I20,K20,M20,O20,B10,B21,B25,I22,K22,M22,O22,I23,K23,M23,O23,I25,K25,M25,O25,I26,K26,M26,O26,I28,K28,M28,028,I30,M30,I32,I36,K36”).Value
Workbooks(2).Close savechanges:=False
Else
MsgBox “The data hasn’t been transferred.”, vbExclamation, “Error”
End If
End Sub
Sub TheTransferInfo1()
Call Open_Workbook_DialogInfo1
Call TransferDataInfo1
End Sub
Thank you for your response.
Very useful to save/load data from certain cell. How is it possible to save also the cells that are added using a macro and load them later on in an updated version of an encrypted file ? Thank you.
Very useful indeed!
What happens, or what is supposed to happen with the extra “xls Excel” file that was added and the original EXE file?
Must they be uninstalled, deleted or “killed” afterwards? I don’t see any such actions in the above code.
Thank you.