Copy recordset to certain columns in excel

[mikeyp3]

Hello,

I'm looking to copy a recordset to certain columns in excel. I am familiar with moving a complete recordset to a range in excel, but is there code to place:

rst.field(1) in cell("b10") and rst.field(4) in cell (I10)


here is my code to copy an entire recordset

Dim db As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim intLastCol As Integer

Const conMAX_ROWS = 20000

Set db = CurrentProject.Connection
rst.Open "qryAUVs", db, adOpenKeyset, adLockReadOnly

intLastCol = worksheet1.UsedRange.Columns.Count
With worksheet1
.Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
intLastCol)).ClearContents

.Range("A2").CopyFromRecordset rst
End With

rst.Close
db.Close


This code works fine, but it puts the access data into excel, column for column(one next to the other). I'd like to put field1 in column B and Field 2 in column I. Any help is appreciated. Thx. Mike

 

[Trevil]

How about something like:

Set oSheet1 = fvobj_Excel.Workbooks.OPEN(fv_strWorkSheetPath & fvstr_WorkSheet).Sheets(1)
Do while not rst.eof

oSheet1.cells(9, 2).Value = Val(rst("CountofSSN").Value)
oSheet1.cells(9, 3).Value = Val(rst("SomeDate").Value)
rst.movenext
Loop

"Hmmm, it worked when I tested it....

 

[PHV]

field1 in column B and Field 2 in column I
Something like this ?
Dim myRow As Long
With worksheet1
.Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
intLastCol)).ClearContents
myRow = 1
While Not (rst.EOF Or rst.BOF)
.Cells(myRow, 2) = rst(0) 'Field1 in colB
.Cells(myRow, 9) = rst(1) 'Field2 in colI
myRow = myRow + 1
rst.MoveNext
WEnd
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886