CODEWITHSUNDEEP
vbaexcel
lakshmen
lakshmen

Reputation: 29064

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

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?

Upvotes: 8

Views: 93506

Answers (4)

Bill Barnes
Bill Barnes

Reputation: 1

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

Upvotes: 0

wayne leanza
wayne leanza

Reputation: 1

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

Upvotes: 0

Chandraprakash
Chandraprakash

Reputation: 978

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

Upvotes: 1

L42
L42

Reputation: 19727

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

Upvotes: 8

Related Questions