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