Category: vba

Previously I have written about selecting a file from a Microsoft VBA using a file dialog window on a Mac. This approach used an Applescript and selected a single file. This approach was limited to selecting a single file located on the local hard drive. Recently I had the need to updated this approach to allow selecting multiple files that are located on any drive/cloud.

The ability to select multiple files in the dialog window required a small change to the Applescript. The text “multiple selections allowed false” changed “false” to “true.” This allows selecting multiple files that the Applescript returns.

mypath = MacScript("return (path to documents folder) 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"

inFiles = MacScript(sMacScript)     ' get filenames

When the MacScript is run it returns a string of full path filenames separated by “,” unless no files are selected then “-128” is returned. The string of filenames are easily split into a array file filenames for VBA use. For the VBA to access each file two changes are required to each filename. First the Mac directory separator “:” is replaced with “/” symbol. The second change is to add a prefix “/Volumes/” to a full path filename. This allows the use of different drives/cloud files. In my previous post I removed “Macintosh HD” for the path name.

filenameSplit = Split(inFiles, ",")
For N = LBound(filenameSplit) To UBound(filenameSplit)
    selectedFile = "/Volumes/" & Replace(filenameSplit(N), ":", "/")

    <... DO WORK ...>

Next

This updated Applescript and code was used in a Word VBA but should work well with Excel on the Mac.