Load a File Error 9

[Mike H.]

After loading a file when I issue the command

Windows(TheWb).Activate

where TheWB is the name of the file (with or without the path, doesn't
matter), I am getting an error: "Runtime Error 9, Subscript out of range".
What do I need to change?

 

[Chip Pearson]

Use the .xls extension in the file name. E.g.,

Windows("Book1.xls").Activate
instead of
Windows("Book1").Activate


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Gary Keramidas]

something like this:

Sub test2()
Dim fPath As String
Dim fname As String

Dim wb2 As Workbook
fPath = "D:\My Documents\Excel\"
fname = fPath & "Book1.xls"
Set wb2 = Workbooks.Open(Filename:=fname)
Windows(wb2.Name).Activate
End Sub

 

[sebastienm]

Hi
What about the extension (with/without)
Also you could do something like:
Dim Wbk as workbook
Set Wbk= Workbooks.Open ( .... your file ....) ''' returns the opened
book into a variable
Wbk.Windows(1).Activate

 

[Mike H.]

If I have the filename "xxx.xls" in place of the variable thewb or if I have
the variable thewb which = xxx.xls, I still get the same error, #9, Subscript
error.

 

[Chip Pearson]

Do you have multiple windows of the same workbook open? If you do, you'll
see something like

FileName.xls:1
or
FileName.xls:2

in the title bar of Excel. If this is the case, you must include the window
number in the Windows reference. E.g.,

Windows("Book1.xls:1").Close

Or, you can close all but one window with code like

With Workbooks("Book1.xls").Windows
Do Until .Count = 1
.Item(1).Close
Loop
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Mike H.]

I don't know what is going on, but I am still getting the same error 9
subscript out of range when I use even this code.

 

[Dave Peterson]

How about just using:
wb2.activate

I don't know what is going on, but I am still getting the same error 9
subscript out of range when I use even this code.