0

I'd like to create a macro that will:

  1. Open a browser window to select a saved workbook (let's call it WB1)

  2. In the same macro assign WB1 some form of reference that will allow it to be referenced by other macros

I can achieve step 1 via the following code:

Sub Add_New_Survey()
Dim pathString As String
Dim resultWorkbook As Workbook
Dim found As Boolean
pathString = Application.GetOpenFilename(fileFilter:="All Files (* . xl*) , *.xl* ")

' check if it's already opened
For Each wb In Workbooks
    If InStr(pathString, wb.Name) > 0 Then
        Set resultWorkbook = wb
        found = True
        Exit For
    End If
Next wb

If Not found Then
    Set resultWorkbook = Workbooks.Open(pathString)
End If
End Sub

This will open the workbook. I then need to perform a number of data preparation activities on WB1 which I would like to automate. Is there a way to reference WB1 as I open it from the browser so the following macros know to look on WB1 specifically?

Many thanks

Azuremars
  • 1
  • 1
    Welcome to SO. Your object `resultWorkbook ` is linked to `Workbooks.Open(pathString)`, so as long as you dont unlink it with `Set resultWorkbook = Nothing`, you can reference that workbook always on any sub, (but **declare the variable as Public** first in the module, outside of all subs). https://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba – Foxfire And Burns And Burns Sep 18 '18 at 13:32
  • @FoxfireAndBurnsAndBurns - that's a answer, not a comment! (It's also a correct answer, worthy of an upvote or two and a checkmark.) – FreeMan Sep 18 '18 at 14:25
  • Thanks @FreeMan! I'll post it! – Foxfire And Burns And Burns Sep 18 '18 at 15:57

1 Answers1

1

Welcome to SO. Your object resultWorkbook is linked to Workbooks.Open(pathString), so as long as you dont unlink it with Set resultWorkbook = Nothing, you can reference that workbook always on any sub, (but declare the variable as Public first in the module, outside of all subs).

To declare a Variable as Public, please read:

How do I declare a global variable in VBA?

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38