Quickbooks SDK Part I

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

rather than just

or even

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 a Quickbooks install. Here’s the entry point code:

Part II >>

11 thoughts on “Quickbooks SDK Part I

  1. Hi

    I am new to this and in the process of learning. I know this is asking for too much. but
    can you help me with the progress bar form you made.

    thank you

  2. Your code sample is very useful. I have used it to write an Estimate ‘modify’ in order to add to an item.

    However, the code fails when creating OREstimateLineMod:
    Object not set to instance of an object

    orEstimateLineMod1 = EstimateMod.OREstimateLineModList.Append()

    Would you have code that I can use to compare as I couldn’t find in the QBSDK sample codes?

    Many thanks.

  3. Being very new to Excel: how do I, having created a worksheet with formulas in some cells, import data I want to be manipulated by the formulas?

  4. Hi Dick

    I have copied above code to VB6 program, and it is asking for reference of QBSessionManager, IMsgSetRequest, ISalesOrderQuery, IPurchaseOrderQuery etc.

    also, i included all references of QB but not working.
    can you please let me know what reference i should include in the program.

    reference means project menu -> references.

  5. Gupendra: I don’t have QB and VB6 on the same computer, so I can’t show you the VB6 reference screen. But the above is the VBA reference screen. I’m still using SDK 5.0, so if you have a new version, the name might be slightly different.

  6. I just came across your pages after downloading SDK 13. Your examples are a great start, but I’m also running into a lot of missing types, so I can’ t open a session to explore the objects.

    There are a TON of QB-related libraries showing up, and I can’t figure out which ones I need for the SDK. Any idea?

  7. Mercifully I haven’t had to use QB in a while. Do have libraries that start qbfc? If so, I would start with the one with the highest number. Sorry I couldn’t be more help.

  8. Thanks for the quick response.

    Through trial and error [I haven’t had a chance to manipulate examples and do any testing yet], it appears that selecting the following libraries fixed all of the broken object references:

    qbFC13 1.0 Type Library

    I had already loaded (because of some sample code from another site):

    QBXMLRP2 1.0 Type Library
    Microsoft XML, v6.0

    But I don’t think the latter have any dependencies in your sample code. A little “object browsing” suggests that many of the exposed libraries are actually used by QB to reach out or push data into Excel, trigger e-mail functionality, etc.

  9. Yep, that’s correct about lack of dependencies. qbFC is supposed to be an abstraction layer over XML. So you’d use the XML libraries if you wanted to write the XML requests and parse the XML responses yourself. You’d use qbFC if you want qbFC to do all the XML work and you just read the values.

    So it’s unlikely you’d use both sets of libraries.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.