Forum Discussion

Copper Contributor

Apr 14, 2024

Solved

Measuring Sheet Size Using VBA

Any ideas to improve this code? This macro is intended to calculate the size of each sheet in Excel. In practice, it will trim fat from a template frequently used at work. For instance, a decisio...

developer

excel

Macros and VBA

JKPieterse
Apr 15, 2024
Riley_Johnson If the workbook is in xlsx or xlsm format, you could look in the zip container to see the size of each sheet's xml file:
Using my editOpenXML example code you could easily extract the xml of any sheet and measure how large it is by taking the Len of the returned (xml) string. See https://jkp-ads.com/articles/excel2007fileformat02.asp

Riley_Johnson

Copper Contributor

Apr 16, 2024

JKPieterse Thank you for sharing! This is probably the most technical I've gotten with Excel before. That said, please pardon me if this is a dumb question.

I noticed that once the object was zipped back together, there was no procedure to convert it back to a .xlsx or .xlsm file. So, in effect, is the original .xlsx or .xlsm file destroyed? This is my folder after running the demo procedure in EditOpenXML 004.

JKPieterse

Silver Contributor

Apr 16, 2024

I haven't visited that code in ages. But I think when the class instance is properly destroyed at the end of the code, it should automatically clean up after itself.