Using GrantAccessToMultipleFiles prevents Sub running on Windows

Question

I'm writing an Excel macro to automatically populate and save a number of Word docs. It's working great on Office for Mac 2016, but I want to ensure cross-compatibility with Windows.

To reduce the user burden of granting individual file permissions due to Mac Office sandboxing I've implemented the GrantAccessToMultipleFiles command, using ramitarora's helpful solution.

Unfortunately, because GrantAccessToMultipleFiles is a Mac-specific command, I'm running into a Compile error: "Sub or Function not defined" as soon as I try to run this on Windows. Is there a solution to this? Because it's a compile error I can't just run over it using "On Error Resume Next".

For reference, relevant code is below:

'Operating System check: if pos=0, user is on OSX
Dim pos As Integer
pos = InStr(Application.OperatingSystem, "Windows")

'If on OSX ask for file permissions in bulk
If pos = 0 Then
    Dim fileAccessGranted As Boolean
    Dim filePermissionCandidates
    Dim filePathsArray As String

    filePathsArray = filePaths(0)

    For i = 0 To studentNum
        filePathsArray = filePathsArray & ", " & filePaths(i + 1)
    Next i

    'Create an array with file paths for which permissions are needed
    filePermissionCandidates = Array(filePathsArray)

    'Request access from user
    fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates)
End If

Thanks.

I'm not sure about your specific problem but I had a similar issue when I was trying to have a script macro that would have an open file dialog on both systems with only one codebase. I solved it eventually. The main difference I see between yours a mine is that I discovered I had to separate the code into different Functions and then have a picker function to decide which one. It doesn't look like your OSX specific code has been isolated into it's own function http://stackoverflow.com/questions/21035212/excel-vb-open-file-osx-and-windows — Flatlyn, Apr 07 '16 at 02:40
Ah, duh, that's the solution! I'll write a separate sub and call it only on OSX. No compile error on Windows since it won't be called. Thanks for the suggestion. — clwatkins, Apr 07 '16 at 02:57
I've added that as an answer if it worked so you can mark it as correct to help others in the future. — Flatlyn, Apr 07 '16 at 03:00

Kate · Answer 1 · 2016-09-14T19:21:51.190

4

Specifically, the code to test for Mac 2016 or not should look like:

#If MAC_OFFICE_VERSION >= 15 Then
    ' Do thing for Office for Mac 2016 (now at version 15.26)
#Else
    'Do thing for Windows
#End If

This is from the Office Dev Center documentation here https://dev.office.com/blogs/VBA-improvements-in-Office-2016

I've tested this to run my macro on both OSX and Windows operating systems and it works.

edited Sep 14 '16 at 19:21

answered Sep 14 '16 at 19:15

Kate

133
2
17

The answer below should be used e.g. use the "Mac" conditional compiler constant. You could then nest the MAC_OFFICE_VERSION to separate Office:mac 2011 from Office:mac 2016 – Jamie Garroch - MVP Mar 03 '21 at 18:55

score 3 · Answer 2 · answered Apr 07 '16 at 07:09

3

You can also use conditional compilation to separate Mac vs Windows code:

#If Mac then
'your Mac code
#Else
'Windows code
#End If

answered Apr 07 '16 at 07:09

Rory

32,730
5
32
35

This is the only correct answer to prevent Windows/macOS specific code from not being compiled on an invalid platform. You can also use "#If Not -Mac... #End If" to run Windows-only code. – Jamie Garroch - MVP Mar 03 '21 at 18:54

score 0 · Accepted Answer · answered Apr 07 '16 at 02:59

0

You have to separate your OSX and Windows code into different functions and then only call the relevant function. That way the function with the specific code is never called on the wrong system and there is no issue.

answered Apr 07 '16 at 02:59

Flatlyn

2,040
6
40
69

Using GrantAccessToMultipleFiles prevents Sub running on Windows

3 Answers3