Quickbooks SDK Part IV
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.
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.
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.
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.