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
|
Similar Threads
