VBA, How to open new excel instance-CodePudding

When I have don't have the excel APP open, the following error is thrown :

ActiveX Component can't create object

Steps to reproduce issue :

1 Open Outlook, ALT F11 And Insert the following sub :

Sub Test()
  Dim myXL As New Excel.Application
  Set myXL = GetObject(, "Excel.Application")
  Set wb = myXL.Workbooks.Open("MyPath\MyXL.xlsx")
End Sub

Close ALL your excel files
Run the sub Test from outlook.

The Error will be thrown on :

Set myXL = GetObject(, "Excel.Application")

How can I avoid this error ?

CodePudding user response：

A better option should be the next way, I think:

   Dim objexcel As Object

   On Error Resume Next 'firstly, try catching the existing open session, if any:
    Set objexcel = GetObject(, "Excel.Application")
    If err.Number <> 0 Then 'if  no any existing session, create a new one:
        err.Clear: Set objexcel = CreateObject("Excel.Application")
    End If
   On Error GoTo 0

Having a reference to 'Microsoft Excel ... Object library` you can declare

Dim objexcel As Excel.Application

and benefit of the intellisense suggestions...

It is also possible to find an Excel open session if you know the full name of a specific workbook open in it:

   Set objExcel = GetObject(ThisWorkbook.fullName).Application   
   Debug.Print objExcel.hwnd

Or even for a new workbook, open by a third party application, in a new session, as "Book1":

   Set objExcel = GetObject("Book1").Application
   Debug.Print objExcel.hwnd

If the respective application drops new workbooks (and opens them in the same session), naming them as "Book2", "Book3" and so on, a loop building the workbook name bay concatenation of "Book" root with the incremented variable can be used to get it.

CodePudding user response：

To avoid this bug I had to add to my code:

Dim objexcel As Object
Set objexcel = CreateObject("Excel.Application")