Hi,


I am using Excel 2007. I have created a file call Template.xlsm that I use
as a template for generating a report. In the Workbook_Open() event form
Template.xlsm, I call Application.Run "BatchReport". "BatchReport" is a Sub
I created that has some code to pull data from a database and put it in the
right places. After the data is returned, all links to the database are
broken so that all data in the workbook is now static. Next, I call
ActiveWorkbook.SaveAs to save this workbook to a new filename.

This works great. My only problem is that in my newly created file, the
Workbook_Open() event still calls sub "BatchReport" (which still exists). If
possible, I would like to both (1) remove the call to sub "BatchReport" in
Workbook_Open() and (2) remove sub "BatchReport" entirely.

Any help or advice would be greatly appreciated.

Thanks in advance,
Paul

--
Paul Kraemer

Save the file as xlsx and the code is gone

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Paul Kraemer" wrote in message ...
Hi,


I am using Excel 2007. I have created a file call Template.xlsm that I use
as a template for generating a report. In the Workbook_Open() event form
Template.xlsm, I call Application.Run "BatchReport". "BatchReport" is a Sub
I created that has some code to pull data from a database and put it in the
right places. After the data is returned, all links to the database are
broken so that all data in the workbook is now static. Next, I call
ActiveWorkbook.SaveAs to save this workbook to a new filename.

This works great. My only problem is that in my newly created file, the
Workbook_Open() event still calls sub "BatchReport" (which still exists). If
possible, I would like to both (1) remove the call to sub "BatchReport" in
Workbook_Open() and (2) remove sub "BatchReport" entirely.

Any help or advice would be greatly appreciated.

Thanks in advance,
Paul

--
Paul Kraemer

Thanks Ron,

That worked great. I just have one more question....what if I wanted to do
the same thing as far as stripping the code, but I wanted to save to Excel
97-2003 format (instead of Excel 2007).

Where Excel 2007 seems to have two different file formats, .xlsm with macros
and .xlsx without macros, it doesn't appear that Excel 97-2003 has the same
thing.

Is there a similar way that I can save to Excel 97-2003 .xls while removing
the code at the same time?

Thanks again,
Paul
--
Paul Kraemer


"Ron de Bruin" wrote:

Save the file as xlsx and the code is gone

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Paul Kraemer" wrote in message ...
Hi,


I am using Excel 2007. I have created a file call Template.xlsm that I use
as a template for generating a report. In the Workbook_Open() event form
Template.xlsm, I call Application.Run "BatchReport". "BatchReport" is a Sub
I created that has some code to pull data from a database and put it in the
right places. After the data is returned, all links to the database are
broken so that all data in the workbook is now static. Next, I call
ActiveWorkbook.SaveAs to save this workbook to a new filename.

This works great. My only problem is that in my newly created file, the
Workbook_Open() event still calls sub "BatchReport" (which still exists). If
possible, I would like to both (1) remove the call to sub "BatchReport" in
Workbook_Open() and (2) remove sub "BatchReport" entirely.

Any help or advice would be greatly appreciated.

Thanks in advance,
Paul

--
Paul Kraemer

Check out Chip Pearson's "Programming the VB editor" lots of great code
ther to do exactly what you want!
'Programming In The VBA Editor'
(http://www.cpearson.com/excel/vbe.aspx)

Paul Kraemer;498554 Wrote:
Thanks Ron,

That worked great. I just have one more question....what if I wanted to
do
the same thing as far as stripping the code, but I wanted to save to
Excel
97-2003 format (instead of Excel 2007).

Where Excel 2007 seems to have two different file formats, .xlsm with
macros
and .xlsx without macros, it doesn't appear that Excel 97-2003 has the
same
thing.

Is there a similar way that I can save to Excel 97-2003 .xls while
removing
the code at the same time?

Thanks again,
Paul
--
Paul Kraemer


"Ron de Bruin" wrote:

Save the file as xlsx and the code is gone

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Paul Kraemer" wrote in
message ...
Hi,


I am using Excel 2007. I have created a file call Template.xlsm
that I use
as a template for generating a report. In the Workbook_Open() event
form
Template.xlsm, I call Application.Run "BatchReport". "BatchReport"
is a Sub
I created that has some code to pull data from a database and put
it in the
right places. After the data is returned, all links to the database
are
broken so that all data in the workbook is now static. Next, I call
ActiveWorkbook.SaveAs to save this workbook to a new filename.

This works great. My only problem is that in my newly created file,
the
Workbook_Open() event still calls sub "BatchReport" (which still
exists). If
possible, I would like to both (1) remove the call to sub
"BatchReport" in
Workbook_Open() and (2) remove sub "BatchReport" entirely.

Any help or advice would be greatly appreciated.

Thanks in advance,
Paul

--
Paul Kraemer



--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=137215

Have you thought about saving as a .xlsx first, then closing, reopening and
saving as a xl97-xl2003 again. You could delete the interim .xlsx file later.

Paul Kraemer wrote:

Thanks Ron,

That worked great. I just have one more question....what if I wanted to do
the same thing as far as stripping the code, but I wanted to save to Excel
97-2003 format (instead of Excel 2007).

Where Excel 2007 seems to have two different file formats, .xlsm with macros
and .xlsx without macros, it doesn't appear that Excel 97-2003 has the same
thing.

Is there a similar way that I can save to Excel 97-2003 .xls while removing
the code at the same time?

Thanks again,
Paul
--
Paul Kraemer

"Ron de Bruin" wrote:

Save the file as xlsx and the code is gone

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Paul Kraemer" wrote in message ...
Hi,


I am using Excel 2007. I have created a file call Template.xlsm that I use
as a template for generating a report. In the Workbook_Open() event form
Template.xlsm, I call Application.Run "BatchReport". "BatchReport" is a Sub
I created that has some code to pull data from a database and put it in the
right places. After the data is returned, all links to the database are
broken so that all data in the workbook is now static. Next, I call
ActiveWorkbook.SaveAs to save this workbook to a new filename.

This works great. My only problem is that in my newly created file, the
Workbook_Open() event still calls sub "BatchReport" (which still exists). If
possible, I would like to both (1) remove the call to sub "BatchReport" in
Workbook_Open() and (2) remove sub "BatchReport" entirely.

Any help or advice would be greatly appreciated.

Thanks in advance,
Paul

--
Paul Kraemer


--

Dave Peterson

Paul,

The example between the dotted lines below assumes you have a workbook with
three sheets. A new workbook will be created with identical copies of those
three sheets, none of the VBA retained and the workbook saved as Excel
97-2003 with an xls extension and closed. Since I didn't specify a path for
the file name, the file will be saved to the current folder.

'------------------------------------------------------

Sub SaveWithoutMacro()

Dim intOpens As Integer

intOpens = Application.Workbooks.Count

Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy

Set objNewBook = Application.Workbooks(intOpens + 1)
objNewBook.Activate
objNewBook.SaveAs Filename:="MyNewBook.xls", FileFormat:=xlExcel8
objNewBook.Close

End Sub



'------------------------------------------------------

Steve Yandl




"Paul Kraemer" wrote in message
...
Thanks Ron,

That worked great. I just have one more question....what if I wanted to
do
the same thing as far as stripping the code, but I wanted to save to Excel
97-2003 format (instead of Excel 2007).

Where Excel 2007 seems to have two different file formats, .xlsm with
macros
and .xlsx without macros, it doesn't appear that Excel 97-2003 has the
same
thing.

Is there a similar way that I can save to Excel 97-2003 .xls while
removing
the code at the same time?

Thanks again,
Paul
--
Paul Kraemer


"Ron de Bruin" wrote:

Save the file as xlsx and the code is gone

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Paul Kraemer" wrote in message
...
Hi,


I am using Excel 2007. I have created a file call Template.xlsm that I
use
as a template for generating a report. In the Workbook_Open() event
form
Template.xlsm, I call Application.Run "BatchReport". "BatchReport" is
a Sub
I created that has some code to pull data from a database and put it in
the
right places. After the data is returned, all links to the database
are
broken so that all data in the workbook is now static. Next, I call
ActiveWorkbook.SaveAs to save this workbook to a new filename.

This works great. My only problem is that in my newly created file,
the
Workbook_Open() event still calls sub "BatchReport" (which still
exists). If
possible, I would like to both (1) remove the call to sub "BatchReport"
in
Workbook_Open() and (2) remove sub "BatchReport" entirely.

Any help or advice would be greatly appreciated.

Thanks in advance,
Paul

--
Paul Kraemer

Hi Steve,

That seems pretty straightforward. I just have one question : I see where
you copy the three worksheets - shouldn't there be a "paste" somewhere in
there?

Thanks for your help.
Paul
--
Paul Kraemer


"Steve Yandl" wrote:

Paul,

The example between the dotted lines below assumes you have a workbook with
three sheets. A new workbook will be created with identical copies of those
three sheets, none of the VBA retained and the workbook saved as Excel
97-2003 with an xls extension and closed. Since I didn't specify a path for
the file name, the file will be saved to the current folder.

'------------------------------------------------------

Sub SaveWithoutMacro()

Dim intOpens As Integer

intOpens = Application.Workbooks.Count

Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy

Set objNewBook = Application.Workbooks(intOpens + 1)
objNewBook.Activate
objNewBook.SaveAs Filename:="MyNewBook.xls", FileFormat:=xlExcel8
objNewBook.Close

End Sub



'------------------------------------------------------

Steve Yandl




"Paul Kraemer" wrote in message
...
Thanks Ron,

That worked great. I just have one more question....what if I wanted to
do
the same thing as far as stripping the code, but I wanted to save to Excel
97-2003 format (instead of Excel 2007).

Where Excel 2007 seems to have two different file formats, .xlsm with
macros
and .xlsx without macros, it doesn't appear that Excel 97-2003 has the
same
thing.

Is there a similar way that I can save to Excel 97-2003 .xls while
removing
the code at the same time?

Thanks again,
Paul
--
Paul Kraemer


"Ron de Bruin" wrote:

Save the file as xlsx and the code is gone

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Paul Kraemer" wrote in message
...
Hi,


I am using Excel 2007. I have created a file call Template.xlsm that I
use
as a template for generating a report. In the Workbook_Open() event
form
Template.xlsm, I call Application.Run "BatchReport". "BatchReport" is
a Sub
I created that has some code to pull data from a database and put it in
the
right places. After the data is returned, all links to the database
are
broken so that all data in the workbook is now static. Next, I call
ActiveWorkbook.SaveAs to save this workbook to a new filename.

This works great. My only problem is that in my newly created file,
the
Workbook_Open() event still calls sub "BatchReport" (which still
exists). If
possible, I would like to both (1) remove the call to sub "BatchReport"
in
Workbook_Open() and (2) remove sub "BatchReport" entirely.

Any help or advice would be greatly appreciated.

Thanks in advance,
Paul

--
Paul Kraemer

Paul,

That's the key. It certainly isn't intuitive but using 'Copy' without
'Paste' will create a new workbook from the sheets copied (if you only
copied one sheet it will be a workbook with a single sheet, regardless of
your default setting for new workbooks). Data and all sheet formatting will
be retained but your VBA containing modules are left behind.

Steve Yandl



"Paul Kraemer" wrote in message
...
Hi Steve,

That seems pretty straightforward. I just have one question : I see where
you copy the three worksheets - shouldn't there be a "paste" somewhere in
there?

Thanks for your help.
Paul
--
Paul Kraemer


"Steve Yandl" wrote:

Paul,

The example between the dotted lines below assumes you have a workbook
with
three sheets. A new workbook will be created with identical copies of
those
three sheets, none of the VBA retained and the workbook saved as Excel
97-2003 with an xls extension and closed. Since I didn't specify a path
for
the file name, the file will be saved to the current folder.

'------------------------------------------------------

Sub SaveWithoutMacro()

Dim intOpens As Integer

intOpens = Application.Workbooks.Count

Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy

Set objNewBook = Application.Workbooks(intOpens + 1)
objNewBook.Activate
objNewBook.SaveAs Filename:="MyNewBook.xls", FileFormat:=xlExcel8
objNewBook.Close

End Sub



'------------------------------------------------------

Steve Yandl




"Paul Kraemer" wrote in message
...
Thanks Ron,

That worked great. I just have one more question....what if I wanted
to
do
the same thing as far as stripping the code, but I wanted to save to
Excel
97-2003 format (instead of Excel 2007).

Where Excel 2007 seems to have two different file formats, .xlsm with
macros
and .xlsx without macros, it doesn't appear that Excel 97-2003 has the
same
thing.

Is there a similar way that I can save to Excel 97-2003 .xls while
removing
the code at the same time?

Thanks again,
Paul
--
Paul Kraemer


"Ron de Bruin" wrote:

Save the file as xlsx and the code is gone

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Paul Kraemer" wrote in
message
...
Hi,


I am using Excel 2007. I have created a file call Template.xlsm
that I
use
as a template for generating a report. In the Workbook_Open() event
form
Template.xlsm, I call Application.Run "BatchReport". "BatchReport"
is
a Sub
I created that has some code to pull data from a database and put it
in
the
right places. After the data is returned, all links to the database
are
broken so that all data in the workbook is now static. Next, I call
ActiveWorkbook.SaveAs to save this workbook to a new filename.

This works great. My only problem is that in my newly created file,
the
Workbook_Open() event still calls sub "BatchReport" (which still
exists). If
possible, I would like to both (1) remove the call to sub
"BatchReport"
in
Workbook_Open() and (2) remove sub "BatchReport" entirely.

Any help or advice would be greatly appreciated.

Thanks in advance,
Paul

--
Paul Kraemer

Paul,

I should also point out that VBA attached to any of the sheets you opt to
copy will carry over to the new book. Your 'workbook_open' sub or any other
code attached to the template workbook will be left behind along with code
in modules and userforms.


Steve



"Steve Yandl" wrote in message
...
Paul,

That's the key. It certainly isn't intuitive but using 'Copy' without
'Paste' will create a new workbook from the sheets copied (if you only
copied one sheet it will be a workbook with a single sheet, regardless of
your default setting for new workbooks). Data and all sheet formatting
will be retained but your VBA containing modules are left behind.

Steve Yandl



"Paul Kraemer" wrote in message
...
Hi Steve,

That seems pretty straightforward. I just have one question : I see
where
you copy the three worksheets - shouldn't there be a "paste" somewhere in
there?

Thanks for your help.
Paul
--
Paul Kraemer


"Steve Yandl" wrote:

Paul,

The example between the dotted lines below assumes you have a workbook
with
three sheets. A new workbook will be created with identical copies of
those
three sheets, none of the VBA retained and the workbook saved as Excel
97-2003 with an xls extension and closed. Since I didn't specify a path
for
the file name, the file will be saved to the current folder.

'------------------------------------------------------

Sub SaveWithoutMacro()

Dim intOpens As Integer

intOpens = Application.Workbooks.Count

Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy

Set objNewBook = Application.Workbooks(intOpens + 1)
objNewBook.Activate
objNewBook.SaveAs Filename:="MyNewBook.xls", FileFormat:=xlExcel8
objNewBook.Close

End Sub



'------------------------------------------------------

Steve Yandl




"Paul Kraemer" wrote in message
...
Thanks Ron,

That worked great. I just have one more question....what if I wanted
to
do
the same thing as far as stripping the code, but I wanted to save to
Excel
97-2003 format (instead of Excel 2007).

Where Excel 2007 seems to have two different file formats, .xlsm with
macros
and .xlsx without macros, it doesn't appear that Excel 97-2003 has the
same
thing.

Is there a similar way that I can save to Excel 97-2003 .xls while
removing
the code at the same time?

Thanks again,
Paul
--
Paul Kraemer


"Ron de Bruin" wrote:

Save the file as xlsx and the code is gone

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Paul Kraemer" wrote in
message
...
Hi,


I am using Excel 2007. I have created a file call Template.xlsm
that I
use
as a template for generating a report. In the Workbook_Open()
event
form
Template.xlsm, I call Application.Run "BatchReport". "BatchReport"
is
a Sub
I created that has some code to pull data from a database and put
it in
the
right places. After the data is returned, all links to the
database
are
broken so that all data in the workbook is now static. Next, I
call
ActiveWorkbook.SaveAs to save this workbook to a new filename.

This works great. My only problem is that in my newly created
file,
the
Workbook_Open() event still calls sub "BatchReport" (which still
exists). If
possible, I would like to both (1) remove the call to sub
"BatchReport"
in
Workbook_Open() and (2) remove sub "BatchReport" entirely.

Any help or advice would be greatly appreciated.

Thanks in advance,
Paul

--
Paul Kraemer

Thank you Steve - I tried it out and it did exactly what I wanted!
--
Paul Kraemer


"Steve Yandl" wrote:

Paul,

I should also point out that VBA attached to any of the sheets you opt to
copy will carry over to the new book. Your 'workbook_open' sub or any other
code attached to the template workbook will be left behind along with code
in modules and userforms.


Steve



"Steve Yandl" wrote in message
...
Paul,

That's the key. It certainly isn't intuitive but using 'Copy' without
'Paste' will create a new workbook from the sheets copied (if you only
copied one sheet it will be a workbook with a single sheet, regardless of
your default setting for new workbooks). Data and all sheet formatting
will be retained but your VBA containing modules are left behind.

Steve Yandl



"Paul Kraemer" wrote in message
...
Hi Steve,

That seems pretty straightforward. I just have one question : I see
where
you copy the three worksheets - shouldn't there be a "paste" somewhere in
there?

Thanks for your help.
Paul
--
Paul Kraemer


"Steve Yandl" wrote:

Paul,

The example between the dotted lines below assumes you have a workbook
with
three sheets. A new workbook will be created with identical copies of
those
three sheets, none of the VBA retained and the workbook saved as Excel
97-2003 with an xls extension and closed. Since I didn't specify a path
for
the file name, the file will be saved to the current folder.

'------------------------------------------------------

Sub SaveWithoutMacro()

Dim intOpens As Integer

intOpens = Application.Workbooks.Count

Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy

Set objNewBook = Application.Workbooks(intOpens + 1)
objNewBook.Activate
objNewBook.SaveAs Filename:="MyNewBook.xls", FileFormat:=xlExcel8
objNewBook.Close

End Sub



'------------------------------------------------------

Steve Yandl




"Paul Kraemer" wrote in message
...
Thanks Ron,

That worked great. I just have one more question....what if I wanted
to
do
the same thing as far as stripping the code, but I wanted to save to
Excel
97-2003 format (instead of Excel 2007).

Where Excel 2007 seems to have two different file formats, .xlsm with
macros
and .xlsx without macros, it doesn't appear that Excel 97-2003 has the
same
thing.

Is there a similar way that I can save to Excel 97-2003 .xls while
removing
the code at the same time?

Thanks again,
Paul
--
Paul Kraemer


"Ron de Bruin" wrote:

Save the file as xlsx and the code is gone

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Paul Kraemer" wrote in
message
...
Hi,


I am using Excel 2007. I have created a file call Template.xlsm
that I
use
as a template for generating a report. In the Workbook_Open()
event
form
Template.xlsm, I call Application.Run "BatchReport". "BatchReport"
is
a Sub
I created that has some code to pull data from a database and put
it in
the
right places. After the data is returned, all links to the
database
are
broken so that all data in the workbook is now static. Next, I
call
ActiveWorkbook.SaveAs to save this workbook to a new filename.

This works great. My only problem is that in my newly created
file,
the
Workbook_Open() event still calls sub "BatchReport" (which still
exists). If
possible, I would like to both (1) remove the call to sub
"BatchReport"
in
Workbook_Open() and (2) remove sub "BatchReport" entirely.

Any help or advice would be greatly appreciated.

Thanks in advance,
Paul

--
Paul Kraemer