AppleScriptTask to Select Files in Excel VBA

Question

I have an Excel macro that requires the user to select two "csv" files and it must run on both MacOS and Windows. I have researched how to use AppleScript for this purpose on the Mac but it is not working as expected. Here is my script, "ChooseFiles.scpt" as I tested it in the script editor:

SelectFiles("csv")

on SelectFiles(fileType)
    set fileNames to choose file with prompt "Please select before and after treatment csv files:" of type {fileType} with multiple selections allowed
    return fileNames
end SelectFiles

The result for selecting two files is valid, although I don't recognize the format:

Result:
{alias "Untitled:Users:billw:Documents:Testing:CSV:01_06_2024_RowSur385589.csv", 
alias "Untitled:Users:billw:Documents:Testing:CSV:11_04_2023_RowSur1823781.csv"}

This is how I use the script in my VBA code. SelectedCSV is not declared with a Dim statement.

SelectedCSV = AppleScriptTask("ChooseFiles.scpt", "SelectFiles", "csv")

If I select only one file, SelectedCSV contains a valid file name of type Variant/String:

File:///Users/billw/Documents/Testing/CSV/01_06_2024_RowSur385589.csv

However when I select two files, SelectedCSV contains only an empty string. The list of two files from testing the script appears to be some sort of array. Please tell me what I need to fix in in the script itself or the Excel VBA code so I get the two files instead of the empty string.

Answer 1

There are 2 issues:

1. there is an hidden conversion from Applescript to VBA when you are sending back a single file path. Applescript sends a file path with Finder format which means that each folder level is separate from next with semi-column character ":", but when it comes back to VBA it is converted to Unix file path using / as folder separator. This conversion only occurs when system recognize the varaible as being a valid file path
2. when there are multiple files selected, Applescript sends back a list, not a string, but VBA instruction ApplescriptTask only gets back a string. As result, list in not view as string and also no conversion is done !

To sort this out, you need 2 steps: you need to force AppleScript to send back a string (not a list) and to force Applescript to explicitely convert Finder file paths to Unix file path. To convert list to string, I just concatenate file path separater but ascii char 10. And when it comes back to VBA, you must use Split to convert that string to an array.

Here is the AppleSrcipt:

on SelectFiles(fileType)
    set fileNames to choose file with prompt "Please select before and after treatment csv files:" of type {fileType} with multiple selections allowed
    set myList to {}
    repeat with aFile in fileNames
        set end of myList to POSIX path of aFile
    end repeat
    set text item delimiters to {ASCII character 10}
    return myList as text
end SelectFiles

Here is the VBA:

Sub Macro()
 myscriptresult = AppleScriptTask("selectcvs.scpt", "SelectFiles", "csv")
 myArray = Split(myscriptresult, Chr(10))
 End Sub

Warning: each item of the array is a Unix path to the file, but only starting from folder /Users/.... in VBA you must add "File://" to get full path.