Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding place for 2.56MB?
Can't determine why Excel 2003 file size is growing.
Stripped my spreadsheet. Erased all VBA macros. Dumped all temp files. Last Cell & UsedRange don't appear to be issues. Not tracking changes. No pivot tables. And still I wind up with 2.56MB sitting somewhere. I originally posted recently under subj of "Excel File Shrink". I started with a 3 MB file that with very little additonal data entry grew to 60MB. Hopefully somebody out there has an answer before I have to pony up and pay MS $245 for Pro Level tech support. Thanks, John |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding place for 2.56MB?
Do you have any objects on any of the worksheets?
Edit|Goto|special|objects may help you find them. wrote: Can't determine why Excel 2003 file size is growing. Stripped my spreadsheet. Erased all VBA macros. Dumped all temp files. Last Cell & UsedRange don't appear to be issues. Not tracking changes. No pivot tables. And still I wind up with 2.56MB sitting somewhere. I originally posted recently under subj of "Excel File Shrink". I started with a 3 MB file that with very little additonal data entry grew to 60MB. Hopefully somebody out there has an answer before I have to pony up and pay MS $245 for Pro Level tech support. Thanks, John -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding place for 2.56MB?
Dave, thanks for hanging with me. Pardon my ignorance, but is a box formed
using the Autoshape tool called an "object"? If so, yes I do have have about 10 objects on my sheet. Still two mysteries to me: 1) sometimes I can run a VBA macro and the file size stays absolutely the same. Then I run it again, and the size increases. I know I must be doing something different each time, but I haven't narrowed it down yet. 2) Any clue on why a completed stripped/deleted sheet including macros could still show a file size of 2.56MB? Thanks, John "Dave Peterson" wrote: Do you have any objects on any of the worksheets? Edit|Goto|special|objects may help you find them. wrote: Can't determine why Excel 2003 file size is growing. Stripped my spreadsheet. Erased all VBA macros. Dumped all temp files. Last Cell & UsedRange don't appear to be issues. Not tracking changes. No pivot tables. And still I wind up with 2.56MB sitting somewhere. I originally posted recently under subj of "Excel File Shrink". I started with a 3 MB file that with very little additonal data entry grew to 60MB. Hopefully somebody out there has an answer before I have to pony up and pay MS $245 for Pro Level tech support. Thanks, John -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding place for 2.56MB?
Yep, those shapes would be objects.
Sometimes people add pictures to the worksheets and those pictures can be pretty large. Open your troublesome workbook. Hit alt-F11 to get to the VBE hit ctrl-g to see the immediate window Type this and hit enter: ?activesheet.shapes.count Swap back to excel and change sheets and do it again. Maybe there are shapes you can't see??? John Childs wrote: Dave, thanks for hanging with me. Pardon my ignorance, but is a box formed using the Autoshape tool called an "object"? If so, yes I do have have about 10 objects on my sheet. Still two mysteries to me: 1) sometimes I can run a VBA macro and the file size stays absolutely the same. Then I run it again, and the size increases. I know I must be doing something different each time, but I haven't narrowed it down yet. 2) Any clue on why a completed stripped/deleted sheet including macros could still show a file size of 2.56MB? Thanks, John "Dave Peterson" wrote: Do you have any objects on any of the worksheets? Edit|Goto|special|objects may help you find them. wrote: Can't determine why Excel 2003 file size is growing. Stripped my spreadsheet. Erased all VBA macros. Dumped all temp files. Last Cell & UsedRange don't appear to be issues. Not tracking changes. No pivot tables. And still I wind up with 2.56MB sitting somewhere. I originally posted recently under subj of "Excel File Shrink". I started with a 3 MB file that with very little additonal data entry grew to 60MB. Hopefully somebody out there has an answer before I have to pony up and pay MS $245 for Pro Level tech support. Thanks, John -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding place for 2.56MB?
DAve, amazing. 8 out of 9 sheets show the exact same number of objects or
shapes.....22,913!! I have no idea how they got there. Can you now tell me how to view them and then delete them? Thanks, John "Dave Peterson" wrote: Yep, those shapes would be objects. Sometimes people add pictures to the worksheets and those pictures can be pretty large. Open your troublesome workbook. Hit alt-F11 to get to the VBE hit ctrl-g to see the immediate window Type this and hit enter: ?activesheet.shapes.count Swap back to excel and change sheets and do it again. Maybe there are shapes you can't see??? John Childs wrote: Dave, thanks for hanging with me. Pardon my ignorance, but is a box formed using the Autoshape tool called an "object"? If so, yes I do have have about 10 objects on my sheet. Still two mysteries to me: 1) sometimes I can run a VBA macro and the file size stays absolutely the same. Then I run it again, and the size increases. I know I must be doing something different each time, but I haven't narrowed it down yet. 2) Any clue on why a completed stripped/deleted sheet including macros could still show a file size of 2.56MB? Thanks, John "Dave Peterson" wrote: Do you have any objects on any of the worksheets? Edit|Goto|special|objects may help you find them. wrote: Can't determine why Excel 2003 file size is growing. Stripped my spreadsheet. Erased all VBA macros. Dumped all temp files. Last Cell & UsedRange don't appear to be issues. Not tracking changes. No pivot tables. And still I wind up with 2.56MB sitting somewhere. I originally posted recently under subj of "Excel File Shrink". I started with a 3 MB file that with very little additonal data entry grew to 60MB. Hopefully somebody out there has an answer before I have to pony up and pay MS $245 for Pro Level tech support. Thanks, John -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding place for 2.56MB?
DAve,
When I try to execute the macro below (I left off Option Explicit()): Sub testme() Dim wks As Worksheet For Each wks In Worksheets wks.Shapes.Delete Next wks End Sub When the systems tries to execute the "wks.Shapes.Delete" line, I get an "Object doesn't support this property or method.". I also tried another variation below that didn't work: Sub testme() Dim wks As Worksheet wks = Sheets("Home") ActiveSheet.Shapes.Delete End Sub and Sub testme() Dim wks As Worksheet wks = Sheets("Home") wks.Shapes.Delete End Sub I'm just guessing. I have about 2000 lines of VBA code that is spread over 11 macros, but my coding is very primitive and brute force. Please let me know where I'm missing it. Thanks, John "Dave Peterson" wrote: You want to delete them all? You could use a macro: Option Explicit() sub testme() dim wks as worksheet for each wks in worksheets wks.shapes.delete next wks end sub Hopefully that won't break when there are so many shapes in each sheet. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm wrote: DAve, amazing. 8 out of 9 sheets show the exact same number of objects or shapes.....22,913!! I have no idea how they got there. Can you now tell me how to view them and then delete them? Thanks, John "Dave Peterson" wrote: Yep, those shapes would be objects. Sometimes people add pictures to the worksheets and those pictures can be pretty large. Open your troublesome workbook. Hit alt-F11 to get to the VBE hit ctrl-g to see the immediate window Type this and hit enter: ?activesheet.shapes.count Swap back to excel and change sheets and do it again. Maybe there are shapes you can't see??? John Childs wrote: Dave, thanks for hanging with me. Pardon my ignorance, but is a box formed using the Autoshape tool called an "object"? If so, yes I do have have about 10 objects on my sheet. Still two mysteries to me: 1) sometimes I can run a VBA macro and the file size stays absolutely the same. Then I run it again, and the size increases. I know I must be doing something different each time, but I haven't narrowed it down yet. 2) Any clue on why a completed stripped/deleted sheet including macros could still show a file size of 2.56MB? Thanks, John "Dave Peterson" wrote: Do you have any objects on any of the worksheets? Edit|Goto|special|objects may help you find them. wrote: Can't determine why Excel 2003 file size is growing. Stripped my spreadsheet. Erased all VBA macros. Dumped all temp files. Last Cell & UsedRange don't appear to be issues. Not tracking changes. No pivot tables. And still I wind up with 2.56MB sitting somewhere. I originally posted recently under subj of "Excel File Shrink". I started with a 3 MB file that with very little additonal data entry grew to 60MB. Hopefully somebody out there has an answer before I have to pony up and pay MS $245 for Pro Level tech support. Thanks, John -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding place for 2.56MB?
Lack of testing and a poor memory on my part (as well as bad typing):
Sorry on all 3 parts. Try this one instead: Option Explicit Sub testme() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets wks.Select wks.Shapes.SelectAll On Error Resume Next Selection.Delete If Err.Number < 0 Then Err.Clear For Each shp In wks.Shapes shp.Delete Next shp End If On Error GoTo 0 Next wks End Sub And sorry about (all!) the errors in the previous suggestion. wrote: DAve, When I try to execute the macro below (I left off Option Explicit()): Sub testme() Dim wks As Worksheet For Each wks In Worksheets wks.Shapes.Delete Next wks End Sub When the systems tries to execute the "wks.Shapes.Delete" line, I get an "Object doesn't support this property or method.". I also tried another variation below that didn't work: Sub testme() Dim wks As Worksheet wks = Sheets("Home") ActiveSheet.Shapes.Delete End Sub and Sub testme() Dim wks As Worksheet wks = Sheets("Home") wks.Shapes.Delete End Sub I'm just guessing. I have about 2000 lines of VBA code that is spread over 11 macros, but my coding is very primitive and brute force. Please let me know where I'm missing it. Thanks, John "Dave Peterson" wrote: You want to delete them all? You could use a macro: Option Explicit() sub testme() dim wks as worksheet for each wks in worksheets wks.shapes.delete next wks end sub Hopefully that won't break when there are so many shapes in each sheet. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm wrote: DAve, amazing. 8 out of 9 sheets show the exact same number of objects or shapes.....22,913!! I have no idea how they got there. Can you now tell me how to view them and then delete them? Thanks, John "Dave Peterson" wrote: Yep, those shapes would be objects. Sometimes people add pictures to the worksheets and those pictures can be pretty large. Open your troublesome workbook. Hit alt-F11 to get to the VBE hit ctrl-g to see the immediate window Type this and hit enter: ?activesheet.shapes.count Swap back to excel and change sheets and do it again. Maybe there are shapes you can't see??? John Childs wrote: Dave, thanks for hanging with me. Pardon my ignorance, but is a box formed using the Autoshape tool called an "object"? If so, yes I do have have about 10 objects on my sheet. Still two mysteries to me: 1) sometimes I can run a VBA macro and the file size stays absolutely the same. Then I run it again, and the size increases. I know I must be doing something different each time, but I haven't narrowed it down yet. 2) Any clue on why a completed stripped/deleted sheet including macros could still show a file size of 2.56MB? Thanks, John "Dave Peterson" wrote: Do you have any objects on any of the worksheets? Edit|Goto|special|objects may help you find them. wrote: Can't determine why Excel 2003 file size is growing. Stripped my spreadsheet. Erased all VBA macros. Dumped all temp files. Last Cell & UsedRange don't appear to be issues. Not tracking changes. No pivot tables. And still I wind up with 2.56MB sitting somewhere. I originally posted recently under subj of "Excel File Shrink". I started with a 3 MB file that with very little additonal data entry grew to 60MB. Hopefully somebody out there has an answer before I have to pony up and pay MS $245 for Pro Level tech support. Thanks, John -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding place for 2.56MB?
Will this macro work?
Sub testme() Sheets("Home").Select Dim wks As Worksheet For Each wks In Worksheets wks.Shapes.SelectAll Selection.Delete Next wks End Sub "Dave Peterson" wrote: You want to delete them all? You could use a macro: Option Explicit() sub testme() dim wks as worksheet for each wks in worksheets wks.shapes.delete next wks end sub Hopefully that won't break when there are so many shapes in each sheet. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm wrote: DAve, amazing. 8 out of 9 sheets show the exact same number of objects or shapes.....22,913!! I have no idea how they got there. Can you now tell me how to view them and then delete them? Thanks, John "Dave Peterson" wrote: Yep, those shapes would be objects. Sometimes people add pictures to the worksheets and those pictures can be pretty large. Open your troublesome workbook. Hit alt-F11 to get to the VBE hit ctrl-g to see the immediate window Type this and hit enter: ?activesheet.shapes.count Swap back to excel and change sheets and do it again. Maybe there are shapes you can't see??? John Childs wrote: Dave, thanks for hanging with me. Pardon my ignorance, but is a box formed using the Autoshape tool called an "object"? If so, yes I do have have about 10 objects on my sheet. Still two mysteries to me: 1) sometimes I can run a VBA macro and the file size stays absolutely the same. Then I run it again, and the size increases. I know I must be doing something different each time, but I haven't narrowed it down yet. 2) Any clue on why a completed stripped/deleted sheet including macros could still show a file size of 2.56MB? Thanks, John "Dave Peterson" wrote: Do you have any objects on any of the worksheets? Edit|Goto|special|objects may help you find them. wrote: Can't determine why Excel 2003 file size is growing. Stripped my spreadsheet. Erased all VBA macros. Dumped all temp files. Last Cell & UsedRange don't appear to be issues. Not tracking changes. No pivot tables. And still I wind up with 2.56MB sitting somewhere. I originally posted recently under subj of "Excel File Shrink". I started with a 3 MB file that with very little additonal data entry grew to 60MB. Hopefully somebody out there has an answer before I have to pony up and pay MS $245 for Pro Level tech support. Thanks, John -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding place for 2.56MB?
Maybe, maybe not.
When there are tons of shapes (and I don't recall how many shapes make tons), the selection.delete line will fail. The one I posted just before you posted this was my way to react to that failure. But I didn't take the time to add tons of shapes to the worksheet--so I don't know for sure! But I do know that the wks.select line isn't really required--so just add that change: Option Explicit Sub testme() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets wks.Shapes.SelectAll On Error Resume Next Selection.Delete If Err.Number < 0 Then Err.Clear For Each shp In wks.Shapes shp.Delete Next shp End If On Error GoTo 0 Next wks End Sub wrote: Will this macro work? Sub testme() Sheets("Home").Select Dim wks As Worksheet For Each wks In Worksheets wks.Shapes.SelectAll Selection.Delete Next wks End Sub "Dave Peterson" wrote: You want to delete them all? You could use a macro: Option Explicit() sub testme() dim wks as worksheet for each wks in worksheets wks.shapes.delete next wks end sub Hopefully that won't break when there are so many shapes in each sheet. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm wrote: DAve, amazing. 8 out of 9 sheets show the exact same number of objects or shapes.....22,913!! I have no idea how they got there. Can you now tell me how to view them and then delete them? Thanks, John "Dave Peterson" wrote: Yep, those shapes would be objects. Sometimes people add pictures to the worksheets and those pictures can be pretty large. Open your troublesome workbook. Hit alt-F11 to get to the VBE hit ctrl-g to see the immediate window Type this and hit enter: ?activesheet.shapes.count Swap back to excel and change sheets and do it again. Maybe there are shapes you can't see??? John Childs wrote: Dave, thanks for hanging with me. Pardon my ignorance, but is a box formed using the Autoshape tool called an "object"? If so, yes I do have have about 10 objects on my sheet. Still two mysteries to me: 1) sometimes I can run a VBA macro and the file size stays absolutely the same. Then I run it again, and the size increases. I know I must be doing something different each time, but I haven't narrowed it down yet. 2) Any clue on why a completed stripped/deleted sheet including macros could still show a file size of 2.56MB? Thanks, John "Dave Peterson" wrote: Do you have any objects on any of the worksheets? Edit|Goto|special|objects may help you find them. wrote: Can't determine why Excel 2003 file size is growing. Stripped my spreadsheet. Erased all VBA macros. Dumped all temp files. Last Cell & UsedRange don't appear to be issues. Not tracking changes. No pivot tables. And still I wind up with 2.56MB sitting somewhere. I originally posted recently under subj of "Excel File Shrink". I started with a 3 MB file that with very little additonal data entry grew to 60MB. Hopefully somebody out there has an answer before I have to pony up and pay MS $245 for Pro Level tech support. Thanks, John -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding place for 2.56MB?
Dave, I tried to send the below directly to your email address. JC
Dave, I took out the wks.Shapes.SelectAll as you suggested. When I ran the macro with this left in, I would get "Out of Memory" msg. On one of my sheets, the # of shapes was reduced to zero. On another sheet, the # of sheets went from 21,000 to 15,000, but couldn't reduce it any further. I'm attaching a file with several "tons" of shapes if you wouldn't mind taking a whack at it and getting a first hand glimpse of what I've been wrestling with. Fyi, even when I delete some shapes, it doesn't seem to reduce the size of the file now sitting at 14.2MB (it started out at less than 3MB). Sub testme() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets 'XXXXX wks.Shapes.SelectAll On Error Resume Next Selection.Delete If Err.Number < 0 Then Err.Clear For Each shp In wks.Shapes shp.Delete Next shp End If On Error GoTo 0 Next wks End Sub "Dave Peterson" wrote: Maybe, maybe not. When there are tons of shapes (and I don't recall how many shapes make tons), the selection.delete line will fail. The one I posted just before you posted this was my way to react to that failure. But I didn't take the time to add tons of shapes to the worksheet--so I don't know for sure! But I do know that the wks.select line isn't really required--so just add that change: Option Explicit Sub testme() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets wks.Shapes.SelectAll On Error Resume Next Selection.Delete If Err.Number < 0 Then Err.Clear For Each shp In wks.Shapes shp.Delete Next shp End If On Error GoTo 0 Next wks End Sub wrote: Will this macro work? Sub testme() Sheets("Home").Select Dim wks As Worksheet For Each wks In Worksheets wks.Shapes.SelectAll Selection.Delete Next wks End Sub "Dave Peterson" wrote: You want to delete them all? You could use a macro: Option Explicit() sub testme() dim wks as worksheet for each wks in worksheets wks.shapes.delete next wks end sub Hopefully that won't break when there are so many shapes in each sheet. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm wrote: DAve, amazing. 8 out of 9 sheets show the exact same number of objects or shapes.....22,913!! I have no idea how they got there. Can you now tell me how to view them and then delete them? Thanks, John "Dave Peterson" wrote: Yep, those shapes would be objects. Sometimes people add pictures to the worksheets and those pictures can be pretty large. Open your troublesome workbook. Hit alt-F11 to get to the VBE hit ctrl-g to see the immediate window Type this and hit enter: ?activesheet.shapes.count Swap back to excel and change sheets and do it again. Maybe there are shapes you can't see??? John Childs wrote: Dave, thanks for hanging with me. Pardon my ignorance, but is a box formed using the Autoshape tool called an "object"? If so, yes I do have have about 10 objects on my sheet. Still two mysteries to me: 1) sometimes I can run a VBA macro and the file size stays absolutely the same. Then I run it again, and the size increases. I know I must be doing something different each time, but I haven't narrowed it down yet. 2) Any clue on why a completed stripped/deleted sheet including macros could still show a file size of 2.56MB? Thanks, John "Dave Peterson" wrote: Do you have any objects on any of the worksheets? Edit|Goto|special|objects may help you find them. wrote: Can't determine why Excel 2003 file size is growing. Stripped my spreadsheet. Erased all VBA macros. Dumped all temp files. Last Cell & UsedRange don't appear to be issues. Not tracking changes. No pivot tables. And still I wind up with 2.56MB sitting somewhere. I originally posted recently under subj of "Excel File Shrink". I started with a 3 MB file that with very little additonal data entry grew to 60MB. Hopefully somebody out there has an answer before I have to pony up and pay MS $245 for Pro Level tech support. Thanks, John -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding place for 2.56MB?
(Also in a private reply)
I don't open other people's files--and it's a small breech of newsgroup etiquette to send files without asking. But when you have lots of shapes, the shapes.selectall can have problems. Maybe just looping through the shapes would be sufficient: Sub testme() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets For Each shp In wks.Shapes shp.Delete Next shp Next wks End Sub Be aware that comments are shapes, cells with data validation are shapes, autofilters are shapes. So you may want to look at Ron de Bruin's site if any of your worksheets have comments, data|validation, or autofilter. If you have any of that stuff on any sheet, then don't use the one above. From Ron de Bruin's site: http://www.rondebruin.nl/controlsobjectsworksheet.htm Sub Shapes4() 'Dave Peterson and Bob Phillips'Example only for the Forms controls Dim shp As Shape Dim testStr As String For Each shp In ActiveSheet.Shapes If shp.Type = 8 Then If shp.FormControlType = 2 Then testStr = "" On Error Resume Next testStr = shp.TopLeftCell.Address On Error GoTo 0 If testStr < "" Then shp.Delete Else shp.Delete End If End If Next shp End Sub John Childs wrote: Dave, I tried to send the below directly to your email address. JC Dave, I took out the wks.Shapes.SelectAll as you suggested. When I ran the macro with this left in, I would get "Out of Memory" msg. On one of my sheets, the # of shapes was reduced to zero. On another sheet, the # of sheets went from 21,000 to 15,000, but couldn't reduce it any further. I'm attaching a file with several "tons" of shapes if you wouldn't mind taking a whack at it and getting a first hand glimpse of what I've been wrestling with. Fyi, even when I delete some shapes, it doesn't seem to reduce the size of the file now sitting at 14.2MB (it started out at less than 3MB). Sub testme() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets 'XXXXX wks.Shapes.SelectAll On Error Resume Next Selection.Delete If Err.Number < 0 Then Err.Clear For Each shp In wks.Shapes shp.Delete Next shp End If On Error GoTo 0 Next wks End Sub "Dave Peterson" wrote: Maybe, maybe not. When there are tons of shapes (and I don't recall how many shapes make tons), the selection.delete line will fail. The one I posted just before you posted this was my way to react to that failure. But I didn't take the time to add tons of shapes to the worksheet--so I don't know for sure! But I do know that the wks.select line isn't really required--so just add that change: Option Explicit Sub testme() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets wks.Shapes.SelectAll On Error Resume Next Selection.Delete If Err.Number < 0 Then Err.Clear For Each shp In wks.Shapes shp.Delete Next shp End If On Error GoTo 0 Next wks End Sub wrote: Will this macro work? Sub testme() Sheets("Home").Select Dim wks As Worksheet For Each wks In Worksheets wks.Shapes.SelectAll Selection.Delete Next wks End Sub "Dave Peterson" wrote: You want to delete them all? You could use a macro: Option Explicit() sub testme() dim wks as worksheet for each wks in worksheets wks.shapes.delete next wks end sub Hopefully that won't break when there are so many shapes in each sheet. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm wrote: DAve, amazing. 8 out of 9 sheets show the exact same number of objects or shapes.....22,913!! I have no idea how they got there. Can you now tell me how to view them and then delete them? Thanks, John "Dave Peterson" wrote: Yep, those shapes would be objects. Sometimes people add pictures to the worksheets and those pictures can be pretty large. Open your troublesome workbook. Hit alt-F11 to get to the VBE hit ctrl-g to see the immediate window Type this and hit enter: ?activesheet.shapes.count Swap back to excel and change sheets and do it again. Maybe there are shapes you can't see??? John Childs wrote: Dave, thanks for hanging with me. Pardon my ignorance, but is a box formed using the Autoshape tool called an "object"? If so, yes I do have have about 10 objects on my sheet. Still two mysteries to me: 1) sometimes I can run a VBA macro and the file size stays absolutely the same. Then I run it again, and the size increases. I know I must be doing something different each time, but I haven't narrowed it down yet. 2) Any clue on why a completed stripped/deleted sheet including macros could still show a file size of 2.56MB? Thanks, John "Dave Peterson" wrote: Do you have any objects on any of the worksheets? Edit|Goto|special|objects may help you find them. wrote: Can't determine why Excel 2003 file size is growing. Stripped my spreadsheet. Erased all VBA macros. Dumped all temp files. Last Cell & UsedRange don't appear to be issues. Not tracking changes. No pivot tables. And still I wind up with 2.56MB sitting somewhere. I originally posted recently under subj of "Excel File Shrink". I started with a 3 MB file that with very little additonal data entry grew to 60MB. Hopefully somebody out there has an answer before I have to pony up and pay MS $245 for Pro Level tech support. Thanks, John -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding place for 2.56MB?
John,
maybe you could save the file inside the For Each wks In Worksheets ... Next wks loop. That way if it does error out then at least you will have cleared some of the shapes from some of the sheets in the saved copy, and if you keep running the macro then eventually you will be able to clear them all. Hope this helps. Pete On Jul 14, 5:40 pm, John Childs wrote: Dave, I tried to send the below directly to your email address. JC Dave, I took out the wks.Shapes.SelectAll as you suggested. When I ran the macro with this left in, I would get "Out of Memory" msg. On one of my sheets, the # of shapes was reduced to zero. On another sheet, the # of sheets went from 21,000 to 15,000, but couldn't reduce it any further. I'm attaching a file with several "tons" of shapes if you wouldn't mind taking a whack at it and getting a first hand glimpse of what I've been wrestling with. Fyi, even when I delete some shapes, it doesn't seem to reduce the size of the file now sitting at 14.2MB (it started out at less than 3MB). Sub testme() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets 'XXXXX wks.Shapes.SelectAll On Error Resume Next Selection.Delete If Err.Number < 0 Then Err.Clear For Each shp In wks.Shapes shp.Delete Next shp End If On Error GoTo 0 Next wks End Sub "Dave Peterson" wrote: Maybe, maybe not. When there are tons of shapes (and I don't recall how many shapes make tons), the selection.delete line will fail. The one I posted just before you posted this was my way to react to that failure. But I didn't take the time to add tons of shapes to the worksheet--so I don't know for sure! But I do know that the wks.select line isn't really required--so just add that change: Option Explicit Sub testme() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets wks.Shapes.SelectAll On Error Resume Next Selection.Delete If Err.Number < 0 Then Err.Clear For Each shp In wks.Shapes shp.Delete Next shp End If On Error GoTo 0 Next wks End Sub wrote: Will this macro work? Sub testme() Sheets("Home").Select Dim wks As Worksheet For Each wks In Worksheets wks.Shapes.SelectAll Selection.Delete Next wks End Sub "Dave Peterson" wrote: You want to delete them all? You could use a macro: Option Explicit() sub testme() dim wks as worksheet for each wks in worksheets wks.shapes.delete next wks end sub Hopefully that won't break when there are so many shapes in each sheet. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm wrote: DAve, amazing. 8 out of 9 sheets show the exact same number of objects or shapes.....22,913!! I have no idea how they got there. Can you now tell me how to view them and then delete them? Thanks, John "Dave Peterson" wrote: Yep, those shapes would be objects. Sometimes people add pictures to the worksheets and those pictures can be pretty large. Open your troublesome workbook. Hit alt-F11 to get to the VBE hit ctrl-g to see the immediate window Type this and hit enter: ?activesheet.shapes.count Swap back to excel and change sheets and do it again. Maybe there are shapes you can't see??? John Childs wrote: Dave, thanks for hanging with me. Pardon my ignorance, but is a box formed using the Autoshape tool called an "object"? If so, yes I do have have about 10 objects on my sheet. Still two mysteries to me: 1) sometimes I can run a VBA macro and the file size stays absolutely the same. Then I run it again, and the size increases. I know I must be doing something different each time, but I haven't narrowed it down yet. 2) Any clue on why a completed stripped/deleted sheet including macros could still show a file size of 2.56MB? Thanks, John "Dave Peterson" wrote: Do you have any objects on any of the worksheets? Edit|Goto|special|objects may help you find them. wrote: Can't determine why Excel 2003 file size is growing. Stripped my spreadsheet. Erased all VBA macros. Dumped all temp files. Last Cell & UsedRange don't appear to be issues. Not tracking changes. No pivot tables. And still I wind up with 2.56MB sitting somewhere. I originally posted recently under subj of "Excel File Shrink". I started with a 3 MB file that with very little additonal data entry grew to 60MB. Hopefully somebody out there has an answer before I have to pony up and pay MS $245 for Pro Level tech support. Thanks, John -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding place for 2.56MB - A Solution!!
I had a 'growing' Excel file that reached 60MB but following Dave Peterson's
expert advice, it got shrunk from 60MB to 1.58MB! I'd like to share the solution that Dave Peterson gave that worked for me. Dave asked if I had any "objects" and I replied, "Only 11". With Dave's help it turned out I had 43000 of them & 42,989 were hidden. Ron de Bruim says the following are also shapes or objects: 1. ActiveX controls (Control Toolbox) or a linked or embedded OLE objects 2. Controls from the Forms toolbar 3. Controls from the Drawing toolbar I'm running a VBA program that was constantly erasing and then copying data to a spread sheet. Files can get large for many reasons. I had stripped my spreadsheet. Erased all VBA macros. Dumped all temp files. Last Cell & UsedRange didn't appear to be issues. Not tracking changes. No pivot tables. To see if I had hidden objects, Dave had me run the Macro below: Open your troublesome workbook. Hit alt-F11 to get to the VBE hit ctrl-g to see the immediate window Type the statement below in the 'immediate' and hit enter: ?activesheet.shapes.count If this 'counting' macro yield a large number, keep going. Dave then gave me the macro below which will select ALL macros on a sheet at one time, and then you hit DELETE. Sub testme() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets For Each shp In wks.Shapes shp.Delete Next shp Next wks End Sub If you want to make all the Shapes or Objects visible, then Dave gave this macro: Sub testme() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets 'MsgBox wks.Shapes.Count For Each shp In wks.Shapes shp.Visible = True Next shp 'MsgBox wks.Shapes.Count Next wks End Sub Here's another of Dave's macros that will go through each sheet and delete all shapes and objects: Sub testme() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets 'MsgBox wks.Shapes.Count For Each shp In wks.Shapes shp.Visible = True Next shp 'MsgBox wks.Shapes.Count Next wks End Sub "Be aware that comments are shapes, cells with data validation are shapes, autofilters are shapes. So you may want to look at Ron de Bruin's site if any of your worksheets have comments, data|validation, or autofilter. If you have any of that stuff on any sheet, then don't use the one above." From Ron de Bruin's site: http://www.rondebruin.nl/controlsobjectsworksheet.htm Thanks Dave. Great Job!! My Applicant Tracking System works and everyone in the office is relieved and impressed!!! "Dave Peterson" wrote: Tracking Changes wasn't enabled. (Also in a private reply) I don't open other people's files--and it's a small breech of newsgroup etiquette to send files without asking. But when you have lots of shapes, the shapes.selectall can have problems. Maybe just looping through the shapes would be sufficient: Sub testme() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets For Each shp In wks.Shapes shp.Delete Next shp Next wks End Sub Be aware that comments are shapes, cells with data validation are shapes, autofilters are shapes. So you may want to look at Ron de Bruin's site if any of your worksheets have comments, data|validation, or autofilter. If you have any of that stuff on any sheet, then don't use the one above. From Ron de Bruin's site: http://www.rondebruin.nl/controlsobjectsworksheet.htm Sub Shapes4() 'Dave Peterson and Bob Phillips'Example only for the Forms controls Dim shp As Shape Dim testStr As String For Each shp In ActiveSheet.Shapes If shp.Type = 8 Then If shp.FormControlType = 2 Then testStr = "" On Error Resume Next testStr = shp.TopLeftCell.Address On Error GoTo 0 If testStr < "" Then shp.Delete Else shp.Delete End If End If Next shp End Sub John Childs wrote: Dave, I tried to send the below directly to your email address. JC Dave, I took out the wks.Shapes.SelectAll as you suggested. When I ran the macro with this left in, I would get "Out of Memory" msg. On one of my sheets, the # of shapes was reduced to zero. On another sheet, the # of sheets went from 21,000 to 15,000, but couldn't reduce it any further. I'm attaching a file with several "tons" of shapes if you wouldn't mind taking a whack at it and getting a first hand glimpse of what I've been wrestling with. Fyi, even when I delete some shapes, it doesn't seem to reduce the size of the file now sitting at 14.2MB (it started out at less than 3MB). Sub testme() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets 'XXXXX wks.Shapes.SelectAll On Error Resume Next Selection.Delete If Err.Number < 0 Then Err.Clear For Each shp In wks.Shapes shp.Delete Next shp End If On Error GoTo 0 Next wks End Sub "Dave Peterson" wrote: Maybe, maybe not. When there are tons of shapes (and I don't recall how many shapes make tons), the selection.delete line will fail. The one I posted just before you posted this was my way to react to that failure. But I didn't take the time to add tons of shapes to the worksheet--so I don't know for sure! But I do know that the wks.select line isn't really required--so just add that change: Option Explicit Sub testme() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets wks.Shapes.SelectAll On Error Resume Next Selection.Delete If Err.Number < 0 Then Err.Clear For Each shp In wks.Shapes shp.Delete Next shp End If On Error GoTo 0 Next wks End Sub wrote: Will this macro work? Sub testme() Sheets("Home").Select Dim wks As Worksheet For Each wks In Worksheets wks.Shapes.SelectAll Selection.Delete Next wks End Sub "Dave Peterson" wrote: You want to delete them all? You could use a macro: Option Explicit() sub testme() dim wks as worksheet for each wks in worksheets wks.shapes.delete next wks end sub Hopefully that won't break when there are so many shapes in each sheet. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm wrote: DAve, amazing. 8 out of 9 sheets show the exact same number of objects or shapes.....22,913!! I have no idea how they got there. Can you now tell me how to view them and then delete them? Thanks, John "Dave Peterson" wrote: Yep, those shapes would be objects. Sometimes people add pictures to the worksheets and those pictures can be pretty large. Open your troublesome workbook. Hit alt-F11 to get to the VBE hit ctrl-g to see the immediate window Type this and hit enter: ?activesheet.shapes.count Swap back to excel and change sheets and do it again. Maybe there are shapes you can't see??? John Childs wrote: Dave, thanks for hanging with me. Pardon my ignorance, but is a box formed using the Autoshape tool called an "object"? If so, yes I do have have about 10 objects on my sheet. Still two mysteries to me: 1) sometimes I can run a VBA macro and the file size stays absolutely the same. Then I run it again, and the size increases. I know I must be doing something different each time, but I haven't narrowed it down yet. 2) Any clue on why a completed stripped/deleted sheet including macros could still show a file size of 2.56MB? Thanks, John "Dave Peterson" wrote: Do you have any objects on any of the worksheets? Edit|Goto|special|objects may help you find them. wrote: Can't determine why Excel 2003 file size is growing. Stripped my spreadsheet. Erased all VBA macros. Dumped all temp files. Last Cell & UsedRange don't appear to be issues. Not tracking changes. No pivot tables. And still I wind up with 2.56MB sitting somewhere. I originally posted recently under subj of "Excel File Shrink". I started with a 3 MB file that with very little additonal data entry grew to 60MB. Hopefully somebody out there has an answer before I have to pony up and pay MS $245 for Pro Level tech support. Thanks, John -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No place for sum | Excel Worksheet Functions | |||
Hiding a button when hiding rows | Excel Discussion (Misc queries) | |||
Nil in Place Off #N/A | Excel Worksheet Functions | |||
This place ROX!! | Excel Discussion (Misc queries) | |||
1st, 2nd, 3rd Place etc..... | Excel Worksheet Functions |