• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 658
  • Last Modified:

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?
0
Paullkha
Asked:
Paullkha
  • 5
  • 4
  • 3
  • +1
1 Solution
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
 
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now