How to open a workbook from Excel 16 with VBA on M

Question

I want to open a simple workbook from a little macro with the VBA of Excel 16 on a MAC 10.10 but I can't.

I have my macro:

    Sub Test()
        Call Workbooks.Open("Classeur1.xlsm")
    End Sub

With Office 11, this function works fine but I have an error with Office 16:

Run-time error '1004':
Application-defined or object-defined error

Do you have any idea to fix it please?

Answer 1

wbName = "FILENAME"
If CInt(Split(Application.Version, ".")(0)) >= 15 Then 'excel 2016 support
    wbName = Replace(wbName, ":", "/")
    wbName = Replace(wbName, "Macintosh HD", "", Count:=1)
End If

Check Excel Version and then replace characters accordingly. I'm not sure why, but my Excel 2016 on macOS is of version 15.xx

Answer 2

I had a macro using Workbooks.Open that worked on Excel 2011 and doesn't work on 2016. I got it to work by changing the format of the file path. Now the path separators are not colon but they are slash.

This works in Excel 2011:

path:..:file

This works in Excel 2016:

path/../file

Answer 3

The problem is Excel 2016 for Mac has a strange "default" directory that it works in. Mine starts up in /Users/xxxxx/Library/Containers/com.microsoft.Excel/Data. You therefore need the fully qualified path to your workbook. Example:-

Sub Test()
   Call Workbooks.Open("/Users/damien/Documents/Classeur1.xlsm")
End Sub

Just a side note, you do not need to use the Call statement. You could simply do this:-

Sub Test()
   Workbooks.Open "/Users/damien/Documents/Classeur1.xlsm"
End Sub