Activating Open Workbooks using VBA

[JC]

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.

 

[Jacob Skaria]

'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

 

[Dave Peterson]

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]

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

 

[JC]

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

 

[Dave Peterson]

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]

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]

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.

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.

 

[JC]

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.