Vriaeliss
asked on
Removing VBA Modules in Excel doesn't succeed.
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
dataExtract
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
Else
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").CopyFro mRecordset rsPubs
' Tidy up
.Close
End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
sortData
End Sub
Private Sub sortData()
Columns("A:E").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom , DataOption1:=xlSortTextAsN umbers, _
DataOption2:=xlSortNormal
DeleteThisModule
End Sub
Sub DeleteThisModule()
MsgBox "Hi, I will delete myself "
ActiveWorkbook.VBProject.V BComponent s.Remove ActiveWorkbook.VBProject.V BComponent s("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:
(From http://www.mrexcel.com/archive2/36300/41840.htm)
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
Set VBComps = ActiveWorkbook.VBProject.V BComponent s
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
and...
(From http://www.ozgrid.com/VBA/delete-module.htm)
Dim vbCom As Object
Set vbCom = Application.VBE.ActiveVBPr oject.VBCo mponents
vbCom.Remove VBComponent:= _
vbCom.Item("Module1")
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.
My Code is this:
Private Sub Auto_Open()
' MsgBox "Running Workbook_Open . . ."
Dim sTest As String
sTest = Range("A2").Value
If sTest = "" Then
dataExtract
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
Else
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").CopyFro
' Tidy up
.Close
End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
sortData
End Sub
Private Sub sortData()
Columns("A:E").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
DataOption2:=xlSortNormal
DeleteThisModule
End Sub
Sub DeleteThisModule()
MsgBox "Hi, I will delete myself "
ActiveWorkbook.VBProject.V
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:
(From http://www.mrexcel.com/archive2/36300/41840.htm)
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
Set VBComps = ActiveWorkbook.VBProject.V
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
and...
(From http://www.ozgrid.com/VBA/delete-module.htm)
Dim vbCom As Object
Set vbCom = Application.VBE.ActiveVBPr
vbCom.Remove VBComponent:= _
vbCom.Item("Module1")
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :)
Thanks,
~ Beau