Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling open workbook from another workbook
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling open workbook from another workbook
Windows("Book2").Activate
or Windows("Book2.xls").Activate depending on what is in the Window Title Bar. -- Gary''s Student - gsnu200908 "Merlynsdad" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling open workbook from another workbook
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. "Gary''s Student" wrote: Windows("Book2").Activate or Windows("Book2.xls").Activate depending on what is in the Window Title Bar. -- Gary''s Student - gsnu200908 "Merlynsdad" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling open workbook from another workbook
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" wrote: 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. "Gary''s Student" wrote: Windows("Book2").Activate or Windows("Book2.xls").Activate depending on what is in the Window Title Bar. -- Gary''s Student - gsnu200908 "Merlynsdad" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling open workbook from another workbook
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" wrote: 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" wrote: 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. "Gary''s Student" wrote: Windows("Book2").Activate or Windows("Book2.xls").Activate depending on what is in the Window Title Bar. -- Gary''s Student - gsnu200908 "Merlynsdad" wrote: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling open workbook from another workbook
thats becasue the workbook name in the activate method is just the name and
excludes the path try Const cstrDatabaseWB As String = "real1.xls" "Merlynsdad" wrote: 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" wrote: 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" wrote: 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. "Gary''s Student" wrote: Windows("Book2").Activate or Windows("Book2.xls").Activate depending on what is in the Window Title Bar. -- Gary''s Student - gsnu200908 "Merlynsdad" wrote: 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling open workbook from another workbook
....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 "Patrick Molloy" wrote: thats becasue the workbook name in the activate method is just the name and excludes the path try Const cstrDatabaseWB As String = "real1.xls" "Merlynsdad" wrote: 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" wrote: 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" wrote: 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. "Gary''s Student" wrote: Windows("Book2").Activate or Windows("Book2.xls").Activate depending on what is in the Window Title Bar. -- Gary''s Student - gsnu200908 "Merlynsdad" wrote: 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling open workbook from another workbook
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" wrote: ...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 "Patrick Molloy" wrote: thats becasue the workbook name in the activate method is just the name and excludes the path try Const cstrDatabaseWB As String = "real1.xls" "Merlynsdad" wrote: 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" wrote: 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" wrote: 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. "Gary''s Student" wrote: Windows("Book2").Activate or Windows("Book2.xls").Activate depending on what is in the Window Title Bar. -- Gary''s Student - gsnu200908 "Merlynsdad" wrote: 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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling open workbook from another workbook
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 "Merlynsdad" wrote: 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" wrote: ...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 "Patrick Molloy" wrote: thats becasue the workbook name in the activate method is just the name and excludes the path try Const cstrDatabaseWB As String = "real1.xls" "Merlynsdad" wrote: 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" wrote: 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" wrote: 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. "Gary''s Student" wrote: Windows("Book2").Activate or Windows("Book2.xls").Activate depending on what is in the Window Title Bar. -- Gary''s Student - gsnu200908 "Merlynsdad" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open a specific workbook...find value from other open workbook and then insert cells values in cell next to it. | Excel Programming | |||
Passing calling workbook name to called workbook | Excel Programming | |||
Reference code in another workbook from a calling workbook | Excel Programming | |||
Open a password protected excel workbook from second workbook to fetch data using dynamic connection | Excel Programming | |||
What commands do you use to name a workbook, save a workbook,open a workbook | Excel Programming |