Converting .xls to .xlsx files with VBA doesn't seem to reduce size

Question

So, I have a bunch of old .xls files that I want to convert to .xlsx files for the sake of disk space economy. Doing it one-by-one is out of the question. I seem to have written some code that actually does open files and save them in new format (plan on adding some lines to delete old files later).

    For Each Myfile In PN
        On Error Resume Next
        Set xlsxWb = Workbooks.Open(Myfile)
        Set xlsWb = xlsxWb
        NameNpath = xlsxWb.FullName & "x"
        xlsxWb.SaveAs Filename:=NameNpath, FileFormat:=xlOpenXMLWorkbook
        xlsxWb.Close SaveChanges:=False
        Kill Myfile
    Next Myfile

There's a problem though. Saving file as or simply converting it with excel usually drastically reduces size of the file, its size becomes like 2 times less. Running my macro doesn't seem to achieve same result, size of the file stays the same, even though it seems to be in .xlsx format now. I'm guessing there might be something fishy going on, like it seems to be in .xlsx format, but it's actually not. Do you have any ideas what might go wrong there?

Answer 1

I seem to have figured it out. For some reason if you don't reopen and close with saving changes a freshly converted file excel doesn't reduce files size. Maybe it compresses file in the background or something - not sure. Right now I just added a few lines opening and closing converted files with saving changes - works for me! Sometimes reducing size so drastically that it becomes 10MBs less

Answer 2

To reduce the size of your XLS files or XLSX files, save then with the XLSB extension. The files will save/close faster, open faster, and will probably shrink around...maybe...1/3 or 2/3 in size.

An XLSB file is a Excel Binary Workbook file format. Binary files are usually read and written to much quicker and are more smaller than non-binary files. This is because binary files are a raw dump (basically 1’s and 0’s) of memory. If you open binary files they might seem like gibberish. Binary files can only be opened if you know the exact type of object that is the file. Text files on the other hand (JSON, XML, CSV, text), as the name suggests, are files that can be opened in text editors (Word, Notepad etc.) and their structure is built into the file can be understood by simply reading the file.

https://analystcave.com/excel-working-with-large-excel-files-the-xlsb-format/