Trouble opening an Excel file in a Word macro on Mac

[Bill Hamilton]

Please can some kind soul tell me why the Word macro code below works perfectly in Windows and fails when run on a Mac?

Sub checkit()
'
'   WORKS SEAMLESSLY ON WINDOWS, FAILS AT THE 'OPEN' STATEMENT ON MAC.
'   'Object doesn't suport this property or method'
'
Dim xlApp As Object
Dim xlBook As Object
Dim stVar As String
Dim strWorkbookName As String
#If Mac Then
  strWorkbookName = "/Users/billh/Dropbox/CNTSA/mactesting/testrunningawordmacrofromExcel.xlsm"
#Else
  strWorkbookName = "C:\Users\billh\Dropbox\CNTSA\mactesting\testrunningawordmacrofromExcel.xlsm"
#End If
On Error GoTo err_exit
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(FileName:=strWorkbookName)   '<<< Fails on Mac
On Error GoTo 0
xlApp.Visible = False
MsgBox "A1=" & xlBook.Sheets("Sheet1").Range("A1")     ' just some text to show it's working.
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
Exit Sub

err_exit:
  MsgBox Err.Number & vbLf & Err.Description
  Err.Clear
  xlApp.Quit ' this also fails on Mac with 'Object doesn't support this property or method' if Set xlBook failed
  Set xlBook = Nothing
  Set xlApp = Nothing
End Sub

As it says above, the failure occurs at the 'Set xlBook = xlApp.Workbooks.Open(FileName:=strWorkbookName)' line and gives Error 438, 'Object doesn't support this property or method'. I get the same in the error handler too when trying to shut it down. I believe I'm doing this pretty-much the standard way given in many examples on forums etc. The filepath names are correct and refer to the same file on Dropbox and are pasted in from File Explorer and Finder as appropriate. The Word file containing this macro is also on Dropbox and when run under Windows it runs through with no errors.

The CreateObject works and a 'Book1' Excel file is shown. I'm doing 'CreateObject' rather than 'GetObject' because when the real-life process which will be developed from this trial finishes I want to shut down only this instance, not all the other instances:there will be at least one. (And please don't get sidetracked about that - it's not the issue here.)

I'm converting a Windows system amounting to thousands of lines of code in several Excel workbooks and one Word file to work on Macs and this is a simple test routine to check out the process of opening an Excel file from a macro in the Word file. I'm going to have a bit of a problem if I can't get this to work. I hope it's something simple I'm just not seeing, but given that almost 100% of my VBA experience is Excel-based, then there might well be a Word VBA wrinkle I don't know about.

Any clues about why the xlApp.Quit also gives an error?

Running Office for Mac 365 (Word 16.37) and Mac OS Catalina 10.15.5.

 

[Bill Hamilton]

No takers so I did more investigation myself and have found the solution, viz:

Instead of

Set xlBook = xlApp.Workbooks.Open(FileName:=strWorkbookName)

I should be using

Set xlBook = xlApp.Application.Workbooks.Open(FileName:=strWorkbookName)

The critical bit is the introduction of '.Application'.

It works with the 'Quit' as well. i.e.

xlApp.Application.Quit

does as intended.

And, if you want to minimise the XL window, use

xlApp.Application.WindowState = wdWindowStateMinimize

Case closed. I hope this might help someone in the future.

PS Apologies to the moderators for posting in the wrong forum - I'm too used to writing in Excel VBA I forgot the original one was just for that.

 

[RoryA]

Thanks for following up with your solution!