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

  • Thread starter Thread starter Trev B
  • Start date Start date
T

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
 
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
 
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
 
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]
 
Actually, that line is not needed at all, you can remove it.

HTH

Bob


Chip Pearson said:
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
Click to expand...
Click to expand...
 
Back
Top