Calling open workbook from another workbook

[Merlynsdad]

I'm switching back and forth between two workbooks. I open Book1 from the
ThisWorkbook_Open event in Book2, then switch back to Book2 with a
Me.Activate since I'm still in it. How do I switch back to the already opened
Book1? Presumably this is also how I would switch back to Book2 while in
Book1?

 

[Gary''s Student]

Windows("Book2").Activate
or
Windows("Book2.xls").Activate

depending on what is in the Window Title Bar.

 

[Merlynsdad]

That would do it if it were local, but it's out on a network and I'm
referring to it with Constant "cstrDatabaseWB". Using the constant either
with the "" or without doesn't work. I forgot to mention I'm also running the
code in the click event of a listbox.

 

[Patrick Molloy]

gary's answer is correct if you have two or more workbooks open ... all you
need to do is activate the one that to want to use. Are you now saying that
when you "switch" to book 2 that you actually CLOSE book1? I' m not clear on
thios question.

 

[Merlynsdad]

No, I'm not closing Book1, I'm just activating it and switching back and
forth from Book1 to Book 2 as I copy and paste. If I use the actual name of
the workbook "real1.xls" it works fine. However when I input the constant
cstrDatabaseWB in the command:

Windows(cstrDatabaseWB).Activate

I get a runtime Error 9, "subscript out of range" error.

The constant is currently defined as:

Const cstrDatabaseWB As String = "C:\Documents and Settings\5YPJB\My
Documents\Excel documents\Sherry project\real1.xls"

because I'm writing this on my machine, but the constant will eventually end
up with a network path.

 

[Patrick Molloy]

thats becasue the workbook name in the activate method is just the name and
excludes the path

try
Const cstrDatabaseWB As String = "real1.xls"

 

[Patrick Molloy]

....and you'll probably want another const for the file path

another was would be to dim a couple of variables as workbooks ...

eg

SET wb1 = workbooks.open(cstrDatabaseWB)
SET wb2 = workbooks.open(someother_cstrDatabaseWB)

then all you need is
wb1.Activate
or
wb2.Activate

seesm easier to me. plus, all you need to close is
wb1.close false ' to close without saving
or
wb2.close true ' to save & close

 

[Merlynsdad]

I think you're assuming I know more than I know. I'm still a novice at this.
The top of the module looks like this:

Dim ListIndex As Long
Dim list As String
Dim lstText As String
Dim myRange As Range
Dim i As Integer
Const cstrDatabaseWB As String = "C:\Documents and Settings\5YPJB\My
Documents\Excel documents\Sherry project\real1.xls"


On Error GoTo HandleError
Application.ScreenUpdating = False

' Select the employee

lstText = lstEmployee.Text

' Run the DCIData query on the employee from the main workbook

' wb1.Activate (does this go here???)
Sheets("Dcidata").Select
Range("$E$6").Select
Selection.AutoFilter
Selection.AutoFilter field:=5, Criteria1:=lstText


Could you indicate what you would do a little more clearly, as I'm getting
lost trying to follow your previous posts. Thanks much.

 

[Patrick Molloy]

sorry.
your constant, cstrDatabaseWB , holds th efull path as well as the name of
the file, ie "C:\Documents and Settings\5YPJB\My
Documents\Excel documents\Sherry project\real1.xls"

However, the workbook name, once its open is just the file name, "real1.xls"
so you need

Workbooks("real1.xls").Activate


and your code would then be

Workbooks("real1.xls").Activate ' probably not necesary
WITH Workbooks("real1.xls").Sheets("Dcidata").Range("$E$6")
.AutoFilter
.AutoFilter field:=5, Criteria1:=lstText

END WITH

of course, this may cause other issues. Note that you don't need to activate
or select ranges in order to use them