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
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:
If Not CustomerName Is Nothing Then
x = CustomerName.GetValue
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:
Dim SessMgr As QBSessionManager 'Top level object
Dim msgReq As IMsgSetRequest 'Container for xml request
Dim qrySO As ISalesOrderQuery
Dim qryPO As IPurchaseOrderQuery
Dim qryAssembly As IItemInventoryAssemblyQuery
Dim qryRaw As IItemInventoryQuery
Dim resp As IMsgSetResponse 'Container for xml responses
Dim respList As IResponseList 'Collection of responses
Dim CurResp As IResponse 'Object variable to loop through IResponseList
Dim colSO As Collection 'collection of sales orders
Dim colPO As Collection 'collection of purchase orders
Dim colAss As Collection 'collection of inventory assemblies
Dim colRaw As Collection 'collection of raw materials
Dim lRespCnt As Long
On Error GoTo ErrorHandler
Application.DisplayAlerts = False
'Calls a pseuod-progress bar userform
UUpdate.lbxStatus.AddItem "Creating Queries…"
Set SessMgr = New QBSessionManager
SessMgr.OpenConnection "", "AIM_Prod"
SessMgr.BeginSession " \ ActserverQBDataAIM - USA.QBW", omMultiUser
'Create a container for the input message
Set msgReq = SessMgr.CreateMsgSetRequest("US", 5, 0)
msgReq.Attributes.OnError = roeContinue
'Create a query to hold details
Set qrySO = msgReq.AppendSalesOrderQueryRq
Set qryPO = msgReq.AppendPurchaseOrderQueryRq
Set qryAssembly = msgReq.AppendItemInventoryAssemblyQueryRq
Set qryRaw = msgReq.AppendItemInventoryQueryRq
UUpdate.lbxStatus.AddItem "Retrieving Data from Quickbooks…"
'Send the request to QB and get a response
Set resp = SessMgr.DoRequests(msgReq)
'Put all responses in a collection
Set respList = resp.ResponseList
UUpdate.lbxStatus.AddItem "Processing Data…"
'Loop through responses
For lRespCnt = 0 To respList.Count - 1
Set CurResp = respList.GetAt(lRespCnt)
If CurResp.StatusCode = 0 Then 'Status code indicates success
'Fill a collection based on the type of response
Select Case CurResp.Type.GetValue
Set colSO = New Collection
Set colSO = GetSalesOrder(CurResp)
Set colPO = New Collection
Set colPO = GetPurchaseOrder(CurResp)
Set colAss = New Collection
Set colAss = GetAssembly(CurResp)
Set colRaw = New Collection
Set colRaw = GetRaw(CurResp)
UUpdate.lbxStatus.AddItem "Writing Data to Worksheets…"
'Write the data to the sheets
'Update the text of a hyperlink that calls this sub
wshSummary.Range("LastUpdate").Value = "LastUpdated: " & Format(Now, "m/d/yyyy h:mm AM/PM")
On Error Resume Next
Application.DisplayAlerts = True
Set SessMgr = Nothing
MsgBox Err.Number & vbNewLine & vbNewLine & Err.Description