Daily Dose of Excel

Part I: Introduction and Controlling Procedure
Part II: Creating the Quickbooks Queries
Part III: Filling Class Modules
Part IV: Writing to Worksheets

The Quickbooks SDK is the only way I know of to get data out of Quickbook's proprietary database format and into Excel (SDK=Software Developer's Kit, if you didn't know). It's an XML based system; you send an XML request and it returns and XML response. Assuming it was able to fill your request, the XML response will be a structure filled with the data you requested.

There is a very, very thin object model wrapper that will take care of the XML for you. Thin though it may be, it's still better than creating my own XML strings. Inexplicably, everything in the OM is a method, that is, there are no properties. If you want to get the customer's name, it looks like

x = CustomerName.GetValue

rather than just

x = CustomerName.Value

or even

x = Customer.Name.

But that's not all. If there is no CustomerName object associated with your request, the CustomerName object will be Nothing and the GetValue method will fail. So now to get the customer's name, you need code like this:

For every "property", you need to test for the nothingness of object, then call it's GetValue method. It seems like it would have been just as easy to create a more robust object model that had real objects, properties, and methods. I would prefer a Customer object with a Name property rather than a CustomerName object. The result is that the code you need to write, compared to say using ADO to access a SQL database, is extremely long. The correct answer is for Intuit to provide an ODBC driver that accesses the data directly. Currently, there is a third party ODBC driver that uses the XML request/response system. In addition to long code, everything runs significantly slower than ADO.

I wanted to put some code out there for other people who may want to do the same things. I didn't want to spend all my time complaining about the SDK, although I can see that's unavoidable. In its defense, the SDK is extremely well documented. You may not like the way you have to do it, but you'll know how to do it. Also, the developer community is very active and helpful.

For my first installment, I'm posting my main procedure. There's a hyperlink on a worksheet that displays the last time this procedure was run. Because it takes more than five minutes to run, I don't want to run it if the information is fairly up to date. The FollowHyperlink event is used to run this procedure when the hyperlink is clicked.

The purpose of this workbook is to manage inventory. I need to have several tables of information to tell me how many pieces I have on hand, how many are out on loan, how many are in repair, how many are on order from vendors, and how many are due to customers. If Quickbooks had an "inventory location" feature, I wouldn't need any of this, but they don't.

The flow of the procedure starts with the standard Quickbooks stuff. I have to create a session, open a connection, begin the session, etc. Once I have a session up and going, I need to create the requests. For my purposes, I have four requests; sales orders, purchase orders, inventory items, and inventory assemblies. For each request, I create a AppendxxxxQuery, then pass it to a procedure that builds the query. I'll post that procedure in a future installment.

I end up with more tables in my workbook than requests because some of my tables are subsets of Quickbooks' tables. My inventory on loan, for instance, is recorded as a sales order to a dummy customer. I query all the sales orders from QB and pull out the loaners to create that table.

Once my queries are completed, I send the request to Quickbooks and get a response back. This takes the longest by far. There's no callback so that I can update a progress bar either. Because I sent four requests, I should get four responses. Each response is sent to a procedure that fills a collection with custom objects. The procedures and the class modules will come in a future post. Setting up the class modules is a pain, but the alternative is checking the stupid objects for Nothing and using GetValue to get the values. This way, I have an object called Assembly which has a number of properties like the one called QtyOnHand. I can get that value like I would a property of an object in Excel's object model.

Now that I have four collections filled with CAssembly, CPurchaseOrder, CRawMaterial, and CSalesOrder objects, I pass those collections to 'Write' procedures that loop through the objects and write tables to worksheets.

That's pretty much it. I have some other procedures that filter items out of these tables and I have a bunch of formulas that draw info out, but that's the meat of it. I'll post the other procedures and class modules soon. I don't know if I'll have a download associated with this. While I think it's nice to see everything in one place, it will hardly work with just an Quickbooks install. Here's the entry point code:

Part II >>