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 |
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
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.
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?
I am trying to download SDK and get an error 404 page not found. Any ideas on where I can download the SDK from
Thanks In Advance
I have to log in to get it, but it’s here now
http://member.developer.intuit.com/myidn/technical_resources/qb_sdk/default.aspx?id=1826
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.
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.
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?
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.
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:
Intuit_Quickbooks_FCS
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.
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.