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

Answer 1

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)