Part I: Introduction and Controlling Procedure
Part II: Creating the Quickbooks Queries
Part III: Filling Class Modules
Part IV: Writing to Worksheets
In Part I, I listed the controlling procedure that does the Quickbooks related stuff and calls all the other sub procedures. In Part II, I listed the sub procedures that created each of the four queries. Now that the queries are set up, the controlling procedure sends the XML requests to Quickbooks and waits for responses. I takes about four minutes to run the whole thing and three-and-a-half minutes are waiting for Quickbooks to send back the data.
Once the responses are received, I loop through them and use a Select Case statement to handle each request separately. By handle, I mean store the data in such a way that I can use it. For each of the four responses, I created a class module. For instance, I have a CPurchaseOrder class module that will be used to hold the purchase order information.
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 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 |
Private msTxnID As String Private msTxnNumber As String Private msVendorName As String Private mdtPoDate As Date Private msPONumber As String Private mdtExpectedDate As Date Private mbIsManuallyClosed As Boolean Private mbIsFullyReceived As Boolean Private msItemName As String Private mdQtyOrdered As Double Private mdQtyReceived As Double Private mbLineIsManuallyClosed As Boolean Public Property Get TxnID() As String TxnID = msTxnID End Property Public Property Let TxnID(ByVal sTxnID As String) msTxnID = sTxnID End Property Public Property Get TxnNumber() As String TxnNumber = msTxnNumber End Property Public Property Let TxnNumber(ByVal sTxnNumber As String) msTxnNumber = sTxnNumber End Property Public Property Get VendorName() As String VendorName = msVendorName End Property Public Property Let VendorName(ByVal sVendorName As String) msVendorName = sVendorName End Property Public Property Get PoDate() As Date PoDate = mdtPoDate End Property Public Property Let PoDate(ByVal dtPoDate As Date) mdtPoDate = dtPoDate End Property Public Property Get PONumber() As String PONumber = msPONumber End Property Public Property Let PONumber(ByVal sPONumber As String) msPONumber = sPONumber End Property Public Property Get ExpectedDate() As Date ExpectedDate = mdtExpectedDate End Property Public Property Let ExpectedDate(ByVal dtExpectedDate As Date) mdtExpectedDate = dtExpectedDate End Property Public Property Get IsManuallyClosed() As Boolean IsManuallyClosed = mbIsManuallyClosed End Property Public Property Let IsManuallyClosed(ByVal bIsManuallyClosed As Boolean) mbIsManuallyClosed = bIsManuallyClosed End Property Public Property Get IsFullyReceived() As Boolean IsFullyReceived = mbIsFullyReceived End Property Public Property Let IsFullyReceived(ByVal bIsFullyReceived As Boolean) mbIsFullyReceived = bIsFullyReceived End Property Public Property Get ItemName() As String ItemName = msItemName End Property Public Property Let ItemName(ByVal sItemName As String) msItemName = sItemName End Property Public Property Get QtyOrdered() As Double QtyOrdered = mdQtyOrdered End Property Public Property Let QtyOrdered(ByVal dQtyOrdered As Double) mdQtyOrdered = dQtyOrdered End Property Public Property Get QtyReceived() As Double QtyReceived = mdQtyReceived End Property Public Property Let QtyReceived(ByVal dQtyReceived As Double) mdQtyReceived = dQtyReceived End Property Public Property Get LineIsManuallyClosed() As Boolean LineIsManuallyClosed = mbLineIsManuallyClosed End Property Public Property Let LineIsManuallyClosed(ByVal bLineIsManuallyClosed As Boolean) mbLineIsManuallyClosed = bLineIsManuallyClosed End Property |
Nothing too fancy there. I set up a Public variable for every “field” that I’m bringing over, then use MZTools to convert all those Public variables to Property Get/Let procedures. With the class module set up, I can parse the request and put all the data in the class module.
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 |
Function GetPurchaseOrder(ByRef CurResp As IResponse) As Collection Dim POList As IPurchaseOrderRetList Dim curPO As IPurchaseOrderRet Dim POLineList As IORPurchaseOrderLineRetList Dim curPOLine As IPurchaseOrderLineRet Dim colPO As Collection Dim clsPO As CPurchaseOrder Dim i As Long, j As Long 'Variable to hold the sales orders Set POList = CurResp.Detail Set colPO = New Collection 'Loop through the sales order list For i = 0 To POList.Count - 1 'Get the first one in the list Set curPO = POList.GetAt(i) Set POLineList = curPO.ORPurchaseOrderLineRetList For j = 0 To POLineList.Count - 1 Set curPOLine = POLineList.GetAt(j).PurchaseOrderLineRet Set clsPO = New CPurchaseOrder 'transaction ID If Not curPO.TxnID Is Nothing Then clsPO.TxnID = curPO.TxnID.GetValue End If 'tran num If Not curPO.TxnNumber Is Nothing Then clsPO.TxnNumber = curPO.TxnNumber.GetValue End If 'vendor If Not curPO.VendorRef Is Nothing Then If Not curPO.VendorRef.FullName Is Nothing Then clsPO.VendorName = curPO.VendorRef.FullName.GetValue End If End If 'PO Date If Not curPO.TxnDate Is Nothing Then clsPO.PoDate = curPO.TxnDate.GetValue End If 'closed If Not curPO.IsManuallyClosed Is Nothing Then clsPO.IsManuallyClosed = curPO.IsManuallyClosed.GetValue End If 'recd If Not curPO.IsFullyReceived Is Nothing Then clsPO.IsFullyReceived = curPO.IsFullyReceived.GetValue End If 'item If Not curPOLine.ItemRef Is Nothing Then If Not curPOLine.ItemRef.FullName Is Nothing Then clsPO.ItemName = curPOLine.ItemRef.FullName.GetValue End If End If 'order qty If Not curPOLine.Quantity Is Nothing Then clsPO.QtyOrdered = curPOLine.Quantity.GetValue End If 'rec’d qty If Not curPOLine.ReceivedQuantity Is Nothing Then clsPO.QtyReceived = curPOLine.ReceivedQuantity.GetValue End If 'line is closed If Not curPOLine.IsManuallyClosed Is Nothing Then clsPO.LineIsManuallyClosed = curPOLine.IsManuallyClosed.GetValue End If colPO.Add clsPO, CStr(curPOLine.TxnLineID.GetValue) Next j Next i Set GetPurchaseOrder = colPO End Function |
The appropriate IResponse object is passed into this function, which then returns a collection. The collection is a bunch of CPurchaseOrder objects and can be passed to procedures that do calculations or write to worksheets or both. For every field that I want to store in the class, I have to determine if Quickbooks returned anything by comparing it to Nothing, then call the GetValue method to set the class module’s property.
In addition to CPurchaseOrder and GetPurchaseOrder(), I have the following class module/function pairs: CAssembly, GetAssembly; CRawMaterial, GetRawMaterial; CSalesOrder, GetSalesOrder.
I don’t need to use class modules for this. That’s probably true for most uses of class modules. But there are a few reasons why I think it makes sense here. I could loop through all of the responses and immediately write the data to a worksheet, since that’s all I’m doing in the end anyway. With class modules I can access the Quickbooks data once, create multiple sub procedures without having a bunch of hard-to-maintain module-level variables, and easily add new calculations/output.
Accessing Quickbooks data is key because of the requirement to check the objects for Nothing and use the GetValue method to actually retrieve the data. If I, for instance, wanted to write a subset of my sales orders to a worksheet, I would have to limit them with code like this:
1 2 3 4 5 6 7 8 9 |
If Not curSO.IsFullyInvoiced Is Nothing And _ Not curSO.IsManuallyClosed Is Nothing And _ Not curSO.CustomerRef Is Nothing Then If curSO.CustomerRef.FullName Is Nothing Then If curSO.CustomerMsgRef.FullName.GetValue Like "Loaner*" And _ Not curSO.IsFullyInvoiced.GetValue And _ Not curSO.IsManuallyClosed Then |
I have to make sure that IsFullyInvoiced, IsManuallyClosed, and CustomerRef were returned by Quickbooks before I can access their values. Once that test is passed, I have to make sure that FullName was returned. I can’t check for FullName Is Nothing until I know that CustomerRef is there. Once I know that all the objects are there, I can GetValue to actually test my conditions.
With a class module, I test each of these conditions only one time, then put the data into the class’s property. Now I can access the class’s property without all the rigmarole. Compare:
1 2 3 |
If clsSO.CustName Like "Loaner*" And _ Not clsSO.IsFullyInvoiced And _ Not clsSO.SOIsManuallyClosed Then |
So much cleaner and easier to read.
The classes also come in handy for splitting up my procedures. I could use module-level variables to pass the responses back and forth between the procedures, but it’s easier and cleaner to pass everything in a collection. In order to put all that data into a collection, I create class modules which are held in the collection. I use sales orders to track product that I’ve loaned out to potential customers (kludgey workaround, thanks Intuit). I obviously also use sales orders to track sales that haven’t shipped – their intended use. With all my sales orders in one class, I can call a WriteSalesOrders procedure and a WriteLoaners procedure and pass each the same collection of CSalesOrder objects.
Once I had this set up, I liked it so much that I decided to use sales orders to track product that needed repair. All the sales orders were already in the class, so I only had to write a WriteRepairs procedure to pull the appropriate sales orders out and stick them on a worksheet. That compartmentalization makes it easy to add and delete features without screwing up stuff you know already works.
Posting code? Use <pre> tags for VBA and <code> tags for inline.