AppleScripting QuickBooks 2009 Mac

A reader of Intuit's QuickBooks "Community" wanted to "create a workflow to automate the several steps it takes to get to where I need to go. Chart of accounts - Quick Report - Filters - add filter - Name." Here's how you do that:

tell application "QuickBooks 2009" to activate

tell application "System Events"

-- Simulate keystroke command-shift-A to open the Chart of Accounts

keystroke "a" using {command down, shift down}

-- Give the program a moment to catch up

delay 0.4 -- Or use Handy Routine #1 on the next page of this tutorial instead

-- Click on the second button at the very bottom of the Chart of Accounts Page

-- You will need to change "XXXX" to the name of your company as it appears in the title bar

click menu button 1 of window "XXXX - Chart of Accounts" of application process "QuickBooks 2009"

-- Select Quick Report

delay 0.4

keystroke "q"

keystroke return

-- Quick Report is open. Click the Filters… button (the … is an ellipsis, not 3 periods!)

delay 0.4

click button "Filters…" of window "Account QuickReport" of application process "QuickBooks 2009"

-- Filters window is open. Click the Add Filter... popup using the XTools move/click mouse method

move mouse (position of pop up button 1 of window "Report Filters" of process "QuickBooks 2009" as list )

click mouse

delay 0.1

keystroke "N" --Want to add a Name filter in this example. Simply keystroke the first letter (or two) of the field you want to add

keystroke return

delay 0.3

-- This is how you click on any of the popups that appear in the middle of this pane. You may need to adjust the x coordinate on your system

set theCoordinates to the position of static text "Name" of window "Report Filters" of application process "QuickBooks 2009"

move mouse {(item 1 of theCoordinates) + 330, (item 2 of theCoordinates) + 5}

click mouse

-- And now you just keystroke the customer name or other criterion and then keystroke return.

-- This is how you click on any of the "-" buttons to delete a filter in this pane. You may need to adjust the x coordinate on your system

set theCoordinates to the position of static text "Name" of window "Report Filters" of application process "QuickBooks 2009"

move mouse {(item 1 of theCoordinates) + 540, (item 2 of theCoordinates) + 5}

click mouse

end tell

If the script doesn't work right, you may need to add some extra delays or increase the delays to 1.0. And We haven't tested this script to see whether it is resolution dependent. And if the mouse commands aren't working, go back to the first page and make sure you've installed the XTools OSAX.

For information on how to save reports as PDF files and email them see Examples page 5.

Ever want to get information out of an invoice? For example, to run a credit card authorization or create a UPS shipment from the data contained in the invoice? This routine will do the extraction for you:

-- Ask the user to open the desired invoice

set theReply to display dialog "Open an existing Quickbooks invoice and then click Run it!" with icon note buttons ["Abort Script", "Run it!"] default button 2

if (button returned of theReply is "Abort Script") then return

-- Here's an error checking example...

if the name of window 1 of application "QuickBooks 2009" does not contain "Invoice" then

set theReply to display dialog "Dude, I don't see an invoice open." with icon note buttons ["Oops"]

return

end if

-- OK, let's get the data from QB

tell application "QuickBooks 2009"

activate

delay 0.4

-- get the coordinates of the Invoice window. These are given as {x1,y1,x2,y2} which are the x and y coordinates of the upper left and lower right corners.

set theCoordinates to the bounds of window 1

tell application "System Events"

-- We want to start on the Customer:Job field. We do that by moving the mouse to that location and triple-clicking to select it.

-- Remember, mouse commands require the XTools OSAX (see the previous page).

move mouse {(item 1 of theCoordinates + 160), (item 2 of theCoordinates + 45)}

delay 0.4

click mouse

click mouse

click mouse

-- Now we can copy the customer's name by emulating a Copy keystroke.

set the clipboard to "" ---- this clears whatever was already on the clipboard

keystroke "c" using {command down} -- -- copy the name to the clipboard

set theCustomer to the clipboard as text

-- It's a good idea to error check here, but we'll ignore that for now.

-- Then we tab to the Date field...you might notice the next couple of items are done identically, so the code could be shortened by using a repeat loop or a function, but it doesn't really matter....

delay 0.1

keystroke tab

delay 0.1

keystroke "c" using {command down}

set orderDate to the clipboard as text

-- Get the Invoice number

delay 0.1

keystroke tab

delay 0.1

keystroke "c" using {command down}

set invoiceNumber to the clipboard as text

-- Get the Billing Address

delay 0.1

keystroke tab

delay 0.1

keystroke "c" using {command down}

set billingAddress to the clipboard as text

-- Get the Shipping Address

delay 0.2

keystroke tab

delay 0.1

keystroke "c" using {command down}

set shippingAddress to the clipboard as text

-- The exact fields you tab to next depend on your invoice layout. In this example, we have PO Number, Terms, Due Date, and Ship via before we get to the line items.

-- Get the PO number

keystroke tab

delay 0.1

set the clipboard to "" -- in case PO number field is empty

keystroke "c" using {command down}

set PONumber to the clipboard as text

delay 0.1

-- Get the Terms

keystroke tab

delay 0.1

keystroke "c" using {command down}

set paymentMethod to the clipboard as text

delay 0.1

-- We'll skip extracting the rest of the items on this line...you get the idea how to pull them out...

keystroke tab

keystroke tab

keystroke tab

delay 0.2

-- That final tab puts us on the item number field of the first line item.

-- We'll loop through each line and put the line items into a list called myItems.

set myItems to {} -- AppleScript lists are contained within curly braces. Our list will be a list of lists, one for each line item.

set orderTotal to 0 as real -- We can add up the total amount of the order as we go (except for taxes unless we do some extra work).

set dataFlag to false -- We'll change this when we hit a line that has no Item #. An easy way to exit our loop.

repeat while dataFlag is false

set thisItem to {} -- the line item that we are building.

-- Grab the item #

keystroke "c" using {command down} -- copy the item # to the clipboard

delay 0.1

set the end of thisItem to the clipboard as text -- this is a handy construction to add an item to the end of a list.

if (the clipboard) is "" then

set dataFlag to true -- If the item number is blank, we are done collecting data.

else

-- Grab our description

keystroke tab

delay 0.1

keystroke "a" using {command down} -- or only the last word is selected...stupid QB

keystroke "c" using {command down} -- copy the description to the clipboard

delay 0.2

set the end of thisItem to the clipboard as text

delay 0.2

-- And the rest of the items on the line...

keystroke tab

keystroke "c" using {command down} -- copy the quantity to the clipboard. I"ve left this as text, but you might want it to be real instead

delay 0.2

set the end of thisItem to the clipboard as text

delay 0.2

keystroke tab

delay 0.2

keystroke "c" using {command down} -- copy the price to the clipboard; again, you can make it real (see below)

delay 0.2

set the end of thisItem to the clipboard as text

delay 0.2

keystroke tab

delay 0.2

keystroke "c" using {command down} -- copy the line item total to the clipboard

delay 0.2

set the end of thisItem to the clipboard as text

set orderTotal to orderTotal + ((the clipboard as text) as real)

delay 0.2

keystroke tab

set the clipboard to "" -- If the next line is empty, there is nothing to copy and the last Total will still be on the clipboard

set the end of myItems to thisItem

end if

end repeat

end tell

end tell

After copying the above and pasting it into Script Editor, you now have a handy script to extract invoice data from QuickBooks 2009 Mac. How do we *see* the values? We can refer to the items in the variable called "myItems" or any of the other variables (named in green above) that we created. To see some examples in action, add these lines to the end of your script and run the script with the Event Log window open.

log orderDate

log invoiceNumber

log orderTotal

log item 1 of myItems

log item 3 of (item 1 of myItems)

log item 2 of myItems

You can then write additional AppleScript code that will, for example, write the information to a file, email it to a vendor, create a UPS shipping label, run a credit card authorization, check stock etc. Pretty cool! Read the rest of this tutorial for some examples.

Performance note 1:The execution of any script is much slower when you have the Event Log pane enabled in Script Editor. If you switch to the Result pane, execution will be much faster, but you won't see any of the commands that AppleScript is sending or the log results. You can still probe a variable without using the log construction. For example, try this at the end of your script:

display dialog "The order date was " & orderDate

That line will generate a popup window showing the order date that we extracted. Note that you want to put this line of code after the "tell QB" block or the command will be sent to QB, which will probably choke on it. If you need to include it within the QB tell block for some reason you can use the construction:

tell me to display dialog "The order date was " & orderDate

You get the fastest execution if you compile and save a copy of your script as an application. That's easy to do in Script Editor, but we won't explain it here. In fact, once you save your application, you can place it in your User Scripts folder and fire it from within any application by selecting it from the Script menu in the right hand side of your menu bar. You can enable that menu by selecting a checkbox inside the AppleScript Utility application on your computer (see page 1 for more about AppleScript Utility).

Performance note 2: It's our experience that QuickBooks 2009 Mac, like all previous versions of the product, appears to have a memory leak. If you leave QuickBooks 2009 Mac running all the time instead of quitting it each day, the amount of RAM it takes ups grows. For example, it might start 200 MB of RAM, but after a few days it will occupy 450 MB. This can cause delays when accessing new windows or tabbing between fields. Therefore, it's a good idea to make sure you quit and restart QB from time to time. Which is a good idea anyway as memorized transactions are compared against the current date only at startup - if you never quit and restarted QB, your memorized transactions would never enter themselves!

One can avoid having to worry about this kind of performance hit by performing data integrity checks on the items as you grab them. For example, look for the presence or absence of "/" in the date that you grabbed, see if your order number is too long/short, make sure the customer name is longer than x characters etc. These sorts of approaches are ideal because they will transfer to other computers much more cleanly than code that depends on making the delays between fields long enough.

On the next pages we will look at some Handy Routines that make GUI scripting more reliable.