使用 VBA 将 .xls 转换为 .xlsx 文件似乎并没有减少大小
因此，为了磁盘空间经济，我有一堆旧的.xls文件要转换为.xlsx文件。一对一地做是不可能的。我似乎已经编写了一些实际上确实打开文件并以新格式保存的代码（计划添加一些行以稍后删除旧文件）。
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
虽然有问题。将文件保存为或简单地使用Excel转换，通常会大大降低文件的大小，其大小的大小约为2倍。运行我的宏似乎没有达到相同的结果，即使现在以.xlsx格式为.xlsx格式，文件的大小也保持不变。我猜想可能发生了一些可怕的事情，例如似乎以.xlsx格式出现，但实际上不是。 您有什么想法在那里出了什么问题吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
要减小 XLS 文件或 XLSX 文件的大小，请使用 XLSB 扩展名保存。文件的保存/关闭速度更快，打开速度更快，并且可能会缩小...也许...1/3 或 2/3 大小。
XLSB 文件是 Excel 二进制工作簿文件格式。二进制文件通常比非二进制文件读取和写入速度更快并且更小。这是因为二进制文件是内存的原始转储（基本上是 1 和 0）。如果您打开二进制文件，它们可能看起来像乱码。仅当您知道文件对象的确切类型时才能打开二进制文件。另一方面，文本文件（JSON、XML、CSV、文本），顾名思义，是可以在文本编辑器（Word、记事本等）中打开的文件，它们的结构内置于文件中，只需通过以下命令即可理解：读取文件。
https://analystcave.com/excel-working -with-large-excel-files-the-xlsb-format/
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/
我似乎已经想通了。
由于某种原因，如果您不重新打开并关闭并保存更改，则新转换的 Excel 文件不会减小文件大小。也许它会在后台压缩文件或其他东西 - 不确定。现在我只是添加了几行打开和关闭转换后的文件并保存更改 - 对我有用！有时会大幅减小大小，减少 10MB
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