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

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

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

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" wrote in message
...
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

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

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

--
Trev B


"Bob Phillips" wrote:

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" wrote in message
...
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


.

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

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]




On Thu, 4 Feb 2010 06:38:05 -0800, Trev B
wrote:

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

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

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

HTH

Bob


"Chip Pearson" wrote in message
...
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]




On Thu, 4 Feb 2010 06:38:05 -0800, Trev B
wrote:

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