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")...
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.
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.
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.
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.
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,
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With