Using Office 2003 and Windows XP;

Is there any way to activate a template file after it has already been
opened (but not yet saved)?

I tried recording the file name and writing it into a hidden sheet, then use
that to activate the file, but it doesn't work, I guess because it is not yet
saved, but it seems there must be a way to activate it.

I also tried setting an object reference to the file on open, but this loses
the object reference for some reason, I think because the file being opened
is an Oracle ADI template file and certain codes run in the background to
connect to Oracle and the object reference is severed in the process.

Can I use a window name perhaps?

Your example code would be most appreciated. Thanks much in advance.

Do the open with a set statement. After a workbook open the opened workbook
is the active workbook. Set a variable to the active workbook. Later use
the variable to activate the book again

workbooks.open filename:=MyName.xlt
set templ = activeworkbook

'later in the code
templ.activate

"XP" wrote:

> Using Office 2003 and Windows XP;
>
> Is there any way to activate a template file after it has already been
> opened (but not yet saved)?
>
> I tried recording the file name and writing it into a hidden sheet, then use
> that to activate the file, but it doesn't work, I guess because it is not yet
> saved, but it seems there must be a way to activate it.
>
> I also tried setting an object reference to the file on open, but this loses
> the object reference for some reason, I think because the file being opened
> is an Oracle ADI template file and certain codes run in the background to
> connect to Oracle and the object reference is severed in the process.
>
> Can I use a window name perhaps?
>
> Your example code would be most appreciated. Thanks much in advance.

> workbooks.open filename:=MyName.xlt
> set templ = activeworkbook

Or, simpler,

Set WB = Workbooks.Open(Filename:="YourTemplate.xlt")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Joel" <(E-Mail Removed)> wrote in message
news:2240B288-99D1-4188-B40B-(E-Mail Removed)...
> Do the open with a set statement. After a workbook open the opened
> workbook
> is the active workbook. Set a variable to the active workbook. Later use
> the variable to activate the book again
>
> workbooks.open filename:=MyName.xlt
> set templ = activeworkbook
>
> 'later in the code
> templ.activate
>
> "XP" wrote:
>
>> Using Office 2003 and Windows XP;
>>
>> Is there any way to activate a template file after it has already been
>> opened (but not yet saved)?
>>
>> I tried recording the file name and writing it into a hidden sheet, then
>> use
>> that to activate the file, but it doesn't work, I guess because it is not
>> yet
>> saved, but it seems there must be a way to activate it.
>>
>> I also tried setting an object reference to the file on open, but this
>> loses
>> the object reference for some reason, I think because the file being
>> opened
>> is an Oracle ADI template file and certain codes run in the background to
>> connect to Oracle and the object reference is severed in the process.
>>
>> Can I use a window name perhaps?
>>
>> Your example code would be most appreciated. Thanks much in advance.

One more:

Dim Wkbk as workbook
set wkbk = workbooks.add(template:="C:\yourtemplate.xlt")
'then work with the wkbk variable.
Wkbk.worksheets(1).range("a1").value = "hi there"

(I've never worked with the Oracle stuff, though. Not sure how it would affect
you.)

XP wrote:
>
> Using Office 2003 and Windows XP;
>
> Is there any way to activate a template file after it has already been
> opened (but not yet saved)?
>
> I tried recording the file name and writing it into a hidden sheet, then use
> that to activate the file, but it doesn't work, I guess because it is not yet
> saved, but it seems there must be a way to activate it.
>
> I also tried setting an object reference to the file on open, but this loses
> the object reference for some reason, I think because the file being opened
> is an Oracle ADI template file and certain codes run in the background to
> connect to Oracle and the object reference is severed in the process.
>
> Can I use a window name perhaps?
>
> Your example code would be most appreciated. Thanks much in advance.

--

Dave Peterson

Thanks everyone for your posts; actually the Oracle ADI somehow causes
automation to lose/drop the reference to the template file. So using "Set
wrkBook =" did not function in any method tried.

However, I did find that I could "manually" get a reference to the template;
when the template is opened, the program writes the file name into a hidden
sheet, then the following function can re-activate the unsaved template file:

Public Function FileTemplateActivate(argFileName As String)
'FUNCTION DETERMINES IF FILE IS CURRENTLY OPEN;
Dim wrkBooks As Workbooks
Dim wrkBook As Workbook
Set wrkBooks = Application.Workbooks
For Each wrkBook In wrkBooks
If UCase(wrkBook.Name) = UCase(argFileName) Then
Workbooks(wrkBook.Name).Activate: Exit Function
Next wrkBook
End Function

Thanks to everyone for trying to help. Maybe this post will help someone else?


"Dave Peterson" wrote:

> One more:
>
> Dim Wkbk as workbook
> set wkbk = workbooks.add(template:="C:\yourtemplate.xlt")
> 'then work with the wkbk variable.
> Wkbk.worksheets(1).range("a1").value = "hi there"
>
> (I've never worked with the Oracle stuff, though. Not sure how it would affect
> you.)
>
> XP wrote:
> >
> > Using Office 2003 and Windows XP;
> >
> > Is there any way to activate a template file after it has already been
> > opened (but not yet saved)?
> >
> > I tried recording the file name and writing it into a hidden sheet, then use
> > that to activate the file, but it doesn't work, I guess because it is not yet
> > saved, but it seems there must be a way to activate it.
> >
> > I also tried setting an object reference to the file on open, but this loses
> > the object reference for some reason, I think because the file being opened
> > is an Oracle ADI template file and certain codes run in the background to
> > connect to Oracle and the object reference is severed in the process.
> >
> > Can I use a window name perhaps?
> >
> > Your example code would be most appreciated. Thanks much in advance.
>
> --
>
> Dave Peterson
>