Hi Team,
The macro below copy data from the 2nd workbook opened and paste in the 1st workbook opened.
Because the 1st workbook is now an .exe file the macro is not working.
How can I fix the macro, considering Workbooks(1) is now .exe file?
Sub TransferData()
Workbooks(1).Sheets(“Logbook”).Range(“A11:C367”).Value = Workbooks(2).Sheets(“Logbook”).Range(“A11:C367”).Value
End Sub
Thanks
You have several ways to open the 2nd workbook:
you could add it as a companion file:
you could open it from VBA. Place the 2nd workbook in the same folder as the new EXE and use the following VBA code:
For instance,
Workbooks.Open(PathToFile(“SecondWorkbook.xlsx”))
Or use shellexecute if the second file is an exe file
eg
'open xlspadlock protected file
Dim TargetFilepath As String
TargetFilepath = PathToFile(“SafeBUDDY Graphs.exe”)
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
'Call Shell(Filepath)
Dim strAction As String
strAction = "OPEN" ' action might be OPEN, NEW or other, depending on what you need to do
lngErr = ShellExecute(0, strAction, TargetFilepath, "", "", 0)
You need the function in the module first.
Public Function PathToFile(Filename As String)
Dim XLSPadlock As Object
On Error GoTo Err
Set XLSPadlock = Application.COMAddIns(“GXLSForm.GXLSFormula”).Object
PathToFile = XLSPadlock.PLEvalVar(“EXEPath”) & Filename
Exit Function
Err:
PathToFile = ""
End Function
Sorry gdgsupport, I don’t think you understand my question (probably my explanation was terrible). The macro has nothing to do with opening a workbook.
My macro is super simple, it just copy and paste, from the second workbook opened to the first workbook opened. That’s it!
Explaining in a bit more details: the user will open the new version of the workbook, and later will open the old version of the workbook. The macro will copy the data entered by the user previously in the old version, and paste to the new version.
This macro works well if both files are Excel workbooks, but because the new version is an .exe file (due to the XLS Padlock) the macro doesn’t work. My understanding is, the macro looks for an workbook and can’t find because the new workbook is an .exe file.
Can you please advise how can I fix this macro?
Thank you.
Sub TransferData()
Workbooks(1).Sheets(“Logbook”).Range(“A11:C367”).Value = workbooks(2).Sheets(“Logbook”).Range(“A11:C367”) .Value
End Sub
Well, for workbooks(1) and (2) to exist, you must have opened them first. If the first is the EXE, how do you open the second?
Yes, for them to exist you have to open them first, on this order.
The first file is now an EXE because of XLS Padlock, and the second file is still an Excel workbook (it is the old version of the EXE version).
The macro copy the data from the old version (Excel workbook) into the new version (EXE XLS Padlock workbook).
Because the new version is now and EXE file, the macro cannot copy from the workbook to another workbook, because the other workbook is now EXE file.
Was that clear?
Cheers
That’s why we told you that the 2nd workbook (old version) must be opened with VBA code. The secure instance of Excel run by the EXE won’t accept loading other workbooks by using the File/Open… command. The sole mean is to use VBA.
It worked, thank you.
Hi Support,
The macro works well when transferring data from standard Excel formats (.xlsm or .xlsx) to the EXE application, but it doesn’t work when transferring data from a .xlsc file.
Is it possible to transfer data from an existing .xlsc file to the EXE application?
Thanks
An .XSLC file is actually an XLSM (or XLSX) file, a copy of your workbook, encrypted. It can only be opened by the EXE at the startup and not afterwards. If you want to exchange data outside of the EXE, you have to use VBA code to deal with normal Excel workbooks. If you want to secure them, you can also use encryption to read/save data.
Sorry Support, thanks for the answer but that was a bit confusing, I’m not sure if we are talking about the same thing.
To clarify, that’s what I’m trying to achieve:
I’m currently using the macros below and they work perfectly copying and pasting the data from a XLSM format to the EXE version 1:
Public Function PathToFile(Filename As String)
Dim XLSPadlock As Object
On Error GoTo Err
Set XLSPadlock = Application.COMAddIns(“GXLSForm.GXLSFormula”).Object
PathToFile = XLSPadlock.PLEvalVar(“EXEPath”) & Filename
Exit Function
Err:
PathToFile = “”
End Function
Sub TheTransfer()
Call Open_Workbook_Dialog
Call TransferData
End Sub
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 spreadsheet, where you will pull the data from”, _
MultiSelect:=False)
If my_FileName <> False Then
Workbooks.Open Filename:=my_FileName
End If
End Sub
simplified TransferData macro with one line only (real one has over 30 lines). It is basically copying and pasting:
Sub TransferData()
Workbooks(1).Sheets(“Logbook”).Range(“A11:C367”).Value = workbooks(2).Sheets(“Logbook”).Range(“A11:C367”) .Value
End Sub
The question: is it possible to transfer the data (copy and paste) from user’s EXE version 1 .xslc file to the new EXE version 2?
It’s possible to share data between workbooks by using a third-party normal workbook file stored in a common location. Use VBA to store data from EXE 1 to the common normal (non-encrypted) workbook file, then configure your EXE 2 with VBA to retrieve data from the common workbook file.