Another week, another update for the fantasy football version of the Cheatsheet Compiler & Draft Buddy, and this a great one for Mac Excel users.

Last year, I removed the ability to play sound files in the CC/DB, as part of the big overhaul combining the old Cheatsheet Compiler, and the old Draft Buddy, into a single workbook file. The sounds were fun, but no doubt a frill, so they didn’t make the cut in the 2011 version. Plus, the sounds never worked on a Mac (integrated through Excel), and I could never figure out how to make it happen. I think I’ve finally cracked that nut, using a combination of Visual Basic in the macros, and AppleScript.

I’ve looked at AppleScript before, but never made much progress using it. The purpose at the time was to try to solve the “macros don’t work in Mac Office 2008” issue. We still needed VBA, or something, to trigger the AppleScript to interact with the CC/DB. That wasn’t possible in Mac Office 2008, but with the latest version of Office for Mac, plus the pre-2008 versions of course, the macros do work, and now using AppleScript we can play the sound files.

Here is the AppleScript I constructed to play a .wav file:

on run argv

set audioFile to ((path to me as string) & "Contents:Resources:" & argv & ".wav") as alias

tell application "Finder" to set theName to (name of audioFile)

tell application "QuickTime Player"
activate
open audioFile
play document theName
set soundLength to duration of document theName as integer
delay soundLength
close document theName
end tell

end run

The AppleScript is saved as a script bundle, which provides the ability to bundle the .wav files together with the script. The first line is asking for an argument indicating what sound file to play. If you remove the first and last line, plus change the part:

"Contents:Resources:" & argv & ".wav"

to something like:

"Contents:Resources:boo.wav"

(the name of one of my sound files is “boo.wav”), and run the AppleScript from the Editor, then you should find it plays the sound file. Make sure you’ve saved the script and bundled your sound file in the right location under Resources.

Now we need a Visual Basic macro inside the Excel file to run this AppleScript. This is just a testing file, and not the final version for the CC/DB, because I still need to add options for Excel to decide if it is on Windows or a Mac, and the ability for the user to choose the sound file they want.

For testing, I hardcoded to play the file “boo.wav”:

Sub Button1_Click()

Dim soundFile As String

soundFile = "boo"

Dim scriptToRun As String
Dim posixcmd As String

scriptToRun = ""
posixcmd = ThisWorkbook.Path
posixcmd = posixcmd & ":"

scriptToRun = "set thePath to " & """" & posixcmd & """" & Chr(13)
scriptToRun = scriptToRun & "set appPath to thePath as alias" & Chr(13)
scriptToRun = scriptToRun & "tell application " & Chr(34) & "Finder" & Chr(34) & Chr(13)
scriptToRun = scriptToRun & "set appUserPath to quoted form of POSIX path of appPath" & Chr(13)
scriptToRun = scriptToRun & "end tell" & Chr(13)
scriptToRun = scriptToRun & "do shell script " & Chr(34) & "/usr/bin/osascript " & Chr(34) & " & appUserPath & " & Chr(34) & "playSound.scptd " & Chr(34) & " & " & Chr(34) & soundFile & Chr(34)

' MsgBox (scriptToRun)

MacScript (scriptToRun)

End Sub

Create a button in your Excel file to trigger the above macro. When you click the button it will run the AppleScript, playSound.scptd, which should be located in the same folder as the Excel file, passing an argument “boo” as the sound file we want to play.

I’m sure many of you don’t care about the technical details of how this works, but I was excited because this solution has taken so many years to figure out, so I thought I’d include it for those who would find it useful in their Excel projects.

Thanks to the bloggers who posted AppleScript tutorials around the Web. Archived message board Q&A posts helped a lot too. I jumped from website to Google to website so many times piecing it together, I can’t even find the main contributors except for this first AppleScript tutorial.

We are just a couple weeks from the release of the 2012 version of the CC/DB and there is still lots for me to do getting it ready, but I wanted to share that development milestone. Cheers.