Paste data from Recordset

Question

I want to paste the data in record set to 3 columns in a worksheet.

 rs.Open "SELECT [Sheet1$].Security,[Sheet1$].Description,[Sheet1$].ID FROM [Sheet1$] WHERE [Sheet1$].Number=1", cn, adOpenKeyset, adLockReadOnly

With Worksheets("Sheet4")
    .Cells.ClearContents
    .Cells(5, 1).CopyFromRecordset rs
End With

When I use the above code it pastes the data in to sheet 4 starting from 5,1, whereas I want to paste this data in column A, E, H of sheet 4. can anyone throw some light please.

Not sure if you can paste data from a recordset in a discontinuous range. You'd probably have to copy it to a temp worksheet and then copy the columns you want or create 3 recordsets and then paste them 1 at a time in the desired columns. — OpiesDad
– OpiesDad, Commented Mar 13, 2015 at 20:34
Loop over the recordset and copy each field value to the relevant column. — Tim Williams
– Tim Williams, Commented Mar 13, 2015 at 21:31

barrowc · Accepted Answer · 2015-03-14 03:14:45Z

1

You can put constant values into a SELECT statement so:

rs.Open "SELECT [Sheet1$].Security, '', '', '', [Sheet1$].Description, '', '', [Sheet1$].ID FROM [Sheet1$] WHERE [Sheet1$].Number=1", cn, adOpenKeyset, adLockReadOnly

or you can use NULL:

rs.Open "SELECT [Sheet1$].Security, NULL, NULL, NULL, [Sheet1$].Description, NULL, NULL, [Sheet1$].ID FROM [Sheet1$] WHERE [Sheet1$].Number=1", cn, adOpenKeyset, adLockReadOnly

edit: this will obliterate everything in columns B:D and F:G which may or may not be an issue for you

answered Mar 14, 2015 at 3:14

barrowc

10.7k2 gold badges47 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Paste data from Recordset

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related