Removing VBA Modules in Excel doesn't succeed.

Posted on 2006-04-24
Last Modified: 2012-06-27
I'm trying to create a subroutine in the Excel 2002 VB Editor to automatically remove the module once all the script has finished running (trying to automate a daily process using Excel Macros that run when the worksheet is opened). My goal is to save the resulting sheet as a new sheet, and not have the macros follow it, thus bringing up the "Enable Macros/Disable Macros" warning that's featured in Office XP.

My Code is this:

Private Sub Auto_Open()
    ' MsgBox "Running Workbook_Open . . ."
    Dim sTest As String
    sTest = Range("A2").Value
    If sTest = "" Then
    End If
End Sub
Private Sub dataExtract()
    ' MsgBox "Running dataExtract . . ."
    ' Create a connection object.
    Dim cnPubs As ADODB.Connection
    Set cnPubs = New ADODB.Connection

    ' Provide the connection string.
    Dim strConn As String

    'Use the SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"

    'Connect to the Pubs database on the local server.
    strConn = strConn & "DATA SOURCE=[Server];INITIAL CATALOG=[pubs];"

    'Use an integrated login.
    strConn = strConn & " INTEGRATED SECURITY=sspi;"

    'Now open the connection.
    cnPubs.Open strConn

    ' Create a recordset object.
    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset
    Dim dtYesterday As String
    Dim dtToday As String

    dtToday = DatePart("w", Date)
    If dtToday = "2" Then
        dtYesterday = Date - 3
        dtYesterday = Date - 1
    End If
    ' MsgBox dtYesterday
    With rsPubs
        ' Assign the Connection object.
        .ActiveConnection = cnPubs
        ' Extract the required records.
        .Open "Select e.programno, e.programdate, c.LName, c.FName, c.PaxId from EnrollmentsHist e, Customer c where (e.XActDate >= '" & dtYesterday & "') AND (c.PaxID = e.PaxID) AND (e.LastXAct = 'C') AND (e.ClientID in ('eh', 'ehd', 'rs'))"
        ' Copy the records into cell A1 on Sheet1.
        Sheet1.Range("A2").CopyFromRecordset rsPubs

        ' Tidy up
    End With

    Set rsPubs = Nothing
    Set cnPubs = Nothing


End Sub

Private Sub sortData()

    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers, _
End Sub
Sub DeleteThisModule()

    MsgBox "Hi, I will delete myself "
    ActiveWorkbook.VBProject.VBComponents.Remove ActiveWorkbook.VBProject.VBComponents("Module2")

End Sub

I don't get any errors, and once the marcos are done running the code window in the VB Editor closes, but the module isn't actually removed. I've also tried these sets of code in Sub DeleteThisModule(), but had the same lack of results:

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
Set VBComps = ActiveWorkbook.VBProject.VBComponents
For Each VBComp In VBComps
   Select Case VBComp.Type
      Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule
        MsgBox VBComps
         VBComps.Remove VBComp
      Case Else
         With VBComp.CodeModule
            .DeleteLines 1, .CountOfLines
         End With
   End Select
Next VBComp


 Dim vbCom As Object
    Set vbCom = Application.VBE.ActiveVBProject.VBComponents
    vbCom.Remove VBComponent:= _

I have the "Tools > Macro > Security..." "Trust access to Visual Basic Project" checked already.
I have MS VB for Applications Extensibility 5.3, MS ActiveX Data Objects Recordset 2.8 Library, MS ActiveX Data Objects 2.8 Library, XLODBC.XLA, MS Office 10.0 Object Library, OLD Automation, MS Excel 10.0 Object Library, and Visual Basic for Applications all loaded from Tools > References in MS VB Editor.

500 Points because a quick solution would be best, also this seems difficult enough to merit them.
Question by:Vriaeliss
    LVL 12

    Accepted Solution

    Hello, Vriaeliss,

    I tried copying just the "delete module" section of your code (i.e. the last three lines) into a Macro.  It worked for me in XL2000.

    Perhaps there is a difference with XL2002, or perhaps something else in your extended macro is interfering.  You might try commenting-out sections of code to test this second hypothesis.


    Author Comment

    Hmm... That's true. It does work in a new sheet (the delete module sub alone).

    I ended up developing a work-around that made a new sheet to store the data, and I think that works better than this idea (delete the module and save the current workbook as a new file).

    I wonder if it has anything to do with the delete module sub being at the end of a chain that runs automatically upon opening...

    Since I've worked around the problem, I'd like to close the question, so if you've worked on this, post what you have and I'll divvy points best I can :)

     ~ Beau

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    Visual xHarbour 1 47
    pairs challenge 5 28
    python question 5 33
    autoit - check if option is checked in another program 2 24
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    This is an explanation of a simple data model to help parse a JSON feed
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    779 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

    9 Experts available now in Live!

    Get 1:1 Help Now