1

In my tool I first let user open the workbook he or she desire

    MyFileName = Application.GetOpenFilename(FileFilter:="Excel Files, *.xl*;*.xm*")

If MyFileName <> False Then
    Workbooks.Open Filename:=MyFileName
End If

and later on when I want to activate that file again (after opening it gets all necessary variables and goes to another workbook, the one with the tool by ThisWorkbook command) it does not work

I've tried it by doing this

Workbooks(MyFileName).Activate

Whats more important, If i do MsgBox MyFileName it clearly states the path for the MyFileName thus I don't understand why it does not activate this workbook at all - subscript out of range

rainbowthug
  • 67
  • 1
  • 8

1 Answers1

2

What's the issue?

The issue is that MyFileName contains a path (incl. file name) and not a file name only. But Workbooks(MyFileName) is waiting for a file name only (without a path).

Solution

Set your opened workbook to a variable

Dim MyFileName As Variant
MyFileName = Application.GetOpenFilename(FileFilter:="Excel Files, *.xl*;*.xm*")

If Not (VarType(MyFileName) = vbBoolean And MyFileName = False) Then
    Dim OpenWb As Workbook
    Set OpenWb = Workbooks.Open(Filename:=MyFileName)
End If

And use this variable like below then

OpenWb.Worksheets("Sheet1").Range("A1").Value

Don't use .Activate or .Select this is a bad practice and only needed in very rare cases. You might benefit from reading How to avoid using Select in Excel VBA.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Hey. I don't wanna use this workbook further, I just want it to be activate, get all the variables once again (different set of) and once again switch to "ThisWorkbook". Will OpenWb.Worksheets("Sheet1").Range("A1").Value help in this case, to let this firstly opened wb be the one active? Because its ThisWorkbook which is active all the way after first variable setting – rainbowthug Nov 05 '20 at 10:25
  • @rainbowthug I don't understand what you mean by *"get all the variables once again (different set of)"*. • You don't need to activate a workbook to perform actions on it. • Please make sure that all you want to ask is in your original code with enough explanation to reproduce it (you can edit your original question). – Pᴇʜ Nov 05 '20 at 10:41