[Webinar] Streamline your web hosting managementRegister Today

x
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

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