Skip to main content Skip to navigation

Bioinformatics & Digital Health RTP

Opening files in Excel VBA on a mac

There are a number of problems associated with creating a spreadsheet that works on Apple macs and Windows which can select and open files using a 'open file' dialog box

  • Difficulty in creating a fileopen dialog box for macs
  • Error 1004 returned when refreshing a queryTable after the filename has been changed
  • Writing vba that works on both OSs given the range of functions that are required on one, but generate fatal errors on the other.

The following code provides solutions to most of these problems, opening three text files using tableQueries based on the name of one of the files that is found using a find file dialog box. Some of these ideas were incorporated into Phil Brady's notes on porting a VBA application to a MAC

Browser specific fileopen dialog box

The following is the Windows version of the code for opening a dialog box for selecting a file

Function BrowseWin(mypath As String)
With Application.FileDialog(msoFileDialogOpen)
  .InitialFileName = GetDir(mypath)
  If .Show = -1 Then
    BrowseWin = .SelectedItems.Item(1)
  Else
    BrowseWin = "-"
  End If
  End With
End Function

And this is for macs:

The fileopen dialog box on a mac is best implemented with applescript. This script catches errors within the Apple script and returns the error number as text (which always begins with a '-') allowing the calling code to decide what to do. This catches 'errors' such as clicking the cancel button and opening with a directory that does not exist. I was not able to identify detailed error info if I caught the error in the VBA.

Function BrowseMac(mypath As String) As String
  sMacScript = "set applescript's text item delimiters to "","" " & vbNewLine & _
    "try " & vbNewLine & _
    "set theFiles to (choose file " & _
    "with prompt ""Please select a file or files"" default location alias """ & _
    mypath & """ multiple selections allowed true) as string" & vbNewLine & _
    "set applescript's text item delimiters to """" " & vbNewLine & _
    "on error errStr number errorNumber" & vbNewLine & _
    "return errorNumber " & vbNewLine & _
    "end try " & vbNewLine & _
    "return theFiles"
  BrowseMac = MacScript(sMacScript)
End Function

Enabling file access on Excel 2016 for macs

In the 2016+ versions of Excel, VBA operates in a sandbox which means that explicit permission has to be granted if any files are to be opened. This is not required if the file has been identified with a appleScript 'fileopen dialog box. For other files this is granted using the GrantAccessToMultipleFiles method. This is wrapped in a method because if it is encountered in code prior to Excel 2016, or on a mac then it throws an untrappable error

Function grantFileAccess(filePermissionCandidates)
  grantFileAccess = GrantAccessToMultipleFiles(filePermissionCandidates) 'returns true if access granted, false otherwise_
End Function

Getting the directory from a full path

This gets the directory from a full path

Public Function GetDir(file) As String
  If Application.OperatingSystem Like "*Mac*" Then
    div = ":"
  Else
    div = "\"
  End If
  x = InStrRev(file, div)
  If x = 0 Then
    GetDir = file
  Else
    GetDir = Left(file, x)
  End If
End Function

Browsing for a filename

This the function called by the 'Browse' button, which calls the OS specific browse subroutine and handles the responses for the mac filedialog, and then goes into common code to see whether the returned value should be placed in the spreadsheet and the query tables loaded using Load_Click

Sub BrowseRoot_Click()
  Dim startDir As String
  startDir = GetDir(Range("RootFile").Value)
  If Application.OperatingSystem Like "*Mac*" Then
    Path = BrowseMac(startDir)
    If Path = "-43" Or Path = "-1700" Then
' Path not found or similar, Try again with the root documents folder
      startDir = MacScript("return (path to documents folder) as String")
      Path = BrowseMac(startDir)
      End If
    Else
      Path = BrowseWin(startDir)
    End If
' If the user has not cancelled, or there is not an error then
' record the result
  If Left(Path, 1) <> "-" Then
    Range("Rootfile") = Path
    Load_Click
    End If
End Sub

Loading queryTables

This method is either called as part of the processing when 'Browse' is clicked, or can be called independently to refresh the files if the names have not changed. It makes use of three queryTables that have already been placed in the appropriate positions in the spreadsheets. The 'Browse' button selects the filename which ends _results.txt which is associated with the first queryTable. The other two filenames are derived from the _results.txt filenames Because they have not been explicietly selected by the user the mac security requires that the user confirms that it is OK to load them.

On a mac the code has to deal with the fact that the mac fileOpen dialog box uses path variables with a ':' separator bu the grant access function uses a '/' separator and must start with '/User...'

Sub Load_Click()
With Worksheets("Results")
  ResultPath = Range("RootFile").Value
  grantAccessReq = Application.OperatingSystem Like "*Mac*" And _
    Application.Version >= 15

' On a mac we need to convert the path returned from the file dialog box
' into a path using forward slashes based on /User because this is the
' form needed for the grantfile access functions
  If grantAccessReq Then
    Path = Replace(ResultPath, ":", "/")
    Path = Mid(Path, InStr(Path, "/User"))
  Else
    Path = ResultPath
  End If

' Paths to the other two files
  NormPath = Left(Path, InStr(Path, "_results")) & "norm.txt"
  ExprPath = Left(Path, InStr(Path, "_results")) & "expression.txt"

' On a mac with recent version of excel, request permission to load the files
  If grantAccessReq Then
' Create an array with file paths for which permissions are needed_
    filePermissionCandidates = Array(NormPath, ExprPath)
'Request Access from the user
    grantFileAccess (filePermissionCandidates)
    End If

' Change the identity of the files being loaded by the querytables and then load them
  With .Range("$A$1").QueryTable
    .Connection = "TEXT;" & ResultPath
    .Refresh BackgroundQuery:=False
    End With
  With .Range("$A$30").QueryTable
    .Connection = "TEXT;" & NormPath
    .Refresh BackgroundQuery:=False
     End With
   End With
  With Worksheets("Expression").Range("$A$1").QueryTable
    .Connection = "TEXT;" & ExprPath
    .Refresh BackgroundQuery:=False
    End With
End Sub