At various times from ~2000 through 2009, Intuit had multiple requests to implement native support for AppleScript in QuickBooks and they completely ignored those of us who asked. No surprise there - Intuit's track record of treating Mac users as second class citizens and refusing to offer us *any* free support for their products (not even one phone call or email on occasions where we encountered installation issues or discovered new bugs) speaks volumes.
Luckily, you *can* AppleScript Quickbooks in an indirect fashion - by sending System Events through the OS X graphical user interface (GUI). AppleScripting through GUI is a kludge and nowhere near as fast, reliable, or effective as native support, but it works.
So, what is AppleScript? It is a built-in feature that permits one to automate any number of tasks in the Mac OS. See http://www.apple.com/applescript/. The focus of these pages is to show you how to automate data entry/extraction with Quickbooks 2009 for Mac under OS X 10.5.5 (Leopard). We've run these scripts under Snow Leopard, and they seem to work, but we have not extensively tested them.
If you don't know anything about AppleScript, it's time to go learn. There are lots of books out there and here are some web sites:
We are going to be relying on scripting the Mac OS Graphical User Interface (GUI). For any of the stuff in this tutorial to work, make sure that you have enabled GUI scripting using the AppleScript Utility application which is probably located in Applications: AppleScript on your hard drive:
Notice we have also checked "Show Script menu in menu bar". That allows us to put compiled scripts (AppleScripts that run as applications) in one easy to reach place so we can run them at any time while we're within any application.
Let's go ahead and launch the Script Editor application found in the AppleScript folder of your Applications folder and let's try it out!
In order to send commands to QuickBooks 2009, you need to have AppleScript tell it to activate. There are two ways to do this from a script. Open Script Editor, enter either of these into a new script window, press the Compile button and then press the Run button:
If your script worked correctly, QuickBooks 2009 will become the active application, launching itself if necessary. If you get an error, check the case and spelling of QuickBooks 2009.
Notice the use of the word tell. We can send one line commands to QB (or other applications) using the construction shown on the left or we can use a tell block (right) to send numerous commands between the tell....end tell statements.
QuickBooks 2009 will understand some basic AppleScript commands on its own. But to do anything useful with data input or extraction we will need to use OS X's System Events. Paste this script into Script Editor and run it (anything preceded by "--" is a comment, not a command):
tell application "QuickBooks 2009"
activate
tell application "System Events"
keystroke "i" using {command down}
end tell
end tell
If that worked correctly, QB will open a new blank invoice window for you. Now we are ready to being filling in information!
In the example above, we opened a new Invoice window. But how do we put data in? As discussed above, Quickbooks has some rudimentary AppleScript ability, but to send data to QB we need to emulate keystrokes, menu commands, or mouse movements/clicks. Let's look at each of these.
- Emulating keystrokes. For short blocks of text, you can simply use the following construction.
tell application "QuickBooks 2009"
activate
tell application "System Events"
keystroke "Some Text"
keystroke tab
end tell
end tell
This will enter the text "Some Text" into whatever field is selected in QB and then tab to the next field. Of course, you will want to vary the text that is entered. If you use AppleScript to obtain the data from another source (such as an email that you've parsed or a dialog box), you can put it into a variable. Let's change our script to use a variable named theCustomer instead of relying on the hardcoded text:
display dialog "Enter Customer Name" default answer ""
set theCustomer to text returned of the result
tell application "QuickBooks 2009"
activate
tell application "System Events"
keystroke theCustomer
keystroke tab
end tell
end tell
Keystrokes "type" rather slowly, so if your text is longer (such as the full address), you'll get better performance by putting it into a variable, setting the clipboard to the variable and then "pasting" it:
set billingAddress to "Jobs, Steve" & return & "Apple, Inc" & return & "1 Infinite Loop" & return & "Cupertino, CA 95014"
tell application "QuickBooks 2009"
activate
tell application "System Events"
set the clipboard to billingAddress
keystroke "v" using {command down}
keystroke tab
end tell
end tell
As you can navigate around QB using tab, return and arrow keys, it will be good to know these keystrokes:
keystroke (ASCII character 31)
keystroke (ASCII character 30)
keystroke (ASCII character 29)
keystroke (ASCII character 28)
keystroke (ASCII character 8)
key code 76
keystroke return
The Catch - Keystrokes take a physical amount of time. If you send many keystrokes in a row, QB may fall behind and you could be typing in the wrong box. In the detailed scripts we'll look at later, you can use short delays between keystrokes or sets of keystrokes to avoid such problems (at the expense of things taking a tiny bit longer).
- Using menu selection commands. While we can simulate certain actions by keystroking a command key (command-i for invoice, for example), some QB menu items do not have command key shortcuts. Luckily, we can activate QB menus directly. For example:
tell application "QuickBooks 2009"
activate
tell application "System Events"
click menu item "Enter Bills" of menu "Vendors" of menu bar item "Vendors" of menu bar 1 of process "QuickBooks 2009"
end tell
end tell
- Simulating mouse actions. AppleScript has limited support for mouse movements and clicks. A much easier way to use the mouse with AppleScript is to download the scripting extension called XTools OSAX from http://lestang.org/osax/XTool/XTool-2.0.dmg.tgz.
Once you have installed the XTools OSAX in your /Library/ScriptingAdditions/ folder, you can move the mouse and click it! Let's expand the example we just used. We'll open a new bill and click on the popup list of the A/P Account field. In order to do that, we need to get the coordinates (upper left and lower right) of the window:
tell application "QuickBooks 2009"
activate
tell application "System Events"
click menu item "Enter Bills" of menu "Vendors" of menu bar item "Vendors" of menu bar 1 of process "QuickBooks 2009"
end tell
delay 0.4
set theCoordinates to the bounds of window 1
tell application "System Events"
move mouse {(item 1 of theCoordinates) + 270, (item 2 of theCoordinates) + 45}
delay 0.1
click mouse
end tell
end tell
If that didn't work for you, you may need to adjust the hard-coded numbers in the move mouse command to meet your screen resolution. And once we have that popup menu open, we can keystroke up or down arrows as well as return to select the account we need. Alternatively, you can triple click on the position of the A/P Account field itself and then keystroke the name of the vendor. Remember, keystrokes go within the System Events tell block. And you may need to put in short time delays after each action.
On the next page we'll see some example scripts that use all of these techniques to get data into or out of Quickbooks 2009 Mac under OS X 10.5.5 (Leopard).
It would be very nice if we could use QuickBook's various checkboxes and buttons directly without having to figure out coordinates and send mouse commands. In fact, we can directly:
- Click on buttons such as Next, Previous, Recalc, Cancel, and OK.
- Click on the sub-pane buttons. For example, Expenses or Items in the Enter Bills window or Address Info, Additional Info, Job Info, and Notes in the Customer window.
- Probe whether a checkbox is checked, such as the Customer is Taxable or Add to iCal checkboxes.
BUT because QB takes its input through mouse and key actions we can NOT directly set the values of checkboxes or text fields. You can actually use AppleScript to change the values of checkboxes (and some text fields), and they will appear changed on the screen, but QB will neither see nor save changes made that way - you will have to emulate a mouse click or use keystroke in order for QB to accept it. Again, this is because QB is not directly scriptable, we are using the GUI to fake it.
Let's try an example. Open a New Customer window and click on the Additional Info button. We can probe the state of the Customer is Taxable checkbox this way. Enter this script into Script Editor and make sure the Event Log button in your script window is pressed so we can see the output. Change the value of the checkbox and run the script again - and you'll see that our script detects it!
set taxEnabled to -1
tell application "QuickBooks 2009"
activate
tell application "System Events"
set taxEnabled to the value of checkbox "Customer is taxable" of group 1 of tab group 1 of window "New Customer" of application process "QuickBooks 2009" as integer
end tell
end tell
log taxEnabled
if taxEnabled > 0 then
log "The customer is taxable, dude!"
else
log "No tax for you!"
end if
As you can see, we can use if/then/else constructions to take various actions depending on the state of the checkbox.
Notice also that the statement to probe the checkbox state is not something you could easily guess. You'll see how to figure out what to call each user interface (UI) element in the next section.
Finally, let's switch from the Address Info sheet to the Additional Info sheet of the New Customer window. Open a New Customer window and then run this script:
tell application "QuickBooks 2009" to activate
tell application "System Events"
click radio button "Additional Info" of tab group 1 of window "New Customer" of application process "QuickBooks 2009"
end tell
end tell
Pretty cool, eh?
So, how do we figure out what UI elements we can probe? Here is how we do it for the Invoice window. Open a new Invoice in QB, and then run this script with the Event Log button enabled so you can see the output:
tell application "System Events"
get the entire contents of window "Create Invoices" of process "QuickBooks 2009"
end tell
The output will be a huge long list of every user interface element. In fact, you may find it easier to copy those out as a block and paste them into a text editor so you can find/replace commas with line breaks to make it more readable. Some of those lines of output include:
- button 1 of window "Create Invoices" of application process "QuickBooks 2009" (the red close button)
- checkbox "Customer is
taxable" of window "Create Invoices" of application process "QuickBooks 2009"
- static text "Memo" of window "Create Invoices" of application process "QuickBooks 2009" (the word Memo as it appears on the invoice; this is a value that does not change)
- pop up button 1 of window "Create Invoices" of application process "QuickBooks 2009"
What is that last item? Let's probe it further:
tell application "System Events"
get the properties of pop up button 1 of window "Create Invoices" of process "QuickBooks 2009"
end tell
And from the list of the properties returned in the log window, we can see that it is the Template pop-up menu/button.
Finally, let's see how we figure out how to activate a given menu item:
tell application "System Events"
get the entire contents of menu "Customers" of menu bar 1 of process "QuickBooks 2009"
end tell
That's a lot easier than trying to guess the names or come up with those constructions yourself.
Finally, one other very excellent way of determining the names/identities of user interface elements is to download UIELementInspector, which may be found in the Utilites folder of your Applications folder. If not, you can Download it for 10.6 or later from Apple. We're not sure if the older versions are still available from Apple.
On the next page we will look at some Script Examples
Table of Contents
Return to the ILPI Miscellaneous Help Page
Copyright 2008-2024 by Interactive Learning Paradigms Incorporated. All rights reserved. Reposting of this material is not permitted and constitutes a violation of US and International copyright law.