Fill a FileDialog from another macro

Question

My company has a standard model tool, which uses a standard Excel file as input.

To update inputs from an old template, you download a standard file from a platform, and use an Excel VBA sub that doesn't take any arguments (called "Upgrade engine"). When you call Upgrade engine, there is a file dialog tab that opens, to select the source file to upgrade.

To test the standard model I create templates for each release of the model for non regression testing. I would like to automate the process. I cannot change the code of the standard template.

I created a masterfile with my non regression use cases, their address etc. to update them one by one.

Public gParamTab As Variant
Public gHypTab As Variant

Public gSourcefolder As String
Public gBlankFolder As String
Public gTgtfolder As String

Public Const gParamTabColUseCase As Byte = 1
Public Const gParamTabColTTtgt As Byte = 2
Public Const gParamTabColTTSource As Byte = 3
Public Const gParamTabColFlagRetrieve As Byte = 4
Public Const gParamTabColTTCase As Byte = 5
Public Const gParamTabColFlagUpgrade As Byte = 6

Public Const gBlankTTName As String = "Table_Template_MVP_case"

Public Const gExtension As String = ".xlsb"


Sub init()
gParamTab = Sheets("Parameters").Range("gParamTab")
gHypTab = Sheets("NDD HYP").Range("gHypTab")

gSourcefolder = Sheets("Parameters").Range("gSourcefolder")
gTgtfolder = Sheets("Parameters").Range("gTgtfolder")
gBlankFolder = Sheets("Parameters").Range("gBlankFolder")
End Sub


Sub updateTT()

Call init

Dim lFullname_blank As String, lFullname_source As String, lFullname_tgt As String
Dim lGlobalrange As Variant
Dim lGlobaltable() As Variant
Dim lBlankTT As Workbook
Dim lLastRow As Long
Dim lSearchedVariable As Variant
Dim lBlankTTupgradeengine As String
lcol = 2

For lUsecase = 2 To UBound(gParamTab, 1)
    If gParamTab(lUsecase, gParamTabColFlagUpgrade) = 1 Then
     
        lFullname_blank = gBlankFolder & "\" & gBlankTTName & gParamTab(lUsecase, gParamTabColTTCase) & gExtension
        lFullname_source = gSourcefolder & "\" & gParamTab(lUsecase, gParamTabColTTSource) & gExtension
        lFullname_tgt = gTgtfolder & "\" & gParamTab(lUsecase, gParamTabColTTtgt) & gExtension
        
        Set lBlankTT = Workbooks.Open(lFullname_blank)
        
        lBlankTTupgradeengine = gBlankTTName & gParamTab(lUsecase, gParamTabColTTCase) & gExtension & "!UpgradeEngine.UpgradeEngine"
        
        Application.Run lBlankTTupgradeengine
        
    End If
Next

End Sub

How can I, from another macro, after the statement Application.Run lBlankTTupgradeengine, the upgrade engine macro starts, and calls the following function embedded in the "BlankTT"?

Sub UpgradeEngine()

    Set wkb_target = ThisWorkbook
    Set wkb_source = macros_Fn.Open_wkb()

    [...]

Function Open_wkb() As Workbook

    Dim fileName As Variant

    With Application.FileDialog(msoFileDialogFilePicker)
  
        ' Makes sure the user can select only one file
        .AllowMultiSelect = False
        
        ' Filter to just keep the relevants types of files
        .filters.Add "Excel Files", "*.xlsm; *.xlsb", 1

        .Show
        
        ' Extact path
        If .SelectedItems.Count > 0 Then
            fileName = .SelectedItems.Item(1)
        Else
            End
        End If
        
    End With
                  
    If (fileName <> False) Then
        Set Open_wkb = Workbooks.Open(fileName:=fileName, IgnoreReadOnlyRecommended:=False, Editable:=False, ReadOnly:=True, UpdateLinks:=False)
    Else
        MsgBox "This file is already open. Please close it before launching the function."
        End
    End If
        
End Function

This function opens a dialog box with a browse button to select the Excel spreadsheet to use as source.

How can I fill automatically this FileDialog from my code, without changing the code of the standard Excel file?

I'm trying to move a copy of the upgrade engine, but with an argument in the sub instead of the filedialog.

Answer 1

Your best bet would be to add an optional parameter to UpgradeEngine - something like:

Sub UpgradeEngine(Optional wbPath as String = "")

    '...
    Set wkb_target = ThisWorkbook
    If Len(wbPath) > 0 Then
        Set wkb_source = Workbooks.Open(wbPath)  'open using provided file path
    Else
        Set wkb_source = macros_Fn.Open_wkb()    'open user-selected file
    End If
    '...
    '...

Then you can call it and pass in the path you want.

FYI the code in Open_wkb seems off (at least, the "already open" message seems wrong). fileName <> False only checks if the user made a selection: it doesn't indicate anything about whether a selected file is already open or not.

Answer 2

I finally copied all the modules of the target file in my own macro, made a few tweaks, and it works even better. Passing this step of the Filedialog was in fact the first step of a long and hard road. There was a lot of msgbox and other interactions so I disabled everything in my one code.