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.
One thought on “VBA Mac File Dialog Selecting Multiple Files”