Excel Spreadsheet Size

[Hack]

I just got, via FTP, 4 Excel spreadsheets from one of my clients.

Each file has four sheets in them. Three of the sheets only have a heading and the word NONE in the top cell.

The other sheet has data from C1 to K40.

Its size is 1,853MB - If I copy the data from that sheet to a brand new sheet, the file size is 17K.

What the heck is making the spreadsheet from my client so large? I can't see it.

(I also can not zip the sheet and attach it as the information on it is very properity to my client.)

Any hints/suggestions/witchs brew that might lend a clue?





Edit: There is no VBA code or macros in any of these sheets.

[RobDog888]

Even though it says "None" there may be other non visible data in the sheet. Unhide rows/columns and look if the sheet is filled with a space in each cell of something like that.

If you delete the entire contents of the sheet and retype "None" in it does the size change? (Only do after you verify there is no hidden data)

[DKenny]

Hack
Excel can get funny around the sizes of the used range on a sheet.
On each Sheet check the bottom corner of the used range, using Ctrl&End. I'll wager that its way down the sheet.
If so, then Delete all rows below, and all columns right of, your "real" used range. Then save the file.
If this was the issue, that should resolve it.

Failing that, can you upload the file or is confidential?

[Hack]

Failing that, can you upload the file or is confidential?

(I also can not zip the sheet and attach it as the information on it is very properity to my client.)

However, I took your suggestion about the Ctrl-End (which didn't fix it) one step further, and I deleted EVERYTHING from the spreadsheet and saved it under a different name.

Guess what...the file size didn't change. I now have a functionally empty spreadsheet that weighs in at almost 2MB?

No go...even zipped and empty, it still exceeds the attachment limit.

[Hack]

Even though it says "None" there may be other non visible data in the sheet. Unhide rows/columns and look if the sheet is filled with a space in each cell of something like that.

How do I unhide rows/columns?

[RobDog888]

Format > Row (or Column) > Unhide Row (or column).

[si_the_geek]

You may also want to take a look here:

Format > Sheet > Unhide

[zaza]

This happens if you add a large picture to the spreadsheet, save it, then delete the picture and save it again. The file size remains very large but the picture is no longer present.

Alternatively, there may be a picture present but with dimensions of zero (try it, particularly when positioned over the grid!) - in which case it can be deleted by selecting the entire sheet and Right-click, Delete (Not Del key, which will not delete it...)

It's a possibility anyway. BTW, I'm running Office XP.

zaza

[malik641]

I would have to say probably a bunch of data in a hidden sheet(s) as si_the_geek was suggesting. I received an Excel file that was 32MB once because the formulas were copied ALL the way down to row 65536 ....man that thing was slow!

[Hack]

You may also want to take a look here:

Format > Sheet > Unhide

If I do this and Unhide is disabled, would it be safe to assume that means that nothing is hidden?

[si_the_geek]

Correct.

If Rob's suggestions also failed, I am officially confused... I can only guess that zaza's suggestion is the one - you should be able to use VBA to find any objects (using the Sheet.Shapes collection)

[Hack]

you should be able to use VBA to find any objects (using the Sheet.Shapes collection)

Sheets.Shapes collection?

[RobDog888]

Also, Sheets.OLEObjects collection too.

Shape object represents an object in the drawing layer, such as an AutoShape, freeform, OLE object, or picture.

[si_the_geek]

Shapes is a collection (a member of the Worksheet object) that contains all the picture objects (inc Clipart/WordArt type things) that are on the sheet.

Here's a quickie that tells you if any sheets in the Active Workbook have any picture objects:

Sub ListShapes()
 
Dim oSheet As Worksheet
 
  For Each oSheet In ActiveWorkbook.Worksheets
    If oSheet.Shapes.Count > 0 Then
      MsgBox "Sheet '" & oSheet.Name & "' has " & oSheet.Shapes.Count & " graphical object(s)"
    End If
  Next oSheet
 
End Sub

[Hack]

Thanks Si, I'll give that a shot.

[Hack]

Hmmm...the darn sheet is still 1.8MG and there is nothing in it.

I hate throwing in the towel, but it looks like it is getting to that. However, before I do, does anyone else have anything I can try?

[RobDog888]

What version of Excel were the workbooks generated with? Different then your version? Also, were they zipped before they were FTP'd?

[malik641]

If I do this and Unhide is disabled, would it be safe to assume that means that nothing is hidden?

Not necessarily. There is code to hide this option even if there are hidden sheets. Example:

Sub Hide_Unhide_ComBar()
Application.CommandBars("Worksheet Menu Bar").Controls("F&ormat").Controls("Sheet").Controls("Unhide...").Enabled = False
End Sub

This is TOTALLY unlikely, but it COULD be there. Are you able to access the modules? That is a surefire way to check for hidden sheets.

[zaza]

Hm, did you guys read my earlier post fully? There doesn't need to actually be an object present - I guess it keeps the picture info in the header of the file or something like that.
I have at present a file exactly like Hack's, created as above. Doing a count of the shapes gives 0. There is nothing there. But the size of the file is 2.3Mb. I'd post it, but it's too big!

zaza

[RobDog888]

So then the best solution would be to create a new blank workbook/sheets and copy over the sheets data.

[zaza]

If you create a blank Excel sheet and save it, then open it in Wordpad, it should look pretty small - about 30 or 40 lines.
I then did the same with my big file...and it's massive. The info is still all there, but the picture isn't visible.
Apparently, Linuxy types can actually get at what's in the header somehow. I don't know much about it but a Windows-hater I know claims all sorts of M$ conspiracies over what's stored in the header of Office documents and he reckons they're easily readable.

zaza

[Hack]

So then the best solution would be to create a new blank workbook/sheets and copy over the sheets data.

I did that, and the new file was around 17K.

That still doesn't explain what happened to the original. If I'm understanding correctly, whoever created the original put a whole bunch of stuff in the headers?

[Hack]

There is nothing there. But the size of the file is 2.3Mb. I'd post it, but it's too big!

I know what you mean. Even empty, and zipped, this spreadsheet is still over 500K (which means I can't attach it.)

[RobDog888]

Too bad MS didnt make a Compact and Repair for Excel like they did for Access.

[Hack]

Too bad MS didnt make a Compact and Repair for Excel like they did for Access.

Hmmm...never thought of that.

Maybe someone else made one. It might be worth a few moments to see what Google turns up.

[RobDog888]

If not then this sounds like a good candidate for an Add-In.

[zaza]

If I'm understanding correctly, whoever created the original put a whole bunch of stuff in the headers?

Perhaps. Either that or it didn't actually delete the (picture code)? I'm not sure exactly where it stores the data when a picture is added, and I can't get this method to work all the time. Maybe there is something to do with Autosave, maybe something else. What I do know is that opening the Excel file as text in Wordpad produces a whole lot more stuff than just the blank workbook - so there is data in there somehow. Whether there's any way of retrieving that is another matter.
I look at it this way - it's as if the picture object and the data in the picture object are two separate things. Under normal circumstances, the picture object links to the picture data and displays it. Usually, if you delete the picture object, then the data is deleted as well. Here, however, the link between object and data has been broken so that the object has been deleted but the data hasn't. Hence the file size remains large. And there's probably no way to "reattach" the data to a picturebox, so you can't tell what it is.
Can you speak to your client and see if anything like this might have happened?

[Hack]

Perhaps. Either that or it didn't actually delete the (picture code)? I'm not sure exactly where it stores the data when a picture is added, and I can't get this method to work all the time. Maybe there is something to do with Autosave, maybe something else. What I do know is that opening the Excel file as text in Wordpad produces a whole lot more stuff than just the blank workbook - so there is data in there somehow. Whether there's any way of retrieving that is another matter.
I look at it this way - it's as if the picture object and the data in the picture object are two separate things. Under normal circumstances, the picture object links to the picture data and displays it. Usually, if you delete the picture object, then the data is deleted as well. Here, however, the link between object and data has been broken so that the object has been deleted but the data hasn't. Hence the file size remains large. And there's probably no way to "reattach" the data to a picturebox, so you can't tell what it is.
Can you speak to your client and see if anything like this might have happened?

You make good points. Sometimes I'm allowed to deal with the clients directly, other times I have to go through the assigned Business Analyst. This is one of those times, but I will pass this along to the Business Analyst for comments from the clients.

[RobDog888]

I tell you I cant wait for Office 2006 with its Open source XML file format. But from what I read its going to be compressed. Maybe there will be a way to clean a file or this problem will be fixed for 2006.

[WorkHorse]

Hmmm...the darn sheet is still 1.8MG and there is nothing in it.

I hate throwing in the towel, but it looks like it is getting to that. However, before I do, does anyone else have anything I can try?

It is probably something obvious once you find it, but Office files can have so much variation that it is difficult to guess what may be going on without actually looking at the file. Make sure that all sheets are unhidden. Check all the cells in the used range to make sure there are no formulas accessing outside workborks. Check the header/footers for any anomolies. Check VBA for any large mdules. Check for any add-in. Check everything under the "Tools" menubar for anything that looks strange--shared book, custom toolbars, etc.

If nothing strange shows up, save the file under a different name. If there is no change in the file size, try copying the sheets one at a time to a new workbook and saving to see which sheet is causing the problem. If no copied sheets cause a problem, start deleting the sheets from the saved book and saving them different names. If you find a big difference, you can start looking at that sheet, if you don't then it isn't any property of a worksheet, but a property of the workbook, which narrows things down.

[Hack]

start deleting the sheets from the saved book and saving them different names. If you find a big difference, you can start looking at that sheet, if you don't then it isn't any property of a worksheet, but a property of the workbook, which narrows things down.

I have tried everything that you mentioned except this part. I'm going to give this a shot and see what happens.

[malik641]

Any word on this? Figure anything out?

And I forgot to mention something (even though I think everyone is leaning toward the kept info in the headers)....

If I do this and Unhide is disabled, would it be safe to assume that means that nothing is hidden?

Besides what I said about disabling the command bar, if sheets are very hidden:

Sheets("Hidden Sheet").Visible = xlSheetVeryHidden

Then you also will not see the "Unhide..." command bar if a sheet(s) is hidden. Can you access the VBE??? I'm pretty sure that's a sure-fire way to check for how many sheets there really are in the workbook.

I don't mean to go on and on about this, but (for me, anyway) sometimes the most difficult problems have the easiest, most overlooked answer (based on my strange luck).

[doasidont]

Two things I've come across. 1) there's an add-in called Excess Format Cleaner which might help. 2) if there's a pivot table on the wb, check to see if source data includes entire columns instead of defined rows.