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