How should I open an Excel Workbook from MS Word VBA

Question

There are other variants of this question on StackOverflow, but they're all from several years ago and the answers from them seem to reference older versions of VBA... or at least they give me errors I've been unable to resolve otherwise. Here's my code, written through the MS Visual Basic Editor, Version 7.1, running on Mac OS Mojave 10.14.5. Word & Excel are both version 16.26. Each Sub below is a different attempt. I'm new to VBA, so trying lots of things that I don't quite understand. Each Sub gives a different error:

Sub OpenExcelFile1()
    Dim oExcel As Excel.Application
    Dim oWB As Excel.Workbook
    Dim granted as Boolean
    Debug.Print "a"

    Set oExcel = New Excel.Application
    granted = GrantAccessToMultipleFiles(Array("/users/sarah/workspace/report-writer/"))
    Debug.Print "b"
    Set oWB = oExcel.Workbooks.Open("/Users/sarah/workspace/report-writer/test.xlsx", ReadOnly:=True)
    Debug.Print "c"
End Sub

OpenExcelFile1 above hangs for about a minute between "a" and "b", then gives Run-time error '-2146959355 (80080005)': Automation error

Sub OpenExcelFile2()
    Dim oWB As Excel.Workbook
    Dim granted As Boolean
    granted = GrantAccessToMultipleFiles(Array("/Users/sarah/workspace/report-writer/"))
    Debug.Print "d"

    Set oWB = Excel.Workbooks.Open("/Users/sarah/workspace/report-writer/test.xlsx", ReadOnly:=True)
    Debug.Print "e"
End Sub

OpenExcelFile2 above gets to "d", then gives Run-time error '430': Class does not support Automation or does not support expected interface.

Sub OpenExcelFile3()
    Dim oWB As Excel.Workbook
    Dim granted As Boolean
    granted = GrantAccessToMultipleFiles(Array("/Users/sarah/workspace/report-writer/"))
    Debug.Print "f"

    Set oWB = Excel.Application.Workbooks.Open("/Users/sarah/workspace/report-writer/test.xlsx", ReadOnly:=True)
    Debug.Print "g"
End Sub

OpenExcelFile3 gets to "f", then prints the same error as OpenExcelFile2

Sub OpenExcelFile4()
    Dim oWB As Excel.Workbook
    Dim granted As Boolean
    granted = GrantAccessToMultipleFiles(Array("/Users/sarah/workspace/report-writer/"))
    Debug.Print "h"
    Set oWB = Excel.Application.WorkbookOpen("/Users/sarah/workspace/report-writer/test.xlsx", ReadOnly:=True)
    Debug.Print "i"
End Sub

OpenExcelFile4 gets to "h", then prints the same error as OpenExcelFile2 and OpenExcelFile3.

The referenced test.xlsx exists at the specified location, with 777 permissions.

I have installed (checked? used? referenced?) the following six references: * Visual Basic For Applications * Microsoft Word 16.0 Object Library * Microsoft Forms 2.0 Object Library * Microsoft Office 16.0 Object Library * Microsoft Excel 16.0 Object Library * Microsoft Visual Basic for Applications Extensibility 5.3

What am I missing? Thank you.

Which version of Office? FWIW there appear to be major problems with "automation" between Office applications on more recent versions for the Mac. See stackoverflow.com/a/52623911 — Cindy Meister
Word and Excel are both version 16.26; I assume that's the Office version too. I saw some notes about rolling back to earlier versions of Office, and bugs in one of the recent versions, but everything I saw was from over a year ago... Surely MS patches their major bugs more often than that.... right? please? — Sarah Messer
LOL "Surely MS patches their major bugs more often than that.... right? please?" — SlowLearner
FWIW, I've also tried analogous code in Excel VBA to open Word docs. The errors are different, but the end result (failure) is the same: SNAFU. — Sarah Messer

Sarah Messer Sarah Messer · Accepted Answer · 2019-07-01T16:07:39

Found a solution which I dislike:

Use Windows, not Mac.
Remove the GrantAccessToMultipleFiles lines, since this function is only present in the Mac version of VBA
Remove the reference to "Microsoft Forms 2.0 Object Library" (I can't find it on Windows. It's probably innocuous if it's on someone else's machine.)

Am posting this as a "solution" because with these modifications because they let me do the basic open-excel-file operation for the methods OpenExcelFile1, OpenExcelFile2, and OpenExcelFile3. The last one, OpenExcelFile4 did not work. It appears the Application.WorkbookOpen() is not present in Windows either. This was tested and working for Windows 10 Pro v 1903 build 18362.175 with MS VBA version 7.1 and Office version 16.

I don't like the solution because the specific project I'm researching this for is Mac-primary. If others have a Mac-friendly solution, I'd love to see it.

How should I open an Excel Workbook from MS Word VBA

2 Answers