Part I: Introduction and Controlling Procedure
Part II: Creating the Quickbooks Queries
Part III: Filling Class Modules
Part IV: Writing to Worksheets
In the last part, I take the collection of custom objects from Part III and write each object to a worksheet. For instance, I filled a collection with CSalesOrder objects and now I pass that collection to the following procedure. It accesses the properties of the CSalesOrder class to determine if that specific sales order belongs on the backorder report and writes certain columns to a table on the worksheet wshSO.
|
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 |
Sub WriteBackorder(ByRef colSO As Collection) Dim clsSO As CSalesOrder Dim i As Long Dim rStart As Range Dim lRow As Long Dim aTitle As Variant With wshSO.Range("A1:K1") .Parent.UsedRange.ClearContents Set rStart = .Item(1) aTitle = Array("TxnID", "TxnNumber", "CustomerRefFullName", "RefNumber", _ "DueDate", "ShipDate", "IsManuallyClosed", "IsFullyInvoiced", "SalesOrderLineItemRefFullName", _ "SalesOrderLineQuantity", "SalesOrderLineInvoiced") .Value = aTitle .Font.Bold = True End With lRow = 1 For i = 1 To colSO.Count Set clsSO = colSO.Item(i) If Not clsSO.IsLoanerLog And _ Not clsSO.IsFullyInvoiced And _ Not clsSO.SOIsManuallyClosed And _ Not clsSO.IsInRepair Then rStart.Offset(lRow, 0).Value = clsSO.TxnID rStart.Offset(lRow, 1).Value = clsSO.TxnNumber rStart.Offset(lRow, 2).Value = clsSO.CustName rStart.Offset(lRow, 3).Value = clsSO.SONum rStart.Offset(lRow, 4).Value = clsSO.DueDate rStart.Offset(lRow, 5).Value = clsSO.PromiseDate rStart.Offset(lRow, 6).Value = clsSO.SOIsManuallyClosed rStart.Offset(lRow, 7).Value = clsSO.IsFullyInvoiced rStart.Offset(lRow, 8).Value = clsSO.InventoryItem rStart.Offset(lRow, 9).Value = clsSO.QtyOrdered rStart.Offset(lRow, 10).Value = clsSO.QtyShipped lRow = lRow + 1 End If Next i AdjustNames wshSO, aTitle, lRow End Sub |
I can pass this same collection to the WriteRepairs procedure where I can get at the sales orders that have been designated as repairs.
|
1 2 3 |
If clsSO.IsInRepair And _ Not clsSO.IsFullyInvoiced And _ Not clsSO.SOIsManuallyClosed Then |
At the end of the “write” procedures, I call the AdjustNames procedure, passing it an array of headings and the number of the last row to which I wrote data. This procedure adjusts the defined names on the worksheet, which have the same name as the heading, to encompass all of the data.
|
1 2 3 4 5 6 7 8 9 |
Sub AdjustNames(ws As Worksheet, vArr As Variant, lRow As Long) Dim i As Long For i = LBound(vArr) To UBound(vArr) ws.Names(vArr(i)).RefersTo = “=” & ws.Cells(2, i + 1).Resize(lRow).Address(True, True, xlA1) Next i End Sub |
At this point, I have several worksheets of data that I can access with formulas and summarize however I wish.
Download QBMRP.zip.
Hi Dick – tried downloading the zip file indicated to have a look, and it’s not having any of it!
Cheers
Mike
Fixed – thanks Mike.
marvellous, all downloaded to be looked at over the weekend – dodgy color schemes and all!
Thanks
Mike
Hello Dick,
The following code is working fine in owc.spreadsheet component version 9 but not working fine when i use owc11.spreadsheet component.
‘******************************************
sub subWriteHeader
with objExcel.Range(“C1:E1?)
.Merge
.Borders.Color = “black”
.Interior.Color = “white”
.Font.Bold = “true”
.Borders.Weight = 2
.Font.Size = “12?
.Font.Color = “Darkblue”
.value = “WWAS Active Delegations”
end with
with objExcel.Range(“C4:C4?)
.Font.Color = “green”
.Font.Size= “12?
.Value= “Limits Displayed In US Dollars”
end with
with objExcel.Range(“A5:N5?)
.Borders.Color = “black”
.Interior.Color = “LightSteelBlue”
.Borders.Weight = 1
.Font.Size = “12?
.Font.Color = “white”
end with
objExcel.Cells(5, 1).Value = “Authorizer”
objExcel.Cells(5, 2).Value = “Authorizer Emp#”
objExcel.Cells(5, 3).Value = “Location Code”
objExcel.Cells(5, 4).Value = “MRU Code”
objExcel.Cells(5, 5).Value = “General Expenses”
objExcel.Cells(5, 6).Value = “Capital Purchases”
objExcel.Cells(5, 7).Value = “Business Case Name”
objExcel.Cells(5, 8).Value = “Business Case Limit”
objExcel.Cells(5, 9).Value = “Delegate”
objExcel.Cells(5, 10).Value = “Delegate Emp#”
objExcel.Cells(5, 11).Value = “Delegate Email”
objExcel.Cells(5, 12).Value = “Delegate Phone”
objExcel.Cells(5, 13).Value = “From Date”
objExcel.Cells(5, 14).Value = “To Date”
end sub
The cell preoperties and the headers are not setting.
Please can you give me a clue what could be the problem.
Thanks,
Raghu
B-E-A-U-T-I-F-U-L. (and clearly written blog & coding)
Nice summary and use of vba to access and enumerate your QB data.
Saved me hours of pawing, sucking, and chewing on the rather terse IDN info.
Thanks much-o for the sample code!
dave m
Dear Dick,
I enjoyed your tutorial and I downloaded your zip file and toyed around with with the code to understand your usage. I am currently working on capturing information from quickbooks for use in MS Access. I am needed the “Duration” field in the timetrackingquery. I have a working xml file that, when I use the SDK test utility, successfully creates an xml response that contains the Duration field. eg PT4H30M0S. I then save that xml file. I have writen vba code to import the xml into Excel and run through several operation that turns that Duration value into 4.5
I would love to find a way to capture this query from QB from Excel VBA. If tried to rewrite your code, but I run into an Argument error when I hit the line of code that contains Duration.GetValue
Since I know the xml file can get the data, I know QB will supply it. I would really love any help you can provide so I can send the xml request and process the response from within Excel’s VBA.
Scott
Scott: What is the exact text of the error message you’re getting. Also, if you can, send the code you’re using to dkusleika@gmail.com. I don’t know if I have time tracking set up here, but I might be able to spot something in the code.