Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Sheets in VBA Project
I've just completed a project that kind of developed "scope creep" (everybody wanted me to add something), and even before I've linked it to other workbooks, there will be four others, it's already kind of big. I've been trying to clean up the code, and I see that in the project window of the VB window it still lists worksheets that are no longer there. The interesting thing is that the workbook originally contained 75 worksheets, that I've condensed down to 8, but the project window shows those 8 plus 5 that have been deleted and two chart sheets. It never had charts to begin with. I've tried to delete these, there's no code in them, but I don't have that option in the file menu. Are these adding to the file size? If so, how do I get rid of them? Any help is appreciated. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200507/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Sheets in VBA Project
You could give Rob Bovey's CodeCleaner add-in a shot.
http://www.appspro.com/Utilities/CodeCleaner.htm -- HTH RP (remove nothere from the email address if mailing direct) "Frank Rudd via OfficeKB.com" wrote in message ... I've just completed a project that kind of developed "scope creep" (everybody wanted me to add something), and even before I've linked it to other workbooks, there will be four others, it's already kind of big. I've been trying to clean up the code, and I see that in the project window of the VB window it still lists worksheets that are no longer there. The interesting thing is that the workbook originally contained 75 worksheets, that I've condensed down to 8, but the project window shows those 8 plus 5 that have been deleted and two chart sheets. It never had charts to begin with. I've tried to delete these, there's no code in them, but I don't have that option in the file menu. Are these adding to the file size? If so, how do I get rid of them? Any help is appreciated. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200507/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Sheets in VBA Project
That helped a little, and every little bit helps. It only dropped it some though. Any other thoughts? Bob Phillips wrote: You could give Rob Bovey's CodeCleaner add-in a shot. http://www.appspro.com/Utilities/CodeCleaner.htm I've just completed a project that kind of developed "scope creep" (everybody wanted me to add something), and even before I've linked it to other [quoted text clipped - 9 lines] Any help is appreciated. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200507/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Sheets in VBA Project
Hi,
there might be hidden sheets in the workbook. try the below, (if the workbook was corrupt, it might crash excel) Sub Test() Dim vbc As Object Debug.Print "***** " & ActiveWorkbook.Name & " *****" For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Type = 100 Then If Not vbc.Properties("Parent").Object Is Application Then Debug.Print vbc.Name, vbc.Properties("Name"), _ IIf(vbc.Properties("Visible") = -1, "Visible", "Hidden") End If End If Next End Sub -- HTH, okaizawa Frank Rudd via OfficeKB.com wrote: I've just completed a project that kind of developed "scope creep" (everybody wanted me to add something), and even before I've linked it to other workbooks, there will be four others, it's already kind of big. I've been trying to clean up the code, and I see that in the project window of the VB window it still lists worksheets that are no longer there. The interesting thing is that the workbook originally contained 75 worksheets, that I've condensed down to 8, but the project window shows those 8 plus 5 that have been deleted and two chart sheets. It never had charts to begin w ith.I've tried to delete these, there's no code in them, but I don't have that option in the file menu. Are these adding to the file size? If so, how do I get rid of them? Any help is appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Sheets in VBA Project
I did this...what was supposed to happen? okaizawa wrote: Hi, there might be hidden sheets in the workbook. try the below, (if the workbook was corrupt, it might crash excel) Sub Test() Dim vbc As Object Debug.Print "***** " & ActiveWorkbook.Name & " *****" For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Type = 100 Then If Not vbc.Properties("Parent").Object Is Application Then Debug.Print vbc.Name, vbc.Properties("Name"), _ IIf(vbc.Properties("Visible") = -1, "Visible", "Hidden") End If End If Next End Sub I've just completed a project that kind of developed "scope creep" (everybody wanted me to add something), and even before I've linked it to other [quoted text clipped - 9 lines] Any help is appreciated. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200507/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Sheets in VBA Project
The worksheets were hidden. The code didn't find them, but for anybody else who has this problem, I opened the properties window and while one of the sheets that wasn't supposed to be there was selected, and saw that it was hidden. Deleting these made a significant difference, as one might expect. Frank Rudd wrote: I did this...what was supposed to happen? Hi, there might be hidden sheets in the workbook. [quoted text clipped - 19 lines] Any help is appreciated. -- Message posted via http://www.officekb.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Sheets in VBA Project
Hi,
that macro outputs result to 'immidiate' window in the visual basic editor. for example, ***** Book1.xls ***** Sheet1 Sheet1 Visible Sheet2 Sheet2 Visible Sheet3 Sheet3 Visible the 1st column is sheet name in the vb project, the 2nd column is name in the workbook. in this list, are there the sheets that you are seeing in 'project' window? are they all visible? if there are not all sheets, try the following macro. this lists all vbcomponents in the active workbook. a number in the 2nd column is component type.(1:standard module, 2: class module, 3: userform) Sub Test2() Dim vbc As Object Debug.Print "***** " & ActiveWorkbook.Name & " *****" For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Type = 100 Then If Not vbc.Properties("Parent").Object Is Application Then Debug.Print vbc.Name, vbc.Properties("Name"), _ IIf(vbc.Properties("Visible") = -1, "Visible", "Hidden") End If Else Debug.Print vbc.Name, vbc.Type End If Next End Sub if they are unusual hidden sheets, the project might be corrupt. -- HTH, okaizawa Frank Rudd via OfficeKB.com wrote: The worksheets were hidden. The code didn't find them, but for anybody else who has this problem, I opened the properties window and while one of the sheets that wasn't supposed to be there was selected, and saw that it was hidden. Deleting these made a significant difference, as one might expect. Frank Rudd wrote: I did this...what was supposed to happen? Hi, there might be hidden sheets in the workbook. [quoted text clipped - 19 lines] Any help is appreciated. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Sheets in VBA Project
Okay, thanks. okaizawa wrote: Hi, that macro outputs result to 'immidiate' window in the visual basic editor. for example, ***** Book1.xls ***** Sheet1 Sheet1 Visible Sheet2 Sheet2 Visible Sheet3 Sheet3 Visible the 1st column is sheet name in the vb project, the 2nd column is name in the workbook. in this list, are there the sheets that you are seeing in 'project' window? are they all visible? if there are not all sheets, try the following macro. this lists all vbcomponents in the active workbook. a number in the 2nd column is component type.(1:standard module, 2: class module, 3: userform) Sub Test2() Dim vbc As Object Debug.Print "***** " & ActiveWorkbook.Name & " *****" For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Type = 100 Then If Not vbc.Properties("Parent").Object Is Application Then Debug.Print vbc.Name, vbc.Properties("Name"), _ IIf(vbc.Properties("Visible") = -1, "Visible", "Hidden") End If Else Debug.Print vbc.Name, vbc.Type End If Next End Sub if they are unusual hidden sheets, the project might be corrupt. The worksheets were hidden. The code didn't find them, but for anybody else who has this problem, I opened the properties window and while one of the [quoted text clipped - 9 lines] Any help is appreciated. -- Message posted via http://www.officekb.com |
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 | |||
Delete Macro in VBA Project Office 2003 | Excel Programming | |||
Programmatically delete macro using a protected project | Excel Programming |