I have a one page workbook with 6002 rows, 137 columns, considerable formating and a single chart that when saved is 11,985,920 bytes? The page was created by a macro. Duplicating the page increases the file size by 902,920 bytes. Removing the chart saves 4096 bytes and removing all formating and restoring column and row widths and heights to default values saves an additional 3072 bytes. What is the origin of the initial approximately 11,950,000 bytes?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Huge file size in Excel 2000
- Thread starter Rujad
- Start date
Try these:
Also make sure that you're NOT saving the file as a lesser version.
Also make sure that you're NOT saving the file as a lesser version.
- Thread starter
- #3
Thanks for the tip sheet.
Using "clear|all" on the unused rows and columns saved an additional 1536 bytes. The file is still 11,984,384 bytes when saved.
Cuting the chart and pasting as a picture (enhanced metafile) increased the size of the file by 46,080 bytes. The cut took forever compared to the "clear" option but didn't save any space. I assume the longer time was due to coping the chart to the clipboard although it only appears to be about 4K.
The other tips are not relevent. The sheets are named Sheet1, Sheet2, etc. very orderly and I used "move or copy|create a copy" to duplicate the sheets. Each duplicate of the sheet adds, at most, 925,000 bytes.
Still a puzzle.
Using "clear|all" on the unused rows and columns saved an additional 1536 bytes. The file is still 11,984,384 bytes when saved.
Cuting the chart and pasting as a picture (enhanced metafile) increased the size of the file by 46,080 bytes. The cut took forever compared to the "clear" option but didn't save any space. I assume the longer time was due to coping the chart to the clipboard although it only appears to be about 4K.
The other tips are not relevent. The sheets are named Sheet1, Sheet2, etc. very orderly and I used "move or copy|create a copy" to duplicate the sheets. Each duplicate of the sheet adds, at most, 925,000 bytes.
Still a puzzle.
Hi!
I think 11 MB is quite reasonable. If you do the math, that works out to be 14.5 bytes per cell. It is reasonable to assume that you have an average of 14 characters per cell. I did a random fill of 822,274 cells (size of your spsht) and came up with 11.2 MB and I had an average of 6 chars per cell (without any formatting)
Indu
I think 11 MB is quite reasonable. If you do the math, that works out to be 14.5 bytes per cell. It is reasonable to assume that you have an average of 14 characters per cell. I did a random fill of 822,274 cells (size of your spsht) and came up with 11.2 MB and I had an average of 6 chars per cell (without any formatting)
Indu
- Thread starter
- #5
I don't disagree with you, however if I duplicate the sheet so that there are two identical sheets, chart and all, the file size increases by only 925,000 bytes. So, the puzzle is, if each sheet is 925,000 bytes where did the other 11 million bytes come from?
- Thread starter
- #6
I don't disagree with you, however if I duplicate the sheet so that there are two identical sheets, chart and all, the file size increases by only 925,000 bytes. So, the puzzle is, if each sheet is 925,000 bytes where did the other 11 million bytes come from?
When I repeat your exercise with 6 alpha characters per cell I also get 11.2MB. When I duplicate that sheet the workbook size doubles to 22.4MB. That's not my problem. My sheets appear to be less than 1MB each but the workbook is 11 MB too big.
When I repeat your exercise with 6 alpha characters per cell I also get 11.2MB. When I duplicate that sheet the workbook size doubles to 22.4MB. That's not my problem. My sheets appear to be less than 1MB each but the workbook is 11 MB too big.
And what happens when you copy the sheet to a new file?
- Thread starter
- #8
Thanks for the follow up.
Copying or moving the sheet to a new file has the same result. The first sheet in the workbook creates an 11MB file and supsequent sheets add 900,000 bytes.
Basic arithmetic: 6002 rows, 137 columns, 1.1 characters per cell is just about the right size. The sheet is heavily with values and mostly empty. A COUNT produces 42,952 and a COUNTA is 67,168 plus a single chart that seems to be about 4KB. That's about 8% full and those cells have about 13 characters on average or about 172KB of numeric data and the rest in an average of 30 characters per cell which looks about right.
Copying or moving the sheet to a new file has the same result. The first sheet in the workbook creates an 11MB file and supsequent sheets add 900,000 bytes.
Basic arithmetic: 6002 rows, 137 columns, 1.1 characters per cell is just about the right size. The sheet is heavily with values and mostly empty. A COUNT produces 42,952 and a COUNTA is 67,168 plus a single chart that seems to be about 4KB. That's about 8% full and those cells have about 13 characters on average or about 172KB of numeric data and the rest in an average of 30 characters per cell which looks about right.
I am not an excel guru by any means, but could this possibly be related to the copying of worksheets and you formating on the first??? If the first worksheet always produces the 11M size and each copy only makes about 900K or so, then perhaps the copies are using the format from the first sheet, and therefore do not contain all the code the first sheet does.....just a thought. Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"![[spin]](/data/assets/smilies/spin.gif "[spin] [spin]")
Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
It's a profession of calling...
"Hey Programmer, your application broke again!"
Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
- Thread starter
- #10
Good thought. When I remove the imbedded chart each sheet gets smaller by 4K bytes. Restoring all the formating to General, no fill, no borders, automatic font color, default column widths and row heights each sheet gets smaller by 3K bytes. The saved file is still 11,978,762 bytes with one page and 12,893,184 with two sheets.
I can zip the one sheet file to 768,165 bytes (including the 116 byte zip overhead) and the two sheet file to 936,034 bytes. That leaves the 11MB I can't find at it compresses to approximately 600KB (95%) which means its not null but contains more than an empty workbook.
A completely empty, one sheet file is 13,824 bytes and compresses to 1179 bytes (92%).
I thought it might be expanding internal table information based on the number size of the matrix represented by the highest active cell but a single character in r1000c100 or in r2000c200 is the same size as an empty workbook.
Still looking for an answer. This thing raised #$%@ on the email network if its mailed without compression.
I can zip the one sheet file to 768,165 bytes (including the 116 byte zip overhead) and the two sheet file to 936,034 bytes. That leaves the 11MB I can't find at it compresses to approximately 600KB (95%) which means its not null but contains more than an empty workbook.
A completely empty, one sheet file is 13,824 bytes and compresses to 1179 bytes (92%).
I thought it might be expanding internal table information based on the number size of the matrix represented by the highest active cell but a single character in r1000c100 or in r2000c200 is the same size as an empty workbook.
Still looking for an answer. This thing raised #$%@ on the email network if its mailed without compression.
Again, I am out of my true league here (Access Programmer by trade), but are you using any VBA code and the like in your workbook??? If you are, have you compiled the code??? It sounds like a very similar Access problem in that nothign is truly removed, even when you delete it, until the not compiling/compacting. Again, just thought. Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"
Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
It's a profession of calling...
"Hey Programmer, your application broke again!"
Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
- Thread starter
- #12
You may be on to something here. The sheets are created by VBA code in another workbook and nothing is deleted in the process. However, if I copy|paste the entire sheet into a new workbook, rather than move or copy, everything looks exactly the same, formating, column widths, even the chart but the saved file size is now only 2,749,952 bytes!!!!
Now there are links to the original source? in the new book.
Duplicating this sheet with copy results in a two sheet workbook of only 3,663,360 bytes. So the second page is still 900KB and the first is smaller.
Now why the pointers?
So I deleted the souce workbook. Opening the copyied file gave me the pointer update message; OK gave me a file not found message on the source; and saving the file resulted in an 11MB file!!!
Anywhy I've got a slim clue. I'll modify the VBA to copy|paste the resulting sheets before saving the workbook to see if it produces better results.
Now there are links to the original source? in the new book.
Duplicating this sheet with copy results in a two sheet workbook of only 3,663,360 bytes. So the second page is still 900KB and the first is smaller.
Now why the pointers?
So I deleted the souce workbook. Opening the copyied file gave me the pointer update message; OK gave me a file not found message on the source; and saving the file resulted in an 11MB file!!!
Anywhy I've got a slim clue. I'll modify the VBA to copy|paste the resulting sheets before saving the workbook to see if it produces better results.
- Thread starter
- #13
Final update.
Everything now conforms to expected sizes, about 900KB per sheet. Problem was created when I copied a single sheet from a larger workbook to a new, single sheet workbook. Removing VBA inserted calculations (with copy|paste values) the problem seems to have disappeared. I haven't tried coping all 111 possible sheets but for now it seems OK.
I'm still not sure what it was but I'm spending any more time on it unless it comes back.
Thanks to all for the help.
Everything now conforms to expected sizes, about 900KB per sheet. Problem was created when I copied a single sheet from a larger workbook to a new, single sheet workbook. Removing VBA inserted calculations (with copy|paste values) the problem seems to have disappeared. I haven't tried coping all 111 possible sheets but for now it seems OK.
I'm still not sure what it was but I'm spending any more time on it unless it comes back.
Thanks to all for the help.
- Status
