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.
MikeC:
Hi Dick - tried downloading the zip file indicated to have a look, and it's not having any of it!
24 November 2006, 3:06 amCheers
Mike
Dick Kusleika:
Fixed - thanks Mike.
24 November 2006, 8:07 amMikeC:
marvellous, all downloaded to be looked at over the weekend - dodgy color schemes and all!
24 November 2006, 9:20 amThanks
Mike
raghu:
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,
19 July 2007, 10:45 amRaghu