How do I find invisible objects contributing to excel file size?

[Francis O]

I have 2 EXCEL files with the same amount of data but have different sizes in
bytes.

One file size is 50KB the other, 1.5MB. What could be contributing to this
difference in size, and how do I find out the hidden objects that contribute
to this size of the file with 1.5MB?

 

[FSt1]

hi
not sure but i think that one file may have lost it used range reference.
see this site to reset the used range.
http://www.contextures.com/xlfaqApp.html#Unused

regards
FSt1

 

[ShaneDevenshire]

Hi,

If the objects are really hidden - choose Tools, Options, View tab, Ojects,
Show All.

On each sheet you can try this: press F5, choose Special, pick Objects and
click OK. If anything is selected press Tab to move to the next object.

Of course the most likely culprit is a bitmap image, which the above
technique will allow you to find.

 

[Dave Peterson]

You could run a macro that looks for hidden shapes:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim Shp As Shape

On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
For Each Shp In wks.Shapes
If Shp.Visible = msoFalse Then
MsgBox "Found one:" & vbLf & Shp.Name & vbLf & wks.Name
Shp.Visible = msoTrue
End If
Next Shp
Next wks

 

[tmexpress]

I'm having a very similar problem to the original poster. I am working with
Excel work books that have a number of embedded files in them. As such, we
are attempting to re-use the work books and cleaning out the data where
necessary. For example, I have a 100mb work book that contains all the
attachments which are embedded as objects. If I go into each Excel work
sheet and highlight the object and press delete, or cut, or Alt-E-D shortcut,
etc, it removes the link to the object. However, this has no impact on the
file size. Even though I have removed each and every object (and confirmed
with using F5 -> Special -> Objects) is gone, the file size remains 100mb.

As such, it is my understanding that Excel still has the file embedded,
though cannot be selected/deleted/removed/etc, and just the link to the
actual object. I further confirmed this by just copying/pasting the
necessary information into another Excel work book and the file size was very
small as it was limited to text.

Is there any way to delete/remove embedded objects completely so that file
sizes come down? There is absolutely no reason why an Excel file with just
text should be 100mb other than that it is somehow retaining the embedded
files which the user has removed. Please help!