Can I save a spreadsheet without the VBA code?

[jrice174]

I have a small VBA progam in an Excel spreadsheet that helps the user set up the worksheets. Once they save the spreadsheet to a new file there is no use for the program but it still gives the Disable/Enable Macros warning. Is there a way to save the Excel workbook without the VBA program?

 

[brengine]

If you're saving a copy of an existing workbook that contains macros, I don't know of a way to exclude/remove the macro modules.

Instead could you use your macro to create a New file? Or look into calling the macro from the personal.xls file or creating a Add-In (.xla).

Ken

 

[TheTick]

You could move or copy the worksheet to a new workbook.

 

[Denial]

Get into the VB Editor. (Tools>Macros>VisualBasicEditor will do this for you, as will the Alt-F11 shortcut.)

Code associated with "Microsoft Excel Objects" (ie worksheets etc) has to be deleted explicitly, by selecting then deleting the lines of code.

Code in modules can be similarly deleted, but that leaves the module behind, and the module's presence will cause your enable/disable message to appear on opening. To delete the module itself (as well as its contents) right-click on the module's name where it appears on the right hand side of the screen, then select "Remove..." from the options offered.

 

[IDS]

If you have Excel 2007 you could save as xlsx format, which does not save macros. You could then re-save as xls for compatibility if you wanted to.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[TheTick]

Perhaps modify your macro to create the sheet in a new workbook that has no macros.

 

[FM1]

with your original file open, open a new excel file.
in the new file go to tool--macros and click run macros. your original macro in the OPEN ORIGINAL FILE will show up. run it and save your new file with what ever name you want. close your original file

 

[jrice174]

I'm sending these files out to customers so I don't have that kind of control over the file. The customer needs the macro in the original to perform the function that is needed to hide/unhide the correct worksheets. When they save the copy of the worksheet they don't need it anymore. I change a setting so that when the customer opens the copy again the macro doesn't run, but it still gives the macros warning screen. I guess that will have to work for now. Thanks

 

[jrice174]

I recently found this which deletes a macro from a file. I haven't tested it, but here it is anyway. I found it at

http://www.ozgrid.com/VBA/delete-module.htm

Excel VBA: Delete Module After Running VBA Code. Deleting Modules via VBA Code

Delete Module via Code. See Also Delete Sheet Event Code With Code & Delete Workbook Event Code

The code below can be used to delete the module which houses the code. In other words, it deletes itself after running once.

You will have to go to Tools>Macro>Security - Trusted Publishers and check Trust access to Visual Basic Editor before running the code. Change "Module1" to suit.

Sub DeleteThisModule()

Dim vbCom As Object
MsgBox "Hi, I will delete myself "
Set vbCom = Application.VBE.ActiveVBProject.VBComponents
vbCom.Remove VBComponent:= _
vbCom.Item("Module1")

End Sub

 

[brengine]

That does work. But it won't delete code from the "ThisWorkbook" or any of the "Sheet"...uuummm...I'll call them Modules (because I'm not sure if that is the correct terminology or not).

Ken