How to activate a workbook that is open using the name of the workbook in VBA

Question

I have already a one workbook open but I am running a macro from another workbook. I would like to activate the first workbook using its name.

The code:

FileName = input_path_1 & input_file_1
Workbooks(FileName.xls).Activate

When I try to do so, it is giving me "Subscript out of range" error. How do I solve it?

Answer 1

Check if your variable Filename contains the correct filename. (e.g. Sample.xls)
Also check if input_path_1 and input_file_1 have correct values.
If they have it should be like this:

Workbooks(Filename).Activate

Now, if you need to append the extension name (e.g. Filename value is just Sample):

Workbooks(Filename & ".xls").Activate

The argument should always be in the form of string and should be the complete filename (with extension). Although numerals (index) is also accepted, you can't be sure what index refer to what workbook. Better yet, assign it to a variable.

Dim otherWB As Workbook
Set otherWB = Workbooks(Filename)
'Set otherWB = Workbooks(Filename & ".xls") '~~> for second scenario above

Edit1: From comment, if Filename contains the fullpath, then this might work.

Dim Filename1 As String
Filename1 = Split(Filename, "\")(UBound(Split(Filename, "\")))
Workbooks(Filename1).Activate

Answer 2

Only way to access the window of the specific workbook is by below method

Vba

Dim filename as string
set filename = Path.GetFileName(fullFilename)

set Workbook.Windows(filename).WindowState = Excel.XlWindowState.xlMinimized
set Workbook.Windows(filename).WindowState = Excel.XlWindowState.xlNormal

' You can also use Worksheet.Activate() here if you want

C#

string filename;
filename = Path.GetFileName(fullFilename);

Workbook.Windows[filename].WindowState = Excel.XlWindowState.xlMinimized;
Workbook.Windows[filename].WindowState = Excel.XlWindowState.xlNormal;

// you can also use Worksheet.Activate() here if you want

Answer 3

Set OutsideWb = Workbooks("path + Filename.xlsm") wont work if workbook already open

set a global wb variable to the opened file and use that eg.

Set oXLBook = oXLApp.Workbooks.Open("path + Filename.xlsm") '
  Set OutsideWb = oXLBook 'prolly dont need oxlbook  todo

Answer 4

In Excel 2019,

Workbooks(Filename).Activate may not work if ".xlsx" is part of the variable name.
Example: Filename = "123_myfile.xlsx" may not activate the workbook.

In this case, try:

Filename = left(Filename,len(Filename)-5) 'Filename now = "123_myfile"

Workbooks(Filename & ".xlsx").Activate