Stack Overflow Asked by rainbowthug on January 8, 2021
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
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).
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.
Answered by Pᴇʜ on January 8, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP