Hi,

I need to delete a module programmatically. Here is my function ( It is on
ms access),

Public Function lathe_V11(tcid As Double, tlid As Double, custid As String,
mach As Integer, prog As Integer)
'Dim string Variables
Dim thepath As String

'Dim Excel Object
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
thepath = mypath & mach & "\" & custid & "\" & mach & prog & ".xls"



With objXL.Application
.Visible = True
.Workbooks.Open thepath
.ActiveWindow.WindowState = xlMinimized
.VBComponents("Module1").Remove
'........
'....More code................
End With
End Function


Could anyone help me?

Thanks

Bre-x

The following will remove Module1.


With ThisWorkbook.VBProject.VBComponents
.Remove .Item("Module1")
End With


See www.cpearson.com/Excel/VBE.aspx for much more information and
example code about programmatically working with the VBA editor and
it's objects.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 28 Jan 2009 09:01:59 -0700, "Bre-x" wrote:

Hi,

I need to delete a module programmatically. Here is my function ( It is on
ms access),

Public Function lathe_V11(tcid As Double, tlid As Double, custid As String,
mach As Integer, prog As Integer)
'Dim string Variables
Dim thepath As String

'Dim Excel Object
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
thepath = mypath & mach & "\" & custid & "\" & mach & prog & ".xls"



With objXL.Application
.Visible = True
.Workbooks.Open thepath
.ActiveWindow.WindowState = xlMinimized
.VBComponents("Module1").Remove
'........
'....More code................
End With
End Function


Could anyone help me?

Thanks

Bre-x

Thank you for you answer.

It doesnt work. Sorry, I dont understand your instructions.


Public Function lathe_V11(tcid As Double, tlid As Double, custid As String,
mach As Integer, prog As Integer)
'Dim string Variables
Dim thepath As String

'Dim Excel Object
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
thepath = mypath & mach & "\" & custid & "\" & mach & prog & ".xls"


With objXL.Application
.Visible = True
.Workbooks.Open thepath
.ActiveWindow.WindowState = xlMinimized
With ThisWorkbook.VBProject.VBComponents
.Remove .Item("Module1")
End With

End With
End Function

add -
Dim objWB as Object

change -
.Workbooks.Open thepath
to
set objWB = .Workbooks.Open(thepath)

and change -
With ThisWorkbook.VBProject.VBComponents
to
With objWB .VBProject.VBComponents

Regards,
Peter T



"Bre-x" wrote in message
...
Thank you for you answer.

It doesnt work. Sorry, I dont understand your instructions.


Public Function lathe_V11(tcid As Double, tlid As Double, custid As
String, mach As Integer, prog As Integer)
'Dim string Variables
Dim thepath As String

'Dim Excel Object
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
thepath = mypath & mach & "\" & custid & "\" & mach & prog & ".xls"


With objXL.Application
.Visible = True
.Workbooks.Open thepath
.ActiveWindow.WindowState = xlMinimized
With ThisWorkbook.VBProject.VBComponents
.Remove .Item("Module1")
End With

End With
End Function

Thank you. It works very well!!!


"Peter T" <peter_t@discussions wrote in message
...
add -
Dim objWB as Object

change -
.Workbooks.Open thepath
to
set objWB = .Workbooks.Open(thepath)

and change -
With ThisWorkbook.VBProject.VBComponents
to
With objWB .VBProject.VBComponents

Regards,
Peter T



"Bre-x" wrote in message
...
Thank you for you answer.

It doesnt work. Sorry, I dont understand your instructions.


Public Function lathe_V11(tcid As Double, tlid As Double, custid As
String, mach As Integer, prog As Integer)
'Dim string Variables
Dim thepath As String

'Dim Excel Object
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
thepath = mypath & mach & "\" & custid & "\" & mach & prog & ".xls"


With objXL.Application
.Visible = True
.Workbooks.Open thepath
.ActiveWindow.WindowState = xlMinimized
With ThisWorkbook.VBProject.VBComponents
.Remove .Item("Module1")
End With

End With
End Function