Word for Mac - Error while calling Excel.Application.Workbooks.Open runtime error -2146959355 (80080005)

Question

I am trying to open an Excel file in Word to read the values of some cells and do some magic to a new file in word.

A thing that I did many times using Microsoft Windows. Now that I'm unable to use Windows and I have to use my private computer I am facing some issues to use the files that I wrote on my Mac.

I am using the same version on both O.Ss.: Office Word 2016 but I am getting an error when I run the macro in my mac.

I am getting a

Runtime-Error with code -2146959355 (80080005).

The code is huge but I tried stripping down to find the error. Needless to say that I've reached the point that stripping more is equals to removing the macro. So, this is the code that is giving me the error. And I can't understand the reason.

Sub AutoOpen()

    Dim objExcel As New Excel.Application
    Dim exWb As Excel.Workbook
    Dim excelFile As String
    excelFile = ActiveDocument.Path & Application.PathSeparator & "file.xlsx"

    If (Dir(excelFile) <> "") Then
        Set exWb = objExcel.Workbooks.Open(excelFile)
        exWb.Close
    Else
        MsgBox ("File not found")
    End If
End Sub

The error is referring to the Set of exWb

This same code is working in Windows

What does Debug.Print excelFile return? Not sure, but possibly helpful. — BigBen
That variable contains the correct path: /Volumes/USB/file.xlsx(I used to check with MsgBox) — Gianmarco
nope. I've also tried removing the If-Else block... (and prior of that changing the file name and the Else part was called correctly) — Gianmarco
The location accessible to VBA is ~/Library/Group Containers/UBF8T346G9.Office/. (See macadmins.software/docs/UserContentIn2016.pdf) This is just one a number of issues you will face as Word for Mac is missing some functionality, e.g. building blocks. And additional libraries you may rely on in Windows will obvs be missing, e.g. XML, scripting. You may also find this link helpful: docs.microsoft.com/en-us/office/vba/api/overview/office-mac — Timothy Rylatt

Unknown Unknown · Accepted Answer · 2020-01-20T21:54:50

The problem is that on Mac, creating new COM objects via New or CreateObject is currently broken. Or at least, it is broken for some users with fresh installations of Mac OS and Office.

If you can pin your code down a little more, I think you will see that this error occurs in the first executable line after "Dim objExcel As New Excel.Application".

This problem has been reported to Microsoft on several occasions over several years, but there has been no obvious response. My guess is that either their dev. team has made historic changes to their Mac OS configuration that they have forgotten about that means they don't encounter this problem, or that the people who had expertise in working with COM on Mac have left and Microsoft is stalling until they can recover some relevant expertise.

There does not really seem to be any obvious Microsoft documentation about it.

Update.

In v. 16.34 of Mac Word, things seem to have changed a bit.

GetObject is still useless.

But if you do

Dim app as Object
Set app = CreateObject("Excel.Application")

app is no longer Nothing. Unfortunately, if you do

typename(app)

you get "WorkBook" which means that the code is still not returning the right kind of object.

But here, I can at least now do something like

If UCase(Typename(app)) = "WORKBOOK" Then
  Set app = App.Application
End If

Word for Mac - Error while calling Excel.Application.Workbooks.Open runtime error -2146959355 (80080005)

2 Answers