Solved

Excel VBA Macro

Posted on 2002-05-13
13
615 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now