Solved

Excel VBA Macro

Posted on 2002-05-13
13
630 Views
Last Modified: 2010-05-02
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
Comment
Question by:Paullkha
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 7006831
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
 
LVL 2

Author Comment

by:Paullkha
ID: 7006847
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
 
LVL 2

Author Comment

by:Paullkha
ID: 7006850
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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7006905
' 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
 
LVL 44

Accepted Solution

by:
bruintje earned 50 total points
ID: 7006913
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
 
LVL 44

Expert Comment

by:bruintje
ID: 7006921
hmmm missed that one Richie
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 7006929
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7007003
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
 
LVL 2

Author Comment

by:Paullkha
ID: 7011131
PS. Thank Richie for your input as well.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7011175
thanks for the grade
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7011208
:0, what happens here?
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7011256
Richie what do you mean? i have to read carefully because i don't see your comment pointing to something yet
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7011396
From: bruintje  Date: 05/13/2002 12:17PM PST  
hmmm missed that one Richie  
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question