Remove VBA before doing .SaveAs

  • Thread starter Thread starter Paul Kraemer
  • Start date Start date
P

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
 
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
 
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 said:
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
Click to expand...
 
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
 
Back
Top