Remove VBA before doing .SaveAs

[Paul Kraemer]

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

 

[Ron de Bruin]

Save the file as xlsx and the code is gone

 

[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

 

[Simon Lloyd]

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

Thanks Ron

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

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

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

Thanks again
Pau
-
Paul Kraeme



message news:[email protected]..

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Dave Peterson]

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.

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

 

[Steve Yandl]

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

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]

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

 

[Steve Yandl]

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

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

 

[Steve Yandl]

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

 

[Paul Kraemer]

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