在 Excel VBA 中保存单个工作表

Saving individual sheet in Excel VBA

提问by Pradeep Simha

Currently we have a Excel VBA application, which consists of 25-30 Excel sheet (and all of those sheets all hidden and we do background processing on them and display it to VBA forms UI), and we have developed a UI using VB forms. But problem we are facing is whenever we click on Save button using this code:

目前我们有一个 Excel VBA 应用程序，它包含 25-30 个 Excel 工作表（所有这些工作表都隐藏起来，我们对它们进行后台处理并将其显示到 VBA 表单 UI），我们已经开发了一个使用 VB 表单的 UI。但是我们面临的问题是每当我们使用以下代码单击“保存”按钮时：

ThisWorkbook.Save

ThisWorkbook.Save

But this saves entire workbook not an individual sheet, so even if we make changes in single sheet it saves entire workbook and this save processing makes very slow (since it needs to save all excel sheet containing lot of data unnecessary, even if there is no changes).

但这会保存整个工作簿而不是单个工作表，因此即使我们在单个工作表中进行更改，它也会保存整个工作簿，并且这种保存处理非常缓慢（因为它需要保存包含大量数据的所有 excel 工作表，即使没有变化）。

My question is is there any way we can save "only one sheet in a particular excel sheet" not an entire excel file?

我的问题是有什么方法可以保存“特定excel表中只有一张表”而不是整个excel文件？

Note:I am a Java developer and I worked on VBA before, But it was years back, and I have forgotten bit. Any guidance would be appreciated. Or any pointers on how to handle this situation would be appreciated. Please let me know if you need any more information, I can edit this question.

注意：我是一名 Java 开发人员，我以前在 VBA 上工作过，但那是几年前的事了，我有点忘记了。任何指导将不胜感激。或者任何有关如何处理这种情况的指示将不胜感激。如果您需要更多信息，请告诉我，我可以编辑此问题。

What I have tried already?I did a lot of research from yesterday, I searched in previous questions on SO, but didn't get any useful information. As per my research it says we cannot do this. Am I on right path?

我已经尝试过什么？我从昨天开始做了很多研究，我搜索了以前关于 SO 的问题，但没有得到任何有用的信息。根据我的研究，它说我们不能这样做。我在正确的道路上吗？

回答by Ripster

The short answer is no. You cannot save a single worksheet at a time.

最简洁的答案是不。您不能一次保存一个工作表。

You may want to try to reduce the amount of data in the workbook. Try storing data in several workbooks and when it is needed, open that specific workbook, make the needed changes, and then close it.

您可能想尝试减少工作簿中的数据量。尝试将数据存储在多个工作簿中，并在需要时打开该特定工作簿，进行所需的更改，然后将其关闭。

If it is necessary to have access to all data at once then consider using access or some other database.

如果需要一次访问所有数据，请考虑使用 access 或其他数据库。

It is also possible that the sheets have "blank data". Cells that don't contain anything in them but excel thinks they do so when saving it tries to save way more than needed.

工作表也可能有“空白数据”。不包含任何内容但 excel 认为他们这样做的单元格在保存时会尝试保存超出需要的方式。

回答by Dave Sexton

Assuming that it is the active worksheet that you want to save then you could do something like this:

假设它是您要保存的活动工作表，那么您可以执行以下操作：

ActiveSheet.Copy
ActiveWorkbook.Close True, "path"

This copies the active worksheet which creates a new workbook which will become the active workbook. Then just call the close method on that and give it a file name.

这会复制活动工作表，从而创建一个新工作簿，该工作簿将成为活动工作簿。然后只需调用 close 方法并给它一个文件名。

回答by Peter Albert

I think you should consider splitting your application into multiple workbooks:

我认为您应该考虑将您的应用程序拆分为多个工作簿：

1. The workbook that contains all the logic, user forms and programming code. This workbook handles all other workbooks as well as the displaying of it. Potentially, this could be even an "Application Specific Addin", that stays dormant but activates as soon as any of it's subsequent workbooks gets opened. For this architecture approach check out the section on "Application Specific Addins" in this link.
   
   This workobook/add-in can also hide the other workbooks, so that the user will not notice it's multiple workbooks.
2. One or multiple data workbooks: Depending how interlinked the data is, you can separate this, e.g. in a "Sales data" workbook which contains the large database, as "Base data" workbook, that contains all the smaller data (e.g. products or stores tables).

1. 包含所有逻辑、用户表单和编程代码的工作簿。该工作簿处理所有其他工作簿以及它的显示。潜在地，这甚至可能是一个“特定于应用程序的插件”，它保持休眠状态，但只要打开它的任何后续工作簿就会激活。对于这种架构方法，请查看此链接中的“特定于应用程序的插件”部分。
   
   此工作簿/加载项还可以隐藏其他工作簿，以便用户不会注意到它是多个工作簿。
2. 一个或多个数据工作簿：根据数据的相互关联程度，您可以将其分开，例如在包含大型数据库的“销售数据”工作簿中，作为包含所有较小数据（例如产品或商店）的“基础数据”工作簿表）。

This way, you can reduce the saving to the "relevant" sheets. However, of course this requires quite a bit of reprogramming - but it's worth the effort, as for instance it also allows to provide updates/bug fixes without having the transfer the data between versions, as you only need to distribute the the file with programming logic. :-)

这样，您可以减少对“相关”工作表的保存。然而，这当然需要相当多的重新编程 - 但这是值得的，因为例如它还允许提供更新/错误修复而无需在版本之间传输数据，因为您只需要通过编程分发文件逻辑。:-)