Excel VBA Macro

I have XL sheet. I use VB to open Excel, send some numbers to Excel sheet, run a macro to format sheet, than I save the sheet. The saved sheet still contains my macro. What VBA code can I use to delete all macro's in an excel sheet?
LVL 2
PaullkhaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
bruintjeCommented:
Hi Paullkha,

take a look at the delete a procedure from a module and other handy code here

http://www.cpearson.com/excel/vbe.htm

HTH:O)Bruintje
0
 
PaullkhaAuthor Commented:
From a quick scan, might work, but

I want to use Excel objects only.
ie.
Excel.application or
Excel.workbooks

This method runs a macro from VB:
Application.Run("MYCUSTOM.XLM!MyMacro")

No command exists as follows:
?Application.Delete("MYCUSTOM.XLM!MyMacro")?


0
 
PaullkhaAuthor Commented:
Can I save a workbook w/o saving the embedded macros as well?

The reason is that the workbook I create will be emailed to others. I would prefer if a) they do not see my macro code, b) are not presented with that horrible unfriendly (disable, enable macro dialog box) when they go to open the sheet that I sent them
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Richie_SimonettiIT OperationsCommented:
' this code asumes that you have a reference to a workbook called wbk

wbk.VBProject.VBComponents.Remove wbk.VBProject.VBComponents.item("Macros")' change accordingly
0
 
bruintjeCommented:
Ok this would work

Private Sub Command1_Click()
Dim objExcel As Excel.Application
  Set objExcel = New Excel.Application
  objExcel.Visible = True
  objExcel.Workbooks.Open "c:\temp\Test.xls"
  With objExcel.Workbooks("test.xls").VBProject
    .VBComponents.Remove .VBComponents("Module1")
  End With
End Sub

but it doesn't i get an not trusted error, so that leaves only one option as i can think of in 10 seconds :)

-create you macro containing workbook
-run the format macro
-open a seocnd clean workbook in code
-copy the result sheet to the new clean workbook
-now you can send this workbook to the customers
-and you can even delete the send workbook
-and save the macro workbook for the next run

HTH:O)Bruintje
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
bruintjeCommented:
hmmm missed that one Richie
0
 
rspahitzCommented:
When you create the macro, you have the choice of where to store it.  (See Tools|Macro|Record New Macro: "Record Macro" window.)

Your choices are:

* Personal Macro Workbook
* This workbook
* New workbook

When you record, choose the appropriate location, and understand that if you deliver the workbook without the macros, nobody else will be able to run them!

To modify the location of existing macros, edit the macro (open the VBA window) and locate the module block (located under the Project|Modules folder for the current workbook) then drag the module to a different workbook.  (Alternately, export it, delete it, then import it into the other workbook.)
0
 
Richie_SimonettiIT OperationsCommented:
No problem, bruintje!
just for clarification, in my little example you need to change "macros" with the name of module you want to delete.
Cheers
0
 
PaullkhaAuthor Commented:
PS. Thank Richie for your input as well.
0
 
bruintjeCommented:
thanks for the grade
0
 
Richie_SimonettiIT OperationsCommented:
:0, what happens here?
0
 
bruintjeCommented:
Richie what do you mean? i have to read carefully because i don't see your comment pointing to something yet
0
 
Richie_SimonettiIT OperationsCommented:
From: bruintje  Date: 05/13/2002 12:17PM PST  
hmmm missed that one Richie  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.