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"
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")
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"
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" :
Set wkb_target = ThisWorkbook
Set wkb_source = macros_Fn.Open_wkb()
Function Open_wkb() As Workbook
Dim fileName As Variant
' 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
' Extact path
If .SelectedItems.Count > 0 Then
fileName = .SelectedItems.Item(1)
If (fileName <> False) Then
Set Open_wkb = Workbooks.Open(fileName:=fileName, IgnoreReadOnlyRecommended:=False, Editable:=False, ReadOnly:=True, UpdateLinks:=False)
MsgBox "This file is already open. Please close it before launching the 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.