Access.2007.VBA.Bibl..

Reset lngCount to the number of records in the query:

rst.MoveLast

rst.MoveFirst

lngCount = rst.RecordCount

For n = 1 To lngCount

Write data from the recordset to cells in the current row of the worksheet, using the columnoff-

setargument to move to the next cell:

rng.Value = Nz(rst![ShipCountry])

Set rng = rng.Offset(columnoffset:=1)

rng.Value = Nz(rst![Category])

Set rng = rng.Offset(columnoffset:=1)

rng.Value = Nz(rst![Product])

Set rng = rng.Offset(columnoffset:=1)

rng.Value = Nz(rst![Customer])

Set rng = rng.Offset(columnoffset:=1)

rng.Value = Nz(rst![OrderID])

Set rng = rng.Offset(columnoffset:=1)

rng.Value = Nz(rst![UnitPrice])

Set rng = rng.Offset(columnoffset:=1)

rng.Value = Nz(rst![Quantity])

Set rng = rng.Offset(columnoffset:=1)

rng.Value = Nz(rst![Discount])

Set rng = rng.Offset(columnoffset:=1)

rng.Value = Nz(rst![TotalPrice])

Go to the next row of the worksheet, using the rowoffsetargument:

rst.MoveNext

Set rng = rngStart.Offset(rowoffset:=n)

Next n

Determine the number of data rows in the worksheet with the UsedRangeproperty:

lngRows = wks.UsedRange.Rows.Count

Debug.Print “Number of data rows in worksheet: “ & lngRows

Define the data range:

strRange = “A4:I” & CStr(lngRows)

Set rngData = wks.Range(strRange)

Apply hairline borders to the data range:

With rngData

.Borders(xlDiagonalDown).LineStyle = xlNone

.Borders(xlDiagonalUp).LineStyle = xlNone

Part I The Office Components and What They Do Best