"New" Excel.Application vs Excel.Application

Question

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.

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?

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...

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.

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")...

Solution 1

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.

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.

Solution 2

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

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

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:

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

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

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:

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)