modules removing problem
 

Hello! I want to remove about 30 modules in secend opened Workbook from
first opend Workbook -the instance excel.
This first workbook it is scheduler, wich generated periodically report from
model. This model contains about 30 modules, witch i have to remove before
report save.

In first workbook I running:

Dim objVbc As VBComponent
Set wb2 = workbooks (xxxxx)

For Each objVbc In Workbooks(wb2).VBProject.VBComponents
If objVbc.Type 0 And objVbc.Type < 4 Then
Workbooks(ActiveWorkbook.Name).VBProject.VBCompone nts.Remove
objVbc
Else
objVbc.CodeModule.DeleteLines StartLine:=1,
Count:=objVbc.CodeModule.CountOfLines
End If
Next objVbc

Unfortunately, this code does not work, but when I break the VBA code (after
this above code part ) all modules disappear and is OK!
Is it problem with refresh? What should I do? Help me , please! Thanx

modules removing problem
 

The first flag I see raised is

Workbooks(ActiveWorkbook.Name).

Are you really sure you want to remove modules from the
ActiveWorkbook? A better solution is to set a reference to the
workbook whose modules you want to remove and always use that
reference. E.g.,

Dim DeleteFromWB As Workbook
Dim VBComp As VBIDE.VBComponent

Set DeleteFromWB = Workbooks("Book3.xls")
For Each VBComp In DeleteFromWB.VBProject.VBComponents
Select Case VBComp.Type
Case vbext_ct_ActiveXDesigner, vbext_ct_ClassModule, _
vbext_ct_MSForm, vbext_ct_StdModule
DeleteFromWB.VBProject.VBComponents.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

In this code, DeleteFromWB is the workbook containing the modules you
want to delete. It doesn't matter what workbook is active and what
workbook contains the code. The code will always be deleted from
DeleteFromWB.

See www.cpearson.com/Excel/VBE.aspx for lots more info about working
with the VBA 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 Thu, 20 Aug 2009 19:32:49 +0200, "Adax" wrote:

Hello! I want to remove about 30 modules in secend opened Workbook from
first opend Workbook -the instance excel.
This first workbook it is scheduler, wich generated periodically report from
model. This model contains about 30 modules, witch i have to remove before
report save.

In first workbook I running:

Dim objVbc As VBComponent
Set wb2 = workbooks (xxxxx)

For Each objVbc In Workbooks(wb2).VBProject.VBComponents
If objVbc.Type 0 And objVbc.Type < 4 Then
Workbooks(ActiveWorkbook.Name).VBProject.VBCompone nts.Remove
objVbc
Else
objVbc.CodeModule.DeleteLines StartLine:=1,
Count:=objVbc.CodeModule.CountOfLines
End If
Next objVbc

Unfortunately, this code does not work, but when I break the VBA code (after
this above code part ) all modules disappear and is OK!
Is it problem with refresh? What should I do? Help me , please! Thanx

modules removing problem
 

Uzytkownik "Chip Pearson" napisal w wiadomosci
...
The first flag I see raised is
Workbooks(ActiveWorkbook.Name).
Are you really sure you want to remove modules from the
ActiveWorkbook? A better solution is to set a reference to the
workbook whose modules you want to remove and always use that
reference. E.g.,
Dim DeleteFromWB As Workbook
Dim VBComp As VBIDE.VBComponent
Set DeleteFromWB = Workbooks("Book3.xls")
For Each VBComp In DeleteFromWB.VBProject.VBComponents
Select Case VBComp.Type
Case vbext_ct_ActiveXDesigner, vbext_ct_ClassModule, _
vbext_ct_MSForm, vbext_ct_StdModule
DeleteFromWB.VBProject.VBComponents.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

Thank You very much! but unfortunatelyit dosn't work tooo! :) This problem
I try solve since several months and without result.
Your and my code works when I start this code from empty workbook.
When I add it to my scheduler with many modules I noticed importand
observation: when I breake code after this removing code, immediately all
modules disappears.... Problem with collection refresh? I see, missing
collection refresh...