How to Save an Excel Doc with AppleScript with Today’s Date

Excel is one of the most powerful tools in the business world. The problem is that most people have only scratched the surface of its analytical and reporting power. For some, the reason why we cannot accomplish more with Excel is because of the tedious processes that are involved in importing, exporting, and formatting our sets of data… so how do we fix this? Automation!

If you are using a Mac the best way to automate excel in my opinion is not VBA, it is AppleScript. Using Applescript we can import, export, and format data across almost all of our applications. The following tutorial will teach you how to create and then save an Excel doc with today’s date.

TIP: The fastest way to program in AppleScript that I have found is to create a seperate AppleScript or Text file to copy and paste all useful code. I call this my Library file. Instead of going back and forth between scripts that I’ve made to try and remember what code to use, I just reference this file that I keep organized in a way that makes sense to me. If your file gets too large you can use the Command+f hot key to quickly search your document.

NOTE: If you experience any errors please scroll to the bottom of the page, this will explain the most common error you will receive.

So, lets get started…

How to Make a New Document in Excel Using AppleScript

The first thing you do is create a statement that tells AppleScript to use Excel. This is done with the following:

tell application "Microsoft Excel"

end tell

All of the actions you want Excel to perform must be between the Tell and End tell for them to executed.

For example if you want make a new document you would use the following code:

tell application "Microsoft Excel"

make new document

end tell

How to Save a Document in Excel Using AppleScript

Now that you have a document, you will probably want to name it and save it. If you want to save and name a document in Excel you would do the following code:

tell application "Microsoft Excel"

make new document

set workbookName to ("My First Excel AppleScript.xls") as string

 set destinationPath to (path to desktop as text) & workbookName

save active workbook in destinationPath

end tell

This will save the document to your desktop with the name My First Excel AppleScript as an .xls if you want it to be an .xlsx you can simply change the extension. That would look like this:

set workbookName to ("My First Excel AppleScript.xlsx") as string

How to Save a Document in Excel Using AppleScript with Today’s Date

You can’t have more than one of the same file on your desktop, so if you want to keep replacing the file you would leave the code alone. However, if you want to make and keep a new file every day you could do the following:

tell application "Microsoft Excel"

make new document

 set today to (current date)

set workbookName to ("My First Excel AppleScript " & month of today & " " & day of today & ".xls") as string

 set destinationPath to (path to desktop as text) & workbookName

save active workbook in destinationPath

end tell

This will create a file on your desktop that looks like this:

**** NOTE: If you already have the document open this will not work. This is because two workbooks with the same name cannot be open in Excel. So… if you are testing this out repeatedly you must delete the file from your desktop and close the workbook with the name of the file before you hit run.

The error you will receive looks like this:

“Microsoft Excel got an error: active workbook doesn’t understand the save message.”

***