How to open an Excel workbook that ends with a specific string using Applescript I am new to Applescript and I am trying to open a xlsx file that ends like DK.xlsx. Tried using the following code, but it doesn't work. What am I doing wrong here? Please help.

set sourcefilepath to "Macintosh HD:Users:nprabu:Documents:Work:Project:Q1 2018:"

set sourcefile to name of every file of sourcefilepath whose name ends with "DK.xlsx"

tell application "Microsoft Excel"

    open workbook workbook file name sourcefile

end tell
Because you're new to AppleScript and because the script you gave had myriad rudimentary errors, this answer is quite long as I thought it appropriate to explain how to correct the script and why the errors arose in the first place.

However, for the benefit of any readers for whom this would be TL;DR, here are two three versions of the script in their complete form, each differing in how file(s) are sought and the command sent to Excel in order to open it/them.

Disclaimer:  I haven't been able to fully test the scripts, as I do not own a copy of Microsoft Excel.  I have formulated these snippets based on an online version of the AppleScript dictionary.  If you encounter any script errors, your feedback will help me correct them.

① This version uses the open workbook command as per your original script, which only accepts a single file reference.  Therefore, the search conducted will return whichever single file it finds first with the appropriate filename ending.  Note: See below for why the Excel command might not work, and why I have provided a second version of the script should this be the case:

    set sourcefilepath to "Macintosh HD:Users:nprabu:Documents:Work:Project:Q1 2018:"

    try
        tell application "Finder" to set sourcefile to ¬
            (the first file in the folder sourcefilepath whose ¬
                name ends with "DK.xlsx") as text
    on error
        return display notification ¬
            "No filenames ending in \"DK.xlsx\"" with title "File Not Found"
    end try

    tell application "Microsoft Excel" to ¬
        open workbook workbook file name sourcefile


② This version uses the standard open command that accepts one or more file or alias objects.  Use this version if you need to open multiple files easily or the above version of the script fails to open the file:

    set sourcefilepath to "Macintosh HD:Users:nprabu:Documents:Work:Project:Q1 2018:"

    tell application "Finder" to set sourcefiles to ¬
        (every file in the folder sourcefilepath whose ¬
            name ends with "DK.xlsx") as alias list

    if sourcefiles is {} then return display notification ¬
        "No filenames ending in \"DK.xlsx\"" with title "File Not Found"

    tell application "Microsoft Excel" to open the sourcefiles


③ Addendum: Emergency Script:

Thanks to @user3439894 for testing these scripts and reporting some odd behaviour from Microsoft Excel regarding how it responds to the standard open command (see the comments thread attached to this answer).

Essentially, it behaves as expected if Excel is not already running, and will open multiple files (in the background).  However, if Excel is already open, it will only open one file out of a given file list, and the one it chooses to open is seemingly random.

Therefore, I'm adding this third version of the script as a less graceful approach to opening multiple files, but one that will surely cope with Excel's personality issues:

    set sourcefilepath to "Macintosh HD:Users:nprabu:Documents:Work:Project:Q1 2018:"

    tell application "Finder" to set sourcefiles to ¬
        (every file in the folder sourcefilepath whose ¬
            name ends with "DK.xlsx") as alias list

    if sourcefiles is {} then return display notification ¬
        "No filenames ending in \"DK.xlsx\"" with title "File Not Found"

    tell application "Microsoft Excel"
        activate -- Bring Excel into focus

        -- This loops through the list of files and
        -- opens them one at a time
        repeat with sourcefile in sourcefiles
            open the sourcefile
        end repeat
    end tell


You can, of course, try and replace open the sourcefile with open workbook workbook file name (sourcefile as text), should that command be working in your version, but otherwise you'll be fine just using open. 



A Detailed Breakdown

Retrieving your file(s)

The first few errors in your original script arise from this line:

set sourcefile to name of every file of sourcefilepath ¬
    whose name ends with "DK.xlsx"


(I have split the code over two lines for readability; this doesn't affect how AppleScript executes the commands.)

▸ Firstly, this command needs to be sent to an application that can handle file and folder objects.  The two options you have are Finder and System Events.  Finder is suitable and most commonly used for file commands.  To send a command to Finder, you simply add a tell clause at the start of the line:

tell application "Finder" to set sourcefile to ¬
    name of every file of folder sourcefilepath ¬
    whose name ends with "DK.xlsx"


You also need to specify that sourcefilepath is a folder object, which I've done by adding the object specifier folder in front of the variable.

▸ Your question as it is stated, together with your choice of AppleScript variable name, implies you are wanting a single file to be opened.  However, the command we've just been looking at tells Finder to get every file, which will always return a list of items (although it may be a list containing only one item), rather than a single item.  If there are multiple files whose names end with "DK.xlsx", then all of them will be returned in this list.  This will cause problems when it comes to opening the files using Excel's open workbook command, which can only handle one file per command statement.

If you are reasonably sure there's only one filename that ends in "DK.xlsx", then you can change every to first:

tell application "Finder" to set sourcefile to ¬
    the name of the first file of folder sourcefilepath ¬
    whose name ends with "DK.xlsx"


(I prefixed some terms with the to make it more readable; AppleScript happily copes with or without the being present.)

The other consideration is that open workbook requires that the full path to the file be specified.  However, by requesting that Finder returns just the name of the file(s), you will only be given the filenames without the path.  In order to retrieve the full path, remove the request for the file's name property and instead retrieve the file object, which you can then coerce into text:

tell application "Finder" to set sourcefile to ¬
    (the first file of folder sourcefilepath ¬
    whose name ends with "DK.xlsx") as text


▸ If it turns out that you do, indeed, wish to retrieve multiple files that end in the suffix "DK", then its best to coerce the list of file objects into an alias list (which is a list of aliases).  This is so we can use the standard open command instead of open workbook, as the former can accept a list of multiple file aliases, enabling you to open many files with one single command.

tell application "Finder" to set sourcefile to ¬
    (every file of folder sourcefilepath ¬
    whose name ends with "DK.xlsx") as alias list


Opening the file(s) in Excel

You didn't mention which version of Microsoft Excel you're using, which may turn out to be pertinent information.  This forum question from 2009 is from a user who reports running into trouble when using the open workbook command.

▸ BUT...  Let's assume for now that the command will work with your version, and that you only wish to open a single file.  The syntax as you've got it is all correct, and the adjustment I mentioned above to retrieve the full file path will prevent the open workbook command from throwing an error because of a bad file reference.  One stylistic note: you can write the tell statement and the open workbook command on a single line, then delete the end tell line:

tell application "Microsoft Excel" to open workbook workbook file name sourcefile


No one form is better than the other, so it's personal choice.

▸ The forum post I mentioned above that reported a malfunctioning open workbook command also cited the same problem when using the standard open command.  However, this Stack Overflow post provides the likely cause, and reassures me that this open command will work where the open workbook command may not.

Basically, in the same way that the Finder application needs a folder object to be specified explicitly as being a folder, Excel needs the file object to be specified explicitly as being a file (or alias):

tell application "Microsoft Excel" to open file sourcefile


▸ The benefit of this open command is that it can accept lists of aliases to open many files at once.  Therefore, if you decide to keep the version of the Finder command that retrieves multiple files, make sure you coerce it to an alias list, which you can then use like so:

tell application "Microsoft Excel" to open sourcefile


Note here that the file object specifier is absent when opening multiple files, because the variable sourcefile is already of the correct type, namely alias list.

Closing comments

The final versions of the script I supplied at the beginning are slightly different in how I chose to implement the changes I've detailed here.  I also felt it wise to include some basic error-handling in case a file could not be found, which would throw an error and might lead you to wonder if the code was faulty.

If you find that the file successfully opens in Excel, but that it remains in the background concealed behind other applications, you can add this command to the script to bring Excel into the foreground and make it the focussed application:

tell application "Microsoft Excel" to activate


or—combining it with the existing Excel commands:

tell application "Microsoft Excel"
    activate
    open file sourcefile
end tell




If you need any of this clarified or if you have any further minor questions relating to this, leave a comment and I'll get back to you.  If you find this helpful or if it solves your problem, do consider selecting this answer or any other that proves more useful so other forum visitors know this issue has been addressed and can benefit from it if they have a similar problem.