Switching between open workbooks

[RJC]

When trying to go from one open file/workbook to another within a VB module,
WITHOUT SPECIFICALLY PROVIDING THE FILENAME, I'm getting an almost random
selection of choices. Specifically, my macro resides in 'Window 3' of the 3
open workbooks. If I try "Windows(1).Activate" from within that macro, I
stay in 'Window 3'. I understand this from the perspective (as noted in the
VB materials) that the 'ActiveWindow' is alwasy 'Window 1'. If I then edit
the macro to activate "Windows(2).Activate", it will, sometimes, activate the
"Windows" tab identified 'Window 1'. There are various other 'funny' cases,
which seem to be related to what I sequence I may have opened or accessed the
files, though the file/Windows enumeration as provided under the "Windows"
tab is alwas as noted.

Anybody know anything that might help???

Thanks.

 

[Jim Thomlinson]

You do not want to reference workbooks by their index (as you have seen).
Your best option is to set workbook objects to the workbook that you are
dealing with. Something like this...

dim wbkTarget as workbook

set wbkTarget = workbooks("MyBook.xls")
wbktarget.select
msgbox "Tada"
thisworkbook.select

 

[Dave Peterson]

Oops.

dim wbkTarget as workbook

set wbkTarget = workbooks("MyBook.xls")
wbktarget.Activate '<--
msgbox "Tada"
thisworkbook.Activate '<--

 

[Jim Thomlinson]

It's feeling very... Monday... Thanks Dave!

 

[RJC]

Thanks,,, now I I've got to do some homework. Basically, I know how to spell
VB. I recorded the macro based on (very) repititious keystrokes, but have no
idea exactly what you've told me to do. 'Not sure which of the terms are VB
terms, which ones are variables, which ones are necessary (I'm guessing
"Tada" is a congratualtions to me,,, I've not earned it yet), how do I
reference the three workbooks I'm trying to sort through and with what
names,,,,,, I'll play with this a while. If I don't get it sorted out, I'll
be back.

Thanks.

Bob C

It's feeling very... Monday... Thanks Dave!

 

[RJC]

OK, I'm guessing that you can't do what I want to do. I'm trying to allow
the user to only open the file, and not have to type in the name to run the
macro. I was hoping that by intializing via the 'workbooks' object, I could
somehow subsequently refer to the other windows/workbooks without referring
to their names. I've not been able to do so. I assume that the "MyBook.xls"
in the responses has to specifically refer to the filename in question. If
I'm missing something (which usually only happens on days of the week which
end in a 'Y'), I'd appreciate any additional feedback.

Thanks.
--
Bob C

It's feeling very... Monday... Thanks Dave!

 

[Dave Peterson]

If you're opening the files in your code, you could use:

dim wkbk1name as variant
dim wkbk2name as variant
dim wkbk1 as workbook
dim wkbk2 as workbook
dim ActWkbk as workbook

set actwkbk = activeworkbook

wkbk1name = application.getopenfilename("Excel files, *.xls")
if wkbk1name = false then
'use hit cancel
exit sub
end if

wkbk2name = application.getopenfilename("Excel files, *.xls")
if wkbk2name = false then
exit sub
end if

set wkbk1 = workbooks.open(filename:=wkbk1name)
set wkbk2 = workbooks.open(filename:=wkbk2name)

wkbk1.activate
wkbk2.activate
thisworkbook.activate 'workbook with the code
actwkbk.activate 'workbook that was active when the code started

or anything else you want to do with the workbooks.

It's very unusual to have to select/activate something to work with it.