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
1 |
x = CustomerName.GetValue |
rather than just
1 |
x = CustomerName.Value |
or even
1 |
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:
1 2 3 |
If Not CustomerName Is Nothing Then x = CustomerName.GetValue End If |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 |
Sub UpdateQBData() 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.Show UUpdate.lbxStatus.AddItem "Creating Queries…" DoEvents 'Instantiate variable 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 QuerySalesOrder qrySO Set qryPO = msgReq.AppendPurchaseOrderQueryRq QueryPurchaseOrder qryPO Set qryAssembly = msgReq.AppendItemInventoryAssemblyQueryRq QueryAssembly qryAssembly Set qryRaw = msgReq.AppendItemInventoryQueryRq QueryRaw qryRaw UUpdate.lbxStatus.AddItem "Retrieving Data from Quickbooks…" DoEvents '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…" DoEvents '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 Case rtSalesOrderQueryRs Set colSO = New Collection Set colSO = GetSalesOrder(CurResp) Case rtPurchaseOrderQueryRs Set colPO = New Collection Set colPO = GetPurchaseOrder(CurResp) Case rtItemInventoryAssemblyQueryRs Set colAss = New Collection Set colAss = GetAssembly(CurResp) Case rtItemInventoryQueryRs Set colRaw = New Collection Set colRaw = GetRaw(CurResp) End Select End If Next lRespCnt UUpdate.lbxStatus.AddItem "Writing Data to Worksheets…" DoEvents 'Write the data to the sheets WriteBackorder colSO WriteLoaners colSO WriteRepairs colSO WritePurchases colPO WriteAssembly colAss WriteRawMats colRaw WriteFinishedGoods colAss 'Update the text of a hyperlink that calls this sub wshSummary.Range("LastUpdate").Value = "LastUpdated: " & Format(Now, "m/d/yyyy h:mm AM/PM") ErrorExit: On Error Resume Next Application.DisplayAlerts = True SessMgr.EndSession SessMgr.CloseConnection Set SessMgr = Nothing Unload UUpdate Exit Sub ErrorHandler: MsgBox Err.Number & vbNewLine & vbNewLine & Err.Description Stop Resume ErrorExit End Sub |