How to avoid Dual opening within vba

Question

Goal: Make a public constant to use as workbook within multiple functions. The constant is obtained by prompting the user to open a specific file. (This is done because the file is updated monthly and each month has a designated name) How can I reference the open workbook without opening it multiple times?

Details: I have tried to avoid opening multiple times by deleting the "path = OpenFile()". I also tried to make the functions public, the variables are still contained within their original function. I'm not able to use the defined variables across functions. The error states either:

"Subscript out of Range"

or

"Object Required"

EDIT The error refers to

mvmtqt.Activate

and any additional references to variables stated in a previous function but not restated in the newer function. There are several functions not included to spare space for the question.

The additional openfile1() and openfile2() and so forth refer to copies of openfilee().

Code:

Public Function OpenFilee() As String
    On Error GoTo Trap

    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .Title = "Open Sterling Shipment History" 'Name for file
        .InitialFileName = "Dialog start-up path"
        .Filters.Clear
       ' .Filters.Add "Text Documents (*.txt)", "*.txt", 1
        .ButtonName = " Open "
        .AllowMultiSelect = False
    End With

    If fd.Show <> 0 Then OpenFilee = fd.SelectedItems(1)

Leave:
    Set fd = Nothing
    On Error GoTo 0
Exit Function

Trap:
    MsgBox Err.Description, vbCritical
    Resume Leave

End Function


Public Function ShipmentHistPt2()

    Dim path1 As String
    path1 = OpenFile1()
    If path1 <> vbNullString Then Workbooks.Open (path1)
    Dim mvmtln As Workbook
    Set mvmtln = Workbooks.Open(path1)
    Dim path2 As String
    path2 = OpenFile2()
    If path2 <> vbNullString Then Workbooks.Open (path2)
    Dim mvmtqt As Workbook
    Set mvmtqt = Workbooks.Open(path2)
    mvmtqt.Activate
    Sheets("Sheet1").Select

Issue: Whenever I try to activate the window(mvmtqt.Activate) it initiates opening the file again(if I define the variable within the function again), which not only slows down the process, but it also eliminates all previous changes.

What is `OpenFile1()` referencing? What about `OpenFile2()`? Which line(s) is giving the error? — Mistella, Jun 05 '18 at 16:51
I've edited the question for further clarification. There are multiple functions involved within the macro that cannot all be included because of how large they are. — Jreya Kyong, Jun 05 '18 at 17:45
So, if I'm reading your code correctly; if you run the function `ShipmentHistPt2`, the user will be prompted twice (2) to select files to be opened. Each selected file will then be opened twice, instead of the expected once. Is that right? — Mistella, Jun 05 '18 at 18:05
Yes, spot on. It prompts twice when i leave "workbooks.open(path)" in the code. When it is removed the worksheet is only prompted to open once, but it closes and opens the window like flickering. — Jreya Kyong, Jun 05 '18 at 18:21
Are you intentionally prompting for two different files, or is the intention to only prompt once? Since you have two different workbook objects defined (`Dim mvmln` and `Dim mvmtqt`) I'm assuming you want two prompts for two different files. — Mistella, Jun 05 '18 at 18:32
I'm looking to avoid opening the same file twice. I want to open a single time and then after that just activate the screen because I will be switching between other screens. I want to be able to prompt the user to pick a file and activate the file they've picked whenever necessary. — Jreya Kyong, Jun 05 '18 at 18:35

Mistella · Accepted Answer · 2018-06-05T19:33:58.870

Explanation of original code

In the comments I added to the code below, you can see a repeated pattern of getting a file path from the user (via filePicker), opening that file, then opening the file to set to a variable.

Public Function ShipmentHistPt2()

    Dim path1 As String
    path1 = OpenFile1()                                    'calls function for filePicker
    If path1 <> vbNullString Then Workbooks.Open (path1)   'If file selected, opens file
    Dim mvmtln As Workbook
    Set mvmtln = Workbooks.Open(path1)                     'Opens file and sets to workbook variable
    Dim path2 As String
    path2 = OpenFile2()                                    'calls function for filePicker
    If path2 <> vbNullString Then Workbooks.Open (path2)   'If file selected, opens file
    Dim mvmtqt As Workbook
    Set mvmtqt = Workbooks.Open(path2)                     'Opens file and sets to workbook
    mvmtqt.Activate
    Sheets("Sheet1").Select

Since I am unsure if you want to have two files (prompted for, and selected by user) or one, I'll modify the code for a single prompt and open.

Also, using .Activate and .Select can slow down your code. You can check out more information here on alternatives to using .Select, if you'd like. If you must activate the workbook, you should be able to do it with this line: Workbooks(mvmtln.Name).Activate

Note that the code is a bit easier to read if all the Dim statements are at the beginning.

Public Function ShipmentHistPt2()
    Dim path1 As String
    Dim mvmtln As Workbook

    path1 = OpenFile1()
    If path1 <> vbNullString Then Set mvmtln = Workbooks.Open(path1)

    If Not mvmtln Is Nothing Then 'verifies that mvmtln has been assigned to a workbook
        Workbooks(mvmtln.Name).Activate 'Can un-comment if needed
        Workbooks(mvmtln.Name).Sheets("Sheet1").Select 'Should select the desired sheet
    End If

Edit (Global Vars):

For global variables, you want to have a general code module (not one of the sheets or the workbook) and create some global variables there (do not declare them within a sub or function, or they will only have scope within those places):

Dim globalVar_1 as String 'change as needed
Dim globalVar_2 as Long
Dim globalVar_3 as Workbook
'etc......

Then, after opening the desired workbook, set the global variables to the values needed:

Public Function ShipmentHistPt2()
    Dim path1 As String
    Dim mvmtln As Workbook

    path1 = OpenFile1()
    If path1 <> vbNullString Then Set mvmtln = Workbooks.Open(path1)

    If Not mvmtln Is Nothing Then 'verifies that mvmtln has been assigned to a workbook
        Workbooks(mvmtln.Name).Activate 'Can un-comment if needed
        Workbooks(mvmtln.Name).Sheets("Sheet1").Select 'Should select the desired sheet

        'Added code
        globalVar_1 = Workbooks(mvmtln.Name).Sheets("Sheet1").Range("A1")
        globalVar_2 = Workbooks(mvmtln.Name).Sheets("Sheet1").Range("A2")
        Set globalVar_3 = mvmtln
    End If

Thank you the .Name helps avoid opening it again. Do you have any additional information on how to make the variables global, as they can not be used outside of their original assigned function. — Jreya Kyong, Jun 05 '18 at 19:21
I added an edited section with a general use/description. Let me know if you have a more targeted question. — Mistella, Jun 05 '18 at 19:35

How to avoid Dual opening within vba

1 Answers1