Delete a sheet and delete a module in Another Worksheet then save

[Trev B]

Hi,

Thanks in advance.

My problem is that i need VBA code to open 6 xls files (In turn) and to each
of them:

1) Delete a sheet called "Prog"

2) Delete a module called "modActions"

3) Save the file

Thanks very much for your attention.
Regards

Trev B

 

[Bob Phillips]

Public Sub Test()
HandleIT "C:\Test\Workbook1.xls"
HandleIT "C:\Test\Workbook2.xls"
'etc

End Sub

Private Function HandleIT(wb As String)
Dim VBProj As Object
VBComp As VBIDE.VBComponent

Application.DisplayAlerts = False

Workbooks(wb).Open

With ActiveWorkbook

.Worksheets("Prog").Delete

VBProj = .VBProject
VBProj.VBComponents.Remove VBProj.VBComponents("modActions")
End With

ActiveWorkbook.Save
ActiveWorkbook.Close

Application.DisplayAlerts = True

End Function


HTH

Bob

 

[Trev B]

Thanks Bob,

However, when I try and run it an error occurs on following line:-

VBComp As VBIDE.VBComponent

The error states:-

Statement Invalid Outside Type Block.

Can you please help me get over this error.

Thanks

 

[Chip Pearson]

You are missing the word "Dim" at the front of the line.

VBComp As VBIDE.VBComponent

should be

Dim VBComp As VBIDE.VBComponent

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Bob Phillips]

Actually, that line is not needed at all, you can remove it.

HTH

Bob

You are missing the word "Dim" at the front of the line.

VBComp As VBIDE.VBComponent

should be

Dim VBComp As VBIDE.VBComponent

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Thanks Bob,

However, when I try and run it an error occurs on following line:-

VBComp As VBIDE.VBComponent

The error states:-

Statement Invalid Outside Type Block.

Can you please help me get over this error.

Thanks