2
votes

I have Word 2016 VBA code to read data in an Excel document.

This works on Windows platforms (Windows 7 and Windows 10).

On Mac it fails on the CreateObject("Excel.Application") instruction.

run-time error '-2146959355 (80080005)'
Automation error

Is this a problem in Office 2016 for Mac (currently using version 16.23), or is this a problem in my "environment" and the way Office is installed on my Mac?

It used to work and stopped working with an Office 2016 for Mac update. I can not remember which version caused the issue. Since almost two years, I check this code with every Office update and it has always failed.
I tried running a Windows 10 virtual machine with Parallels Desktop, and in this virtual Windows environment, the code works. I need to run it "natively".

The following code reproduces the problem:

Sub MyTestOfCreateObject()
    Dim xlapp As Object
    Set xlapp = CreateObject("Excel.Application")
    xlapp.Visible = True
    xlapp.Quit
    Set xlapp = Nothing
End Sub

I tried replacing CreateObject("Excel.Application") with Set xlapp = New Excel.Application. I got the same error message.

I expect xlapp to be used as an Excel object.

2

2 Answers

1
votes

After a lot of trial and error in Mac Word VBA, the following works for me. One important thing I discovered was that CreateObject("Excel.Application") returns a Excel.Workbook object rather than an Excel.Application object (and GetObject(...) does the same). This seems to be a change from earlier versions of Excel for Mac. Another was that the launch behavior was somewhat inconsistent, so I added retries with a delay.

    Sub Test1()
        Dim objExcelWkbk As Object
    
        On Error Resume Next
        
        ' GetObject() and CreateObject() behave inconsistently... retry a few times
        Dim iRetry As Integer
        iRetry = 1
        Do While iRetry <= 3
            ' I also tried:
            '     Set objExcelWkbk = New Excel.Application
            ' Although this *will* launch Excel, it always gives an "Class does not support Automation or does not support expected interface" error
            
            ' If Excel is already open then assign to the variable
            ' For Excel, an Excel.Application.Workbook object is returned when successful (rather than Excel.Application)
            Set objExcelWkbk = GetObject(, "Excel.Application")
            CheckAndReportError ("Try#" & iRetry & " (A) GetObject()")
            Wait 2
            If Not objExcelWkbk Is Nothing Then
                Exit Do
            End If
            
            ' If Excel was not previously open then object variable will be empty
            ' so create the object
            Set objExcelWkbk = CreateObject("Excel.Application")
            CheckAndReportError ("Try#" & iRetry & " (B) CreateObject()")
            Wait 2
            If Not objExcelWkbk Is Nothing Then
                Exit Do
            End If
            
            iRetry = iRetry + 1
        Loop
        Err.Clear
        
        If objExcelWkbk Is Nothing Then
            Debug.Print "Failed to create Excel object"
        Else
            Debug.Print "Excel version " & objExcelWkbk.Application.Version & "; OS " & objExcelWkbk.Application.OperatingSystem
            CheckAndReportError ("(C) objExcelWkbk.Application.Version")
            objExcelWkbk.Application.Quit
            CheckAndReportError ("(D) objExcelWkbk.Application.Quit()")
        End If
        
        Set objExcelWkbk = Nothing
    End Sub
    
    Sub CheckAndReportError(Label As String)
        If Err.Number = 0 Then
            Debug.Print Label & " no error"
            Exit Sub
        End If
        Debug.Print Label & " error #" & Err.Number & ": " & Err.Description
        Err.Clear
    End Sub
    
    Sub Wait(ByVal Seconds As Single)
        Dim CurrentTimer As Variant
        WaitUntil = Timer + Seconds
        Do While Timer < WaitUntil
            DoEvents
        Loop
    End Sub
    
0
votes

did you get anywhere with this? I know you posted the question sometime ago, but I'm getting the same problem trying to open Excel from Powerpoint on Mac in the same way. It worked on previous versions but fails in Office 2016. I've found references to people reporting it as a problem but no resolutions, although I have seen someone suggesting that Excel be opened with Applescript. Given the number of references to the Application object in the macro, I'm probably looking at a complete rewrite.