无需修改标准模板代码，如何自动填充Excel VBA的FileDialog？

背景 #

我们团队有一款以标准Excel文件为输入的模型工具，升级旧模板时需从平台下载标准文件，再调用无参数VBA宏UpgradeEngine——这个宏会弹出FileDialog，要求手动选择待升级的源文件。

为开展模型回归测试，我给每个版本都创建了测试模板，希望实现升级流程自动化，但无法修改标准模板的代码。目前已制作包含回归用例及路径信息的主控文件，用于批量更新测试用例。

主控文件现有VBA代码 #

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

标准模板中被调用的宏代码 #

调用Application.Run lBlankTTupgradeengine后，标准模板的UpgradeEngine宏会执行，并调用内部的Open_wkb函数：

UpgradeEngine宏 #

Sub UpgradeEngine()

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

    [...]

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

问题 #

Open_wkb函数会弹出文件选择对话框，需要手动选择源文件。请问在不修改标准模板代码的前提下，如何通过外部宏自动填充这个FileDialog，实现全自动化升级？我之前尝试复制升级引擎并改为带参数的子程序，但未能解决问题。

内容的提问来源于stack exchange，提问作者Methodrone8