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

 

[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

 

[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]

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