Fill a FileDialog from another macro

0 votes

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

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

        ' Extact path
        If .SelectedItems.Count > 0 Then
            fileName = .SelectedItems.Item(1)
        End If
    End With
    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."
    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.

Feb 20 in Others by narikkadan
• 60,820 points

1 answer to this question.

0 votes

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
        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.

answered Feb 20 by Kithuzzz
• 35,300 points

Related Questions In Others

0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 60,820 points
0 votes
1 answer
0 votes
1 answer

How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

answered Feb 10, 2022 in Others by gaurav
• 22,970 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,970 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
0 votes
1 answer
0 votes
1 answer

Exclude worksheets from loop with a list

You can achieve this by making a ...READ MORE

answered Feb 11 in Others by Kithuzzz
• 35,300 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP