Read .xlsm file as .xlm

[easperhe29]

If I save a .xlsx/m file in .xml format, I can read the .xml format MUCH faster than the .xlsm format. when processing 100s of files, this can make a ~30x reduction in runtime. Since .xlsx/m are compressed xml, is there any way to read in the .xlsx/m file as a xlm file directly? I believe this is effectively what openpyxl within python does.

 

[Dan_W]

There is. As you point out, XLSX/XLSM files are essentially a collection of compressed XML files (for the most part), and it is indeed quicker to parse through those files than opening up each file changing it/saving it, etc.

@johnnyL and I had a project a few years ago which used this method to correct workbooks with phantom rows/columns/cells. We demonstrate the method here: Alternative approach to fix those problematic excel files when excel sees 1048576 as the last row # & you can't fix it.

I believe you're right about the openpyxl method - that's been my understanding, but without reading the source code, my suspicion is that it uses a ZIP reader that decompresses the byte data of certain segments of the file, thereby avoiding the need to decompress the files using the Shell method, which is what we have done above. I believe Cristian Buse's ZipTools library should be able to do it, but I haven't checked: GitHub - cristianbuse/Excel-ZipTools: Interact with the component parts of an Excel file

Separately, depending on what it is that you want to do exactly, if all you're doing is extracting data from workbooks, you could always try PowerQuery or ADODB methods.