Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming |