Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

"New" Excel.Application vs Excel.Application

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")...
like image 970
ProtoVB Avatar asked Nov 06 '13 19:11

ProtoVB


People also ask

Does Excel application scope create new file?

Create New FileBy default selected; If you give a file path and that is not found, then it will create the same and perform operations on it. If the checkbox is not selected, then an exception is thrown if the workbook cannot be found at the specified path.

What is application in Excel VBA?

Visual Basic for Applications is a computer programming language developed and owned by Microsoft. With VBA you can create macros to automate repetitive word- and data-processing functions, and generate custom forms, graphs, and reports. VBA functions within MS Office applications; it is not a stand-alone product.

What is the Excel object model?

The Excel Application object represents the Excel application itself. The Application object exposes a great deal of information about the running application, the options applied to that instance, and the current user objects open within the instance.

Which of the following can be used to create an object for Excel?

To create an object we need to declare any variable with an object data type and then we use the set statement to reference it. After that, we use to create object to create an object with reference to the application we are referring to.


1 Answers

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)
like image 200
Jason K. Avatar answered Oct 29 '22 06:10

Jason K.