AnswerBun.com

Can't activate workbook by variable

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

Stack Overflow Asked by rainbowthug on January 8, 2021

1 Answers

One Answer

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.

Answered by Pᴇʜ on January 8, 2021

Add your own answers!

Related Questions

How Can I Not Double Count Rows And Objects In My Query?

3  Asked on November 22, 2021 by ziegler199

       

Login with active directory

0  Asked on November 20, 2021 by artisan

       

External CSS not linking for all elements

1  Asked on November 20, 2021 by stratos-la

   

Why is ‘u{1D11E}’.charAt(0) not equal to ‘u{1D11E}’?

2  Asked on November 20, 2021 by json-prime

     

E: Unable to locate package python3-boto3

2  Asked on November 20, 2021 by rosi-darmawati

       

x and o in tic tac toe keeps alternating

0  Asked on November 20, 2021 by channee-mathmath

     

Remove using a button

2  Asked on November 20, 2021 by gastn

   

remove 0 and add ‘ ‘ in every number in pandas

1  Asked on November 20, 2021 by spt-hsb

   

nav-tabs doesn’t change on click

0  Asked on November 20, 2021 by beureu

     

Stick element on bottom on page scroll

3  Asked on November 20, 2021

         

Exchange each 100 in a dataframe for another value

1  Asked on November 20, 2021 by tarik-benrabah

     

GraphQL & Using a nested subscribe function

1  Asked on November 20, 2021 by u-rogel

   

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved.