Copying recordsets

[Bantler]

Hello All,

I am wanting to know if is at all possible to create a recordset via SQL statement and then select the data from 1 of the columns in that recordset which in turn will be paste on an excel sheet.

Code I Have so far below:

Sub getscorecard(thing2 As String, thing3 As String, thing4 As String)

Dim rsSource As New ADODB.Recordset, sqlst As String

Application.ScreenUpdating = False
Sheets("imported scorecard").Visible = True
Sheets("imported scorecard").unprotect Password:="ateam"

MakeConn
sqlst = "SELECT STATEMENT" 'Is rather long so didn't want to post!

With rsSource
.ActiveConnection = conSC
.Open sqlst
Sheets("imported scorecard").Select
Cells.ClearContents
Set ws = Application.ActiveSheet

For iCols = 0 To rsSource.Fields.Count - 1
ws.Cells(1, iCols + 1).Value = rsSource.Fields(iCols).Name
Next
ws.Range(ws.Cells(1, 1), ws.Cell(1,rsSource.Fields.Count+1))
.Font.Bold = True
ws.Range("A2").CopyFromRecordset rsSource
End With

What I'm stuck on is how to actaully copy the column I want.

My guess was it would be straight forward and something like:

ws.Range("A2").CopyFromRecordset rsSource.Fields(6)
ws.Range("A2").CopyFromRecordset rsSource.Fields(6).value

But neither work.

End result all I want to be pasted is one column data based on cell value matches and looping through.

If anyone haas any idea will be much appreicated.

Many Thanks

Bantler

 

[DCrake]

From what I gather all you need is

ws.Range("A2").value = .Fields(6)

 

[Bantler]

Hello,

Many thanks for your reply. I think I may not have been clear. The code you sugguested would bring the value of .fields(6). What I need is all the values in colunm 6 in the recordset.

Each colunm in the recordset has 12 rows so if im selecting column 6 is would only paste in 12 row of data into my excel sheet.

Thanks again

Bantler

 

[DCrake]

Here is a Link to a MS article on populating Excel from Access.

Hope this helps

David

 

[boblarson]

If you only want a certain column, then do up another query that has only that column. Then it is no problem.