Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activating Open Workbooks using VBA
Do the workbooks have to be saved before you can activate a workbook using
the Workbooks("abc.xls").Activate command? I can find nothing that suggests that they need to be. I have two workbooks created from a template that at the time of running the macro will be unsaved. When attempting to use the Workbooks.Activate command I get a subscript out of range error which I am assuming relates to the fact that they are not saved files, however they are open. Any assistance to a learner would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activating Open Workbooks using VBA
'For unsaved workbooks the extension will not be there.
Workbooks("book1").Activate 'For saved workbooks use the extension Workbooks("book1.xls").Activate If this post helps click Yes --------------- Jacob Skaria "JC" wrote: Do the workbooks have to be saved before you can activate a workbook using the Workbooks("abc.xls").Activate command? I can find nothing that suggests that they need to be. I have two workbooks created from a template that at the time of running the macro will be unsaved. When attempting to use the Workbooks.Activate command I get a subscript out of range error which I am assuming relates to the fact that they are not saved files, however they are open. Any assistance to a learner would be greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activating Open Workbooks using VBA
Don't rely on the name of the new workbook. Set a variable that represents
those workbooks when you create them. dim wkbk1 as workbook set wkbk1 = workbooks.add(template:="c:\yourtemplate.xlt") 'later... wkbk1.activate Remember that you don't have to activate/select most things to work on them. wkbk1.activate worksheets(1).select range("A1").select selection.value = "hi there" could be replaced with: wkbk1.worksheets(1).range("A1").value = "hi there" JC wrote: Do the workbooks have to be saved before you can activate a workbook using the Workbooks("abc.xls").Activate command? I can find nothing that suggests that they need to be. I have two workbooks created from a template that at the time of running the macro will be unsaved. When attempting to use the Workbooks.Activate command I get a subscript out of range error which I am assuming relates to the fact that they are not saved files, however they are open. Any assistance to a learner would be greatly appreciated. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activating Open Workbooks using VBA
thank you for your assistance, but I am still having issues and I think a
little more information may assist. The problem with using the workbook.add statement is that the files already exist and the .add command creates them doesn't it? The files are created by exporting information via a 'distributed solution' from Dynamics GP (accounting program) and each export uses a different template located on a server (shared directory but unmapped). The macro I am trying to write will be part of the document created by the second export but needs to grab the data from the first export and append. The first export does not need to be saved and the second export will be saved or closed without saving depending on the user. It is almost like the exports create files that are two separate instances of Excel and one cannot see the other. Using Workbooks('index') doesn't work nor does trying .Activate (subscript out of bounds) with or without the file extension. It appears that I may have to save, close and re-open each file before manipulating the data to get it to work. This just seems so cumbersome. Any suggestions that may overcome this problem would be appreciated. I really don't want to do the convoluted 'save/close/open' approach if at all possible. Thanks "Dave Peterson" wrote: Don't rely on the name of the new workbook. Set a variable that represents those workbooks when you create them. dim wkbk1 as workbook set wkbk1 = workbooks.add(template:="c:\yourtemplate.xlt") 'later... wkbk1.activate Remember that you don't have to activate/select most things to work on them. wkbk1.activate worksheets(1).select range("A1").select selection.value = "hi there" could be replaced with: wkbk1.worksheets(1).range("A1").value = "hi there" JC wrote: Do the workbooks have to be saved before you can activate a workbook using the Workbooks("abc.xls").Activate command? I can find nothing that suggests that they need to be. I have two workbooks created from a template that at the time of running the macro will be unsaved. When attempting to use the Workbooks.Activate command I get a subscript out of range error which I am assuming relates to the fact that they are not saved files, however they are open. Any assistance to a learner would be greatly appreciated. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activating Open Workbooks using VBA
thank you for your assistance, but I am still having issues and I think a
little more information may assist. The problem with using the workbook.add statement as suggested by another respondee is that the files already exist and the .add command creates them doesn't it? The files are created by exporting information via a 'distributed solution' from Dynamics GP (accounting program) and each export uses a different template located on a server (shared directory but unmapped). The macro I am trying to write will be part of the document created by the second export but needs to grab the data from the first export and append. The first export does not need to be saved and the second export will be saved or closed without saving depending on the user. It is almost like the exports create files that are two separate instances of Excel and one cannot see the other. Using Workbooks('index') doesn't work nor does trying .Activate (subscript out of bounds) with or without the file extension. It appears that I may have to save, close and re-open each file before manipulating the data to get it to work. This just seems so cumbersome. Any suggestions that may overcome this problem would be appreciated. I really don't want to do the convoluted 'save/close/open' approach if at all possible. Thanks "Jacob Skaria" wrote: 'For unsaved workbooks the extension will not be there. Workbooks("book1").Activate 'For saved workbooks use the extension Workbooks("book1.xls").Activate If this post helps click Yes --------------- Jacob Skaria "JC" wrote: Do the workbooks have to be saved before you can activate a workbook using the Workbooks("abc.xls").Activate command? I can find nothing that suggests that they need to be. I have two workbooks created from a template that at the time of running the macro will be unsaved. When attempting to use the Workbooks.Activate command I get a subscript out of range error which I am assuming relates to the fact that they are not saved files, however they are open. Any assistance to a learner would be greatly appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activating Open Workbooks using VBA
workbooks.add will create a new workbook in excel. It won't be a file until
it's actually saved to disk. If you want to open an existing file, then workbooks.open would be used. But I don't understand your requirements. You can open a workbook in readonly mode so that you don't save using that name and others can have it open. But if the file that's generated is really a template (*.xlt*), then it wouldn't matter if you opened and closed--or created a new workbook based on that template (and closed without saving). JC wrote: thank you for your assistance, but I am still having issues and I think a little more information may assist. The problem with using the workbook.add statement is that the files already exist and the .add command creates them doesn't it? The files are created by exporting information via a 'distributed solution' from Dynamics GP (accounting program) and each export uses a different template located on a server (shared directory but unmapped). The macro I am trying to write will be part of the document created by the second export but needs to grab the data from the first export and append. The first export does not need to be saved and the second export will be saved or closed without saving depending on the user. It is almost like the exports create files that are two separate instances of Excel and one cannot see the other. Using Workbooks('index') doesn't work nor does trying .Activate (subscript out of bounds) with or without the file extension. It appears that I may have to save, close and re-open each file before manipulating the data to get it to work. This just seems so cumbersome. Any suggestions that may overcome this problem would be appreciated. I really don't want to do the convoluted 'save/close/open' approach if at all possible. Thanks "Dave Peterson" wrote: Don't rely on the name of the new workbook. Set a variable that represents those workbooks when you create them. dim wkbk1 as workbook set wkbk1 = workbooks.add(template:="c:\yourtemplate.xlt") 'later... wkbk1.activate Remember that you don't have to activate/select most things to work on them. wkbk1.activate worksheets(1).select range("A1").select selection.value = "hi there" could be replaced with: wkbk1.worksheets(1).range("A1").value = "hi there" JC wrote: Do the workbooks have to be saved before you can activate a workbook using the Workbooks("abc.xls").Activate command? I can find nothing that suggests that they need to be. I have two workbooks created from a template that at the time of running the macro will be unsaved. When attempting to use the Workbooks.Activate command I get a subscript out of range error which I am assuming relates to the fact that they are not saved files, however they are open. Any assistance to a learner would be greatly appreciated. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activating Open Workbooks using VBA
Thanks Dave - The issue is I have two unsaved excel workbooks open created
from templates and neither can see the other. I have solved the problem by saving the first export and closing it and then opening it via the macro in the second export. This works, but it just seems to be so clumsy. What I can't work out is why each export workbook cannot see the other and cannot be referenced via VBA until one is saved and reopened. This doesn't happen under normal circumstances with unsaved workbooks which is causing the confusion. Again thanks for all of your help. "Dave Peterson" wrote: workbooks.add will create a new workbook in excel. It won't be a file until it's actually saved to disk. If you want to open an existing file, then workbooks.open would be used. But I don't understand your requirements. You can open a workbook in readonly mode so that you don't save using that name and others can have it open. But if the file that's generated is really a template (*.xlt*), then it wouldn't matter if you opened and closed--or created a new workbook based on that template (and closed without saving). JC wrote: thank you for your assistance, but I am still having issues and I think a little more information may assist. The problem with using the workbook.add statement is that the files already exist and the .add command creates them doesn't it? The files are created by exporting information via a 'distributed solution' from Dynamics GP (accounting program) and each export uses a different template located on a server (shared directory but unmapped). The macro I am trying to write will be part of the document created by the second export but needs to grab the data from the first export and append. The first export does not need to be saved and the second export will be saved or closed without saving depending on the user. It is almost like the exports create files that are two separate instances of Excel and one cannot see the other. Using Workbooks('index') doesn't work nor does trying .Activate (subscript out of bounds) with or without the file extension. It appears that I may have to save, close and re-open each file before manipulating the data to get it to work. This just seems so cumbersome. Any suggestions that may overcome this problem would be appreciated. I really don't want to do the convoluted 'save/close/open' approach if at all possible. Thanks "Dave Peterson" wrote: Don't rely on the name of the new workbook. Set a variable that represents those workbooks when you create them. dim wkbk1 as workbook set wkbk1 = workbooks.add(template:="c:\yourtemplate.xlt") 'later... wkbk1.activate Remember that you don't have to activate/select most things to work on them. wkbk1.activate worksheets(1).select range("A1").select selection.value = "hi there" could be replaced with: wkbk1.worksheets(1).range("A1").value = "hi there" JC wrote: Do the workbooks have to be saved before you can activate a workbook using the Workbooks("abc.xls").Activate command? I can find nothing that suggests that they need to be. I have two workbooks created from a template that at the time of running the macro will be unsaved. When attempting to use the Workbooks.Activate command I get a subscript out of range error which I am assuming relates to the fact that they are not saved files, however they are open. Any assistance to a learner would be greatly appreciated. -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activating Open Workbooks using VBA
I'm not sure what you're doing, but I would have guessed that you had a third
workbook (containing the macro) that would be doing the controlling of each of the other two workbooks. If you're calling code in one of those templates, then you'll have to give that code someway of knowing what the "second" workbook is. Maybe you could look through the names of the workbooks--or look for a (hidden) name in the second workbook or even just have the first template open/create the second workbook. JC wrote: Thanks Dave - The issue is I have two unsaved excel workbooks open created from templates and neither can see the other. I have solved the problem by saving the first export and closing it and then opening it via the macro in the second export. This works, but it just seems to be so clumsy. What I can't work out is why each export workbook cannot see the other and cannot be referenced via VBA until one is saved and reopened. This doesn't happen under normal circumstances with unsaved workbooks which is causing the confusion. Again thanks for all of your help. "Dave Peterson" wrote: workbooks.add will create a new workbook in excel. It won't be a file until it's actually saved to disk. If you want to open an existing file, then workbooks.open would be used. But I don't understand your requirements. You can open a workbook in readonly mode so that you don't save using that name and others can have it open. But if the file that's generated is really a template (*.xlt*), then it wouldn't matter if you opened and closed--or created a new workbook based on that template (and closed without saving). JC wrote: thank you for your assistance, but I am still having issues and I think a little more information may assist. The problem with using the workbook.add statement is that the files already exist and the .add command creates them doesn't it? The files are created by exporting information via a 'distributed solution' from Dynamics GP (accounting program) and each export uses a different template located on a server (shared directory but unmapped). The macro I am trying to write will be part of the document created by the second export but needs to grab the data from the first export and append. The first export does not need to be saved and the second export will be saved or closed without saving depending on the user. It is almost like the exports create files that are two separate instances of Excel and one cannot see the other. Using Workbooks('index') doesn't work nor does trying .Activate (subscript out of bounds) with or without the file extension. It appears that I may have to save, close and re-open each file before manipulating the data to get it to work. This just seems so cumbersome. Any suggestions that may overcome this problem would be appreciated. I really don't want to do the convoluted 'save/close/open' approach if at all possible. Thanks "Dave Peterson" wrote: Don't rely on the name of the new workbook. Set a variable that represents those workbooks when you create them. dim wkbk1 as workbook set wkbk1 = workbooks.add(template:="c:\yourtemplate.xlt") 'later... wkbk1.activate Remember that you don't have to activate/select most things to work on them. wkbk1.activate worksheets(1).select range("A1").select selection.value = "hi there" could be replaced with: wkbk1.worksheets(1).range("A1").value = "hi there" JC wrote: Do the workbooks have to be saved before you can activate a workbook using the Workbooks("abc.xls").Activate command? I can find nothing that suggests that they need to be. I have two workbooks created from a template that at the time of running the macro will be unsaved. When attempting to use the Workbooks.Activate command I get a subscript out of range error which I am assuming relates to the fact that they are not saved files, however they are open. Any assistance to a learner would be greatly appreciated. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activating Open Workbooks using VBA
I think the problem lies in the export process. The data is obtained by
exporting from Dynamics GP (2 separate exports to Excel required to obtain data hence the two workbooks) and it is not possible to create the export workbook by calling it from the excel template (or at least with my level of expertise and with the complexity of GP). There are no hidden workbooks that I can establish. To all intents and purposes Workbooks("filename").Activate should work! I will leave it with my solution which works and thank you for your contribution and help. "Dave Peterson" wrote: I'm not sure what you're doing, but I would have guessed that you had a third workbook (containing the macro) that would be doing the controlling of each of the other two workbooks. If you're calling code in one of those templates, then you'll have to give that code someway of knowing what the "second" workbook is. Maybe you could look through the names of the workbooks--or look for a (hidden) name in the second workbook or even just have the first template open/create the second workbook. JC wrote: Thanks Dave - The issue is I have two unsaved excel workbooks open created from templates and neither can see the other. I have solved the problem by saving the first export and closing it and then opening it via the macro in the second export. This works, but it just seems to be so clumsy. What I can't work out is why each export workbook cannot see the other and cannot be referenced via VBA until one is saved and reopened. This doesn't happen under normal circumstances with unsaved workbooks which is causing the confusion. Again thanks for all of your help. "Dave Peterson" wrote: workbooks.add will create a new workbook in excel. It won't be a file until it's actually saved to disk. If you want to open an existing file, then workbooks.open would be used. But I don't understand your requirements. You can open a workbook in readonly mode so that you don't save using that name and others can have it open. But if the file that's generated is really a template (*.xlt*), then it wouldn't matter if you opened and closed--or created a new workbook based on that template (and closed without saving). JC wrote: thank you for your assistance, but I am still having issues and I think a little more information may assist. The problem with using the workbook.add statement is that the files already exist and the .add command creates them doesn't it? The files are created by exporting information via a 'distributed solution' from Dynamics GP (accounting program) and each export uses a different template located on a server (shared directory but unmapped). The macro I am trying to write will be part of the document created by the second export but needs to grab the data from the first export and append. The first export does not need to be saved and the second export will be saved or closed without saving depending on the user. It is almost like the exports create files that are two separate instances of Excel and one cannot see the other. Using Workbooks('index') doesn't work nor does trying .Activate (subscript out of bounds) with or without the file extension. It appears that I may have to save, close and re-open each file before manipulating the data to get it to work. This just seems so cumbersome. Any suggestions that may overcome this problem would be appreciated. I really don't want to do the convoluted 'save/close/open' approach if at all possible. Thanks "Dave Peterson" wrote: Don't rely on the name of the new workbook. Set a variable that represents those workbooks when you create them. dim wkbk1 as workbook set wkbk1 = workbooks.add(template:="c:\yourtemplate.xlt") 'later... wkbk1.activate Remember that you don't have to activate/select most things to work on them. wkbk1.activate worksheets(1).select range("A1").select selection.value = "hi there" could be replaced with: wkbk1.worksheets(1).range("A1").value = "hi there" JC wrote: Do the workbooks have to be saved before you can activate a workbook using the Workbooks("abc.xls").Activate command? I can find nothing that suggests that they need to be. I have two workbooks created from a template that at the time of running the macro will be unsaved. When attempting to use the Workbooks.Activate command I get a subscript out of range error which I am assuming relates to the fact that they are not saved files, however they are open. Any assistance to a learner would be greatly appreciated. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Activating Workbooks | Excel Discussion (Misc queries) | |||
activating workbooks | Excel Discussion (Misc queries) | |||
Activating sheets with same name in multiple workbooks | Excel Programming | |||
activating workbooks | Excel Programming | |||
Hide/Show modeless userform when activating/deactivating workbooks | Excel Programming |