vba “新建” Excel.Application 与 Excel.Application

"New" Excel.Application vs Excel.Application

提问by ProtoVB

I am seeking clarification on the impact of "New" on the objects and the script.

我正在寻求澄清“新”对对象和脚本的影响。

My understanding is that if I need to perform actions on an excel document and the application is closed then I should use New Excel.Application.

我的理解是，如果我需要对 Excel 文档执行操作并且应用程序已关闭，那么我应该使用 New Excel.Application。

If I keep this application active (through an object such as a Workbook for example) and later in the script I decide to open another workbook, should I still use New Excel.Application or would it be better to use Excel.Application then?

如果我保持此应用程序处于活动状态（例如，通过诸如工作簿之类的对象）并且稍后在脚本中我决定打开另一个工作簿，我应该仍然使用 New Excel.Application 还是使用 Excel.Application 会更好？

My concern lies in the fact that I am going to write a long script that will perform actions on at least 5 Workbooks. I will have to a switch from one Workbook to another and then come back to the former...

我担心的是，我将编写一个长脚本，该脚本将对至少 5 个工作簿执行操作。我将不得不从一本工作簿切换到另一本工作簿，然后再回到前者......

If each time the script creates a New Excel.Application, I may end up having quite a lot of them running and I am fearing that this mess would generate issues.

如果每次脚本创建一个新的 Excel.Application 时，我最终可能会运行很多它们，我担心这种混乱会产生问题。

Is it more appropriate to write something like:

写这样的东西是否更合适：

Dim NxlApp as New Excel.Application
Dim xlApp as Excel.Application

  NxlApp.Workbooks.Open "C:\Users\...\WorkbookA.xlsx"
  NxlApp.Visible = True

'Perform actions on WorkbookA (keep it open)


  Set ExcelApp = GetObject("", "Excel.Application.14")
  xlApp.Workbooks.Open "C:\Users\...\WorkbookB.xlsx"
  xlApp.Visible = True

'Perform actions on WorkbookB (keep it open)


'Go back to WorkbookA (using the xlApp variable this time)

  xlApp.Workbook("A.xlsx")...

采纳答案by Graham Anderson

The excel application can have several workbooks open at once so you don't need to instancing a new one. If you from inside excel you should not need to reference the application to open workbooks. You might wish to create several workbook instances e.g.

Excel 应用程序可以同时打开多个工作簿，因此您无需实例化一个新的工作簿。如果您在 excel 内部，则不需要引用该应用程序来打开工作簿。您可能希望创建多个工作簿实例，例如

Dim wbWorkbookA As Workbook
Dim wbWorkbookB As Workbook

Set wbWorkbookA = Workbooks.Open("C:\Users\...\WorkbookA.xlsx")
Set wbWorkbookB = Workbooks.Open("C:\Users\...\WorkbookB.xlsx")

This will open the workbooks and give a reference so that you can manipulate them using your code.

这将打开工作簿并提供参考，以便您可以使用您的代码操作它们。

回答by Jason K.

I am going to write a long script that will perform actions on at least 5 Workbooks

我将编写一个长脚本，该脚本将对至少 5 个工作簿执行操作

Good question, short answer is that you can open 5 workbooks in one Application object.

好问题，简而言之，您可以在一个 Application 对象中打开 5 个工作簿。

If you're in excel (else see below) then you already have an Excel.Application. You can then set each workbook to a different object or reference them by name:

如果您使用的是 excel（其他见下文），那么您已经拥有 Excel.Application。然后，您可以将每个工作簿设置为不同的对象或按名称引用它们：

dim wb1, wb2 as Excel.Workbook, wb3 as New Excel.Workbook 'blank is
same as Variant set wb1 = application.open("path/wb.xlsx") msgbox
wb1.name                  'ref by object msgbox
workbooks("wb.xlsx").name 'ref by name

My understanding is that if I need to perform actions on an excel document and the application is closed then I should use New Excel.Application

我的理解是，如果我需要对 Excel 文档执行操作并且应用程序已关闭，那么我应该使用 New Excel.Application

If you're outside Excel (like in Access) then you may want to create an Excel.Application object, here are some tips:

如果您在 Excel 之外（如在 Access 中），那么您可能想要创建一个 Excel.Application 对象，这里有一些提示：

Dim nXlApp as New Excel.Application

has the same effect as:

具有相同的效果：

Dim xlApp as Excel.Application
Set xlApp = New Excel.Application             'Early Binding
Set xlApp = CreateObject(“Excel.Application”) 'Late Binding

Do not define (dim) inside a loop, however you can instantiate (Set) the same object as many times as you want. If you're using a global object (which is not recommended but sometimes handy) inside a control (button, etc) you may use something like this:

不要在循环内定义 (dim)，但是您可以根据需要多次实例化 (Set) 同一个对象。如果您在控件（按钮等）中使用全局对象（不推荐但有时很方便），您可以使用以下内容：

if xlApp is Nothing then set xlApp = CreateObject(“Excel.Application”)

When you're done don't forget to clean house!

完成后别忘了打扫房子！

wb1.Close False
set wb1 = Nothing 'do this once for each defined object, anything using New/Set

See also,

也可以看看，

• Difference between CreateObject("Excel.Application") .Workbooks.Open and just Workbooks.Open
• https://support.microsoft.com/en-us/kb/288902(GetObject and CreateObject behavior)

• CreateObject("Excel.Application") .Workbooks.Open 和 Workbooks.Open 之间的区别
• https://support.microsoft.com/en-us/kb/288902（GetObject和 CreateObject 行为）