Access Secure Files from a New Excel Instance

By default, only the Excel instance launched by your compiled EXE can access the secure workbook and its associated files. If you create a new Excel instance using VBA, it will not have permission to open these protected files.

To grant access, you must use the XLS Padlock API to pass the window handle (HWND) of the new Excel instance to the main application. This authorizes the new instance to work with the virtual file system.

Sub RunInSeparateExcelInstance()
    Dim XLSPadlock As Object
    Dim appExcel As Object ' Excel.Application
    Dim wbExcel As Object  ' Excel.Workbook
    Dim companionFilePath As String
    
    ' Use LongPtr for 64-bit compatibility when getting the window handle.
    #If VBA7 Then
        Dim windowHandle As LongPtr
    #Else
        Dim windowHandle As Long
    #End If
    
    On Error GoTo Cleanup
    
    ' Get the path to a companion file located in the same folder as the EXE.
    companionFilePath = GetPathToFileInEXEFolder("data.xlsx")
    If companionFilePath = "" Then
        MsgBox "Companion file not found.", vbExclamation
        Exit Sub
    End If
    
    ' Get the XLS Padlock API object.
    Set XLSPadlock = Application.COMAddIns("GXLS.GXLSPLock").Object
    If XLSPadlock Is Nothing Then
        MsgBox "XLS Padlock API not available. Is the workbook protected?", vbCritical
        Exit Sub
    End If
    
    ' Start a new, invisible Excel instance.
    Set appExcel = CreateObject("Excel.Application")
    
    ' Get the window handle (HWND) of the new Excel instance.
    windowHandle = appExcel.Application.Hwnd
    
    ' Authorize the new instance by passing its HWND to the main application.
    ' Option "5" is used for this purpose.
    XLSPadlock.SetOption Option:="5", Value:=windowHandle
    
    ' The new Excel instance can now access the secure companion file.
    Set wbExcel = appExcel.Workbooks.Open(companionFilePath, False, True)
    
    ' Example: Read a value from the companion workbook.
    MsgBox "Value from companion file: " & wbExcel.Worksheets("Sheet1").Cells(1, 1).Value
    
Cleanup:
    If Err.Number <> 0 Then
        MsgBox "An error occurred: " & Err.Description, vbCritical
    End If
    
    ' Close the workbook and quit the new Excel instance.
    If Not wbExcel Is Nothing Then wbExcel.Close SaveChanges:=False
    If Not appExcel Is Nothing Then appExcel.Quit
    
    ' Release object variables.
    Set wbExcel = Nothing
    Set appExcel = Nothing
    Set XLSPadlock = Nothing
End Sub

Public Function GetPathToFileInEXEFolder(ByVal Filename As String) As String
    ' Helper function to get the full path to a file in the EXE's directory.
    ' See: https://www.xlspadlock.com/doc/get-the-path-to-a-file-in-the-same-folder-as-the-compiled-workbook
    Dim XLSPadlock As Object
    On Error Resume Next
    Set XLSPadlock = Application.COMAddIns("GXLSForm.GXLSFormula").Object
    If Not XLSPadlock Is Nothing Then
        GetPathToFileInEXEFolder = Application.BuildPath(XLSPadlock.PLEvalVar("EXEPath"), Filename)
    End If
End Function