hiding place for 2.56MB?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
(e-mail address removed)
 
Do you have any objects on any of the worksheets?

Edit|Goto|special|objects
may help you find them.
 
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
 
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???
 
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
 
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

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
Click to expand...
 
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
 
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.


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
Click to expand...
 
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
 
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

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
Click to expand...
 
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
 
(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 said:
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
Click to expand...
 
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
 
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.
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()
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
Click to expand...

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
Click to expand...

"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!!!



:
Tracking Changes wasn't enabled.
 
Back
Top