Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching between open workbooks
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. -- Bob C |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching between open workbooks
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 -- HTH... Jim Thomlinson "RJC" wrote: 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. -- Bob C |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching between open workbooks
Oops.
dim wbkTarget as workbook set wbkTarget = workbooks("MyBook.xls") wbktarget.Activate '<-- msgbox "Tada" thisworkbook.Activate '<-- Jim Thomlinson wrote: 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 -- HTH... Jim Thomlinson "RJC" wrote: 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. -- Bob C -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching between open workbooks
It's feeling very... Monday... Thanks Dave!
-- HTH... Jim Thomlinson "Dave Peterson" wrote: Oops. dim wbkTarget as workbook set wbkTarget = workbooks("MyBook.xls") wbktarget.Activate '<-- msgbox "Tada" thisworkbook.Activate '<-- Jim Thomlinson wrote: 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 -- HTH... Jim Thomlinson "RJC" wrote: 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. -- Bob C -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching between open workbooks
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 "Jim Thomlinson" wrote: It's feeling very... Monday... Thanks Dave! -- HTH... Jim Thomlinson "Dave Peterson" wrote: Oops. dim wbkTarget as workbook set wbkTarget = workbooks("MyBook.xls") wbktarget.Activate '<-- msgbox "Tada" thisworkbook.Activate '<-- Jim Thomlinson wrote: 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 -- HTH... Jim Thomlinson "RJC" wrote: 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. -- Bob C -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching between open workbooks
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 "Jim Thomlinson" wrote: It's feeling very... Monday... Thanks Dave! -- HTH... Jim Thomlinson "Dave Peterson" wrote: Oops. dim wbkTarget as workbook set wbkTarget = workbooks("MyBook.xls") wbktarget.Activate '<-- msgbox "Tada" thisworkbook.Activate '<-- Jim Thomlinson wrote: 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 -- HTH... Jim Thomlinson "RJC" wrote: 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. -- Bob C -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching between open workbooks
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. RJC wrote: 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 "Jim Thomlinson" wrote: It's feeling very... Monday... Thanks Dave! -- HTH... Jim Thomlinson "Dave Peterson" wrote: Oops. dim wbkTarget as workbook set wbkTarget = workbooks("MyBook.xls") wbktarget.Activate '<-- msgbox "Tada" thisworkbook.Activate '<-- Jim Thomlinson wrote: 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 -- HTH... Jim Thomlinson "RJC" wrote: 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. -- Bob C -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Switching between Workbooks | Excel Discussion (Misc queries) | |||
Switching between workbooks | Excel Programming | |||
Switching between workbooks | Excel Discussion (Misc queries) | |||
Switching Between 2 workbooks using VBA | Excel Programming | |||
switching between workbooks... | Excel Programming |