I am developing an application which will use an Excel
Workbook that has only a menu which, amoung other things
will allow me to open either a specific workbook
previously created or a new workbook. If I create a new
workbook I am using an Excel template for that. In the
template I have code which will need to execute to set
conditions and to prepare the resulting workbook to accept
new data from my user. The problem I am having is, I need
to execute one of two macros within the workbook depending
on if the user chooses to open an existing wlorkbook or a
new workbook. I have no problem if the user chooses an
existing workbook because I know the name of the file.
But...if the user chooses to create a new workbook, when
the template opens, before any events fire it creates a
new workbook. If my template is named Test the new
workbook is named Test1. If I finish the data entry for
that workbook and choose to create another new workbook, I
get Test2, etc. The numbering resets only after I close
the menu workbook and re-open it. My problem comes in my
attempt to execute the macro in the resulting new file. I
do not know (in code) what it will be. I guess I could use
some sort of counter, but that has potential problems.

Any suggestions? How do I execute a macro in a workbook
where I do not know for sure what the name of the new
workbook will be until after my code executes?

The problem this creates is I get an error which is run-
time error 424, object required when I execute the
Application.Run command. The problem is that the new
workbook has been created and the template has been closed
(automatically by Excel). Only after the Excel creates the
new workbook from the template do I have an opportunity to
determine the new workbook name.

Thanks!

Kevin

Not sure if I follow exactly, but if the new workbook is created won'
it become the active workbook for a time until you switch back to you
template. If so, you can use ActiveWorkbook.Name to store the workboo
name.

Or I could be just blowing smoke...



--
kkkni
-----------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...nfo&userid=754
View this thread: http://www.excelforum.com/showthread.php?threadid=26293

Only after the Excel creates the
new workbook from the template do I have an opportunity to
determine the new workbook name

Why is that a problem. You can't run the macro untile the newworkbook is
created anyway? It seems like the next command after

Workbooks.Add

would be
Application.Run "'" & activeWorkbook.name & "'!MyMarco"

--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
I am developing an application which will use an Excel
Workbook that has only a menu which, amoung other things
will allow me to open either a specific workbook
previously created or a new workbook. If I create a new
workbook I am using an Excel template for that. In the
template I have code which will need to execute to set
conditions and to prepare the resulting workbook to accept
new data from my user. The problem I am having is, I need
to execute one of two macros within the workbook depending
on if the user chooses to open an existing wlorkbook or a
new workbook. I have no problem if the user chooses an
existing workbook because I know the name of the file.
But...if the user chooses to create a new workbook, when
the template opens, before any events fire it creates a
new workbook. If my template is named Test the new
workbook is named Test1. If I finish the data entry for
that workbook and choose to create another new workbook, I
get Test2, etc. The numbering resets only after I close
the menu workbook and re-open it. My problem comes in my
attempt to execute the macro in the resulting new file. I
do not know (in code) what it will be. I guess I could use
some sort of counter, but that has potential problems.

Any suggestions? How do I execute a macro in a workbook
where I do not know for sure what the name of the new
workbook will be until after my code executes?

The problem this creates is I get an error which is run-
time error 424, object required when I execute the
Application.Run command. The problem is that the new
workbook has been created and the template has been closed
(automatically by Excel). Only after the Excel creates the
new workbook from the template do I have an opportunity to
determine the new workbook name.

Thanks!

Kevin

That fixed it! Thanks! Sometimes my brain goes south. That
happened here! As soon as I saw your suggestions I
realized I was making the problem out to be more difficult
than it really was.

Thanks again!

Kevin
-----Original Message-----
I am developing an application which will use an Excel
Workbook that has only a menu which, amoung other things
will allow me to open either a specific workbook
previously created or a new workbook. If I create a new
workbook I am using an Excel template for that. In the
template I have code which will need to execute to set
conditions and to prepare the resulting workbook to
accept
new data from my user. The problem I am having is, I need
to execute one of two macros within the workbook
depending
on if the user chooses to open an existing wlorkbook or a
new workbook. I have no problem if the user chooses an
existing workbook because I know the name of the file.
But...if the user chooses to create a new workbook, when
the template opens, before any events fire it creates a
new workbook. If my template is named Test the new
workbook is named Test1. If I finish the data entry for
that workbook and choose to create another new workbook,
I
get Test2, etc. The numbering resets only after I close
the menu workbook and re-open it. My problem comes in my
attempt to execute the macro in the resulting new file. I
do not know (in code) what it will be. I guess I could
use
some sort of counter, but that has potential problems.

Any suggestions? How do I execute a macro in a workbook
where I do not know for sure what the name of the new
workbook will be until after my code executes?

The problem this creates is I get an error which is run-
time error 424, object required when I execute the
Application.Run command. The problem is that the new
workbook has been created and the template has been
closed
(automatically by Excel). Only after the Excel creates
the
new workbook from the template do I have an opportunity
to
determine the new workbook name.

Thanks!

Kevin


.