Fill a FileDialog from another macro

Question

My first posting on this Reddit subreddit. I want to do something that should be really easy, but I have no idea how to do it.

Let me set the scene for you: In my company, we have a standard model tool that accepts an input from a typical excel file.

You download a platform's standard file and utilise a sub that doesn't take any arguments when you wish to update your inputs from an outdated template (called "upgrade engine"). When you use the Upgrade engine, a file dialogue pane appears to assist you in choosing the source file you want to upgrade.

I work on a testing team for the standard model, and for every new model release I have to make a tonne of templates for non-regression testing. I therefore want to automate the procedure. I am unable to alter the code of the default template, and this is a crucial point.

In order to update each of my non-regression test use cases individually, I developed a sort of masterfile with their addresses and other information.

Here is my current code:

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

So i come the main issue, 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

As I mentioned previously, this function launches a dialogue box with a browse option so you may choose the Excel spreadsheet you want to utilize as a source.

How can I automatically fill this File dialog with data from my code without changing the code of the default Excel file?

I looked everywhere, but I couldn't discover anything concerning this issue.

I'm attempting to relocate a copy of the upgrading engine, but I'm having trouble because the macro is too complicated.

Answer 1

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 on the path you want.