vba 减小 Excel 的文件大小

Reduce the file size of Excel

提问by Ram

I'm working in an excelapplication and providing a menu to the user to add a new worksheet in that excel application (Excel Workbook).

我正在使用excel应用程序，并为用户提供一个菜单以在该 excel 应用程序（Excel 工作簿）中添加新工作表。

The worksheet will be added once the user clicks the "OK" button and I'm using a template to add this worksheet (The template has lot of formatting and formulas in it)

一旦用户单击“确定”按钮，就会添加工作表，并且我正在使用模板添加此工作表（该模板中有很多格式和公式）

Lets say the file size is 10 MB after adding a worksheet if the workbook is saved. Then I close the Excel application and reopen it and save the file then the file size is getting reduced to 8 MB.

假设工作簿已保存，则在添加工作表后文件大小为 10 MB。然后我关闭 Excel 应用程序并重新打开它并保存文件，然后文件大小减少到 8 MB。

Can anybody let me know what could be the reason for the same?

任何人都可以让我知道可能是什么原因？

回答by Nigel Heffernan

Unused formattingis the most common cause of 'file bloat' in Excel: a close second is that the 'used range' on a sheet is larger than you think - Excel maintains metadata about every cell on a sheet from A1 to the lowest, furthest cell you've ever edited and that takes a lot of memory.

未使用的格式是 Excel 中“文件膨胀”的最常见原因：紧随其后的是工作表上的“使用范围”比您想象的要大 - Excel 维护有关工作表上从 A1 到最低、最远的每个单元格的元数据您曾经编辑过的单元格，这需要大量内存。

But the 'used range' in a new worksheet is saved as cell A1 to the last cell with data in it.

但是新工作表中的“使用范围”被保存为单元格 A1 到最后一个包含数据的单元格。

A hint: take a look at the sliders in the scroll bars for your worksheet, vertical and horizontal: are they chunky grey blocks that look as if they'll only scroll fifty rows or columns? Or are they little grey dashes that scroll hundreds of rows if the mouse goes near them? When a slider bar moves a distance equal it's own length, it completes one 'page down' (or page sideways) in the available page space; the larger the space or page set (or used range in Excel), the smaller they will be.

提示：看看工作表滚动条中的滑块，垂直和水平：它们是看起来只能滚动五十行或五十列的厚实灰色块吗？或者如果鼠标靠近它们，它们是否会滚动数百行的灰色小划线？当滑动条移动的距离等于它自己的长度时，它会在可用的页面空间中完成“向下翻页”（或横向翻页）；空间或页面集（或 Excel 中的使用范围）越大，它们就越小。

I'd suggest de-bloating (There's got to be a better word! Deflating? Detumescing? Deforming?) your workbook. There used to be lots of third-party tools for this but Excel 2003 onwards has a built-in workbook rebuilder: just click 'Save' and 'Save As...' a web page. Then exit Excel, reopen Excel, and use 'Open' to open the html workbook (despite being an HTML file, it'll have an Excel icon), and 'Save As...' an xls file again. Close Excel again, and have a look at the file size.

我建议去腹胀（必须有一个更好的词！泄气？消肿？变形？）你的工作簿。曾经有很多第三方工具用于此，但 Excel 2003 以后有一个内置的工作簿重建器：只需单击“保存”和“另存为...”一个网页。然后退出 Excel，重新打开 Excel，并使用“打开”打开 html 工作簿（尽管它是一个 HTML 文件，但它会有一个 Excel 图标），然后再次“另存为...”一个 xls 文件。再次关闭 Excel，并查看文件大小。

回答by Alban

You can clear all columns and rows after the last cells realy used to reset the last cells ( [Ctrl]+[End] ). You can also enable the compression of images.

您可以在最后一个单元格真正用于重置最后一个单元格（ [Ctrl]+[End] ）后清除所有列和行。您还可以启用图像压缩。

I dev an AddIns with auto install (in french) to include in context menu 3 new buttons:

我开发了一个具有自动安装功能的插件（法语），以在上下文菜单中包含 3 个新按钮：

1. Optimize
2. Optimize and Save
3. Toggle Case

1. 优化
2. 优化和保存
3. 切换案例

This is based on KB of Microsoft office 2003with personals improvement :

这是基于Microsoft Office 2003 的 KB并进行了个人改进：

1. add compression of images
2. fix bug for Columns
3. feat compatibility with excel 2007 - 2010 - ...

1. 添加图像压缩
2. 修复列的错误
3. 与 excel 2007 - 2010 的壮举兼容性 - ...

SOLUTION> you can download my *.xla file here

解决方案> 你可以在这里下载我的 *.xla 文件

回答by Zack Graber

Nile is correct, the "UsedRange" does not correctly get reset causing a lot of bloat. You can reset this very simply with 1 line of VBA code.

Nile 是正确的，“UsedRange”没有正确重置，导致大量膨胀。您可以使用 1 行 VBA 代码非常简单地重置它。

ActiveSheet.UsedRange

Open the Developer tab in Excel, insert a Module, add the following macro, and run it

在Excel中打开Developer选项卡，插入一个Module，添加如下宏，然后运行

Public Sub ResetUsedRange()
    ActiveSheet.UsedRange
End Sub

You could also tie this macro to an Excel keyboard shortcut for quicker access using the Application.OnKey event

您还可以将此宏绑定到 Excel 键盘快捷键，以便使用 Application.OnKey 事件更快地访问

回答by Gayathri Visvanathan

You can use save as macro enabled file to reduce it to optimal memory capacity. I used it to reduce the size of around 10 MB to 4.5 MB. it helped me a lot in solving my problem.

您可以使用另存为启用宏的文件将其减少到最佳内存容量。我用它来将大约 10 MB 的大小减少到 4.5 MB。它帮助我解决了我的问题。