Excel 2007 Win 7 64-bit
I have a wb with VBA code that does various things. The code then savesAS
the wb in another name. At that point, I want the same code to remove all
code from that wb and save the wb. Can that be done and how? Thanks for
your time. Otto

See Chip's site for programmatically removing VBA from a workbook.

http://www.cpearson.com/excel/VBE.aspx


Gord Dibben MS Excel MVP


On Sun, 13 Dec 2009 12:48:30 -0500, "Otto Moehrbach"
wrote:

Excel 2007 Win 7 64-bit
I have a wb with VBA code that does various things. The code then savesAS
the wb in another name. At that point, I want the same code to remove all
code from that wb and save the wb. Can that be done and how? Thanks for
your time. Otto

Hi,

This would after the SaveAs routine

Sub DeleteCode()
Msg = "Are you sure you want to delete Code from " & ActiveWorkbook.Name
Response = MsgBox(Msg, vbYesNo)
If Response = vbNo Then Exit Sub
On Error Resume Next
With ActiveWorkbook.VBProject
For X = .VBComponents.Count To 1 Step -1
.VBComponents.Remove .VBComponents(X)
Next X
For X = .VBComponents.Count To 1 Step -1
.VBComponents(X).CodeModule.DeleteLines _
1, .VBComponents(X).CodeModule.CountOfLines
Next X
End With
End Sub

Mike

"Otto Moehrbach" wrote:

Excel 2007 Win 7 64-bit
I have a wb with VBA code that does various things. The code then savesAS
the wb in another name. At that point, I want the same code to remove all
code from that wb and save the wb. Can that be done and how? Thanks for
your time. Otto

.

Save the file as xlsx and all your code is gone

Maybe this page will help
http://www.rondebruin.nl/saveas.htm


--

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


"Otto Moehrbach" wrote in message ...
Excel 2007 Win 7 64-bit
I have a wb with VBA code that does various things. The code then savesAS
the wb in another name. At that point, I want the same code to remove all
code from that wb and save the wb. Can that be done and how? Thanks for
your time. Otto

The learning curve for me with E2007 shows no sign of becoming less steep,
thanks for that tip.

Mike

"Ron de Bruin" wrote:

Save the file as xlsx and all your code is gone

Maybe this page will help
http://www.rondebruin.nl/saveas.htm


--

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


"Otto Moehrbach" wrote in message ...
Excel 2007 Win 7 64-bit
I have a wb with VBA code that does various things. The code then savesAS
the wb in another name. At that point, I want the same code to remove all
code from that wb and save the wb. Can that be done and how? Thanks for
your time. Otto

.

Mike
Ditto. I'm old and there is no end to it. Otto

"Mike H" wrote in message
...
The learning curve for me with E2007 shows no sign of becoming less steep,
thanks for that tip.

Mike

"Ron de Bruin" wrote:

Save the file as xlsx and all your code is gone

Maybe this page will help
http://www.rondebruin.nl/saveas.htm


--

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


"Otto Moehrbach" wrote in message
...
Excel 2007 Win 7 64-bit
I have a wb with VBA code that does various things. The code then
savesAS
the wb in another name. At that point, I want the same code to remove
all
code from that wb and save the wb. Can that be done and how? Thanks
for
your time. Otto

.

Ron
I did that and the file now is an xlsx file. All the code is there and
it runs!! Did I miss something? Each time I save it, it warns me that I
will lose all my code. I click Yes and the code is still there. Otto

"Ron de Bruin" wrote in message
...
Save the file as xlsx and all your code is gone

Maybe this page will help
http://www.rondebruin.nl/saveas.htm


--

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


"Otto Moehrbach" wrote in message
...
Excel 2007 Win 7 64-bit
I have a wb with VBA code that does various things. The code then
savesAS the wb in another name. At that point, I want the same code to
remove all code from that wb and save the wb. Can that be done and how?
Thanks for your time. Otto

Ron
After saving it as an xlsx file, I closed the file. Closed Excel.
Opened Excel and the file and the code is gone. Otto

"Ron de Bruin" wrote in message
...
Save the file as xlsx and all your code is gone

Maybe this page will help
http://www.rondebruin.nl/saveas.htm


--

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


"Otto Moehrbach" wrote in message
...
Excel 2007 Win 7 64-bit
I have a wb with VBA code that does various things. The code then
savesAS the wb in another name. At that point, I want the same code to
remove all code from that wb and save the wb. Can that be done and how?
Thanks for your time. Otto