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

Feb 20, 2023 in Others by narikkadan
• 63,420 points
329 views

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

answered Feb 20, 2023 by Kithuzzz
• 38,010 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
• 63,420 points
1,136 views
0 votes
1 answer
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
• 23,260 points
6,200 views
0 votes
1 answer

Retrieve epay.info 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
876 views
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
3,183 views
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
• 23,260 points
480 views
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,670 points
731 views
0 votes
1 answer

VBA - Build a Two-Column Array By Looping Through one Array with a Specific Criteria and Selecting From Another Array

As already mentioned as comment: Loop over ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,010 points
254 views
0 votes
1 answer

Exclude worksheets from loop with a list

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

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
203 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP