Excel 2003 - Copy Variant to Clipboard?
 

When using ADO record sets, I can do something like the following:

Dim ADODB_Connection As ADODB.Connection
Dim ADODB_Command As ADODB.Command
Dim ADODB_RecordSet As ADODB.RecordSet
Dim varRecordSet As Variant, varTransposed As Variant

....(ADO setup stuff) ...
Set ADODB_RecordSet = ADODB_Command.Execute
varRecordSet = ADODB_RecordSet.GetRows

but if continue with something like

Worksheets(1).Range("SomethingAppropriate").Value = varRecordSet

it comes out in c,r format (limiting rows returned to 256) instead of r,c
format.

Right now, I do something like the following:

For r = 1 to NumRows
For c = 1 to NumCols
varTransposed(r,c) = varRecordSet(c-1,r-1)
Next c
Next r

I was wondering if there was some way to move varRecordSet onto the
clipboard, then do something like

..PasteSpecial xlTransposed

instead of manually transposing the array.

Thanks, Bob

P.S. I looked into the DataObject, but it seems only to handle a string.

Excel 2003 - Copy Variant to Clipboard?
 

you need a connection and a recordset

open the connection to the database

then open the recordset ...


here's an example

Option Explicit
sub Fetch()
Dim rst As ADODB.Recordset
Dim db As Connection
Dim SQL As String

Set db = New Connection
With db
.CursorLocation = adUseClient
.Open "PROVIDER=MSDASQL;driver={SQL
Server};server=MyServerNameC;uid=;pwd=;database=My Database;"
End With

Set rst = New Recordset
SQL = "select itemnumber, description from Products"
rst.Open SQL, db, adOpenStatic, adLockOptimistic

Range("B1").CopyFromRecordset rst

rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

End Sub





"INTP56" wrote in message
...
When using ADO record sets, I can do something like the following:

Dim ADODB_Connection As ADODB.Connection
Dim ADODB_Command As ADODB.Command
Dim ADODB_RecordSet As ADODB.RecordSet
Dim varRecordSet As Variant, varTransposed As Variant

...(ADO setup stuff) ...
Set ADODB_RecordSet = ADODB_Command.Execute
varRecordSet = ADODB_RecordSet.GetRows

but if continue with something like

Worksheets(1).Range("SomethingAppropriate").Value = varRecordSet

it comes out in c,r format (limiting rows returned to 256) instead of r,c
format.

Right now, I do something like the following:

For r = 1 to NumRows
For c = 1 to NumCols
varTransposed(r,c) = varRecordSet(c-1,r-1)
Next c
Next r

I was wondering if there was some way to move varRecordSet onto the
clipboard, then do something like

.PasteSpecial xlTransposed

instead of manually transposing the array.

Thanks, Bob

P.S. I looked into the DataObject, but it seems only to handle a string.

Excel 2003 - Copy Variant to Clipboard?
 

Patrick,

Thanks for telling me about the Range.CopyFromRecordSet method. It's just
what I was looking for.

Bob


"Patrick Molloy" wrote:

you need a connection and a recordset

open the connection to the database

then open the recordset ...


here's an example

Option Explicit
sub Fetch()
Dim rst As ADODB.Recordset
Dim db As Connection
Dim SQL As String

Set db = New Connection
With db
.CursorLocation = adUseClient
.Open "PROVIDER=MSDASQL;driver={SQL
Server};server=MyServerNameC;uid=;pwd=;database=My Database;"
End With

Set rst = New Recordset
SQL = "select itemnumber, description from Products"
rst.Open SQL, db, adOpenStatic, adLockOptimistic

Range("B1").CopyFromRecordset rst

rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

End Sub





"INTP56" wrote in message
...
When using ADO record sets, I can do something like the following:

Dim ADODB_Connection As ADODB.Connection
Dim ADODB_Command As ADODB.Command
Dim ADODB_RecordSet As ADODB.RecordSet
Dim varRecordSet As Variant, varTransposed As Variant

...(ADO setup stuff) ...
Set ADODB_RecordSet = ADODB_Command.Execute
varRecordSet = ADODB_RecordSet.GetRows

but if continue with something like

Worksheets(1).Range("SomethingAppropriate").Value = varRecordSet

it comes out in c,r format (limiting rows returned to 256) instead of r,c
format.

Right now, I do something like the following:

For r = 1 to NumRows
For c = 1 to NumCols
varTransposed(r,c) = varRecordSet(c-1,r-1)
Next c
Next r

I was wondering if there was some way to move varRecordSet onto the
clipboard, then do something like

.PasteSpecial xlTransposed

instead of manually transposing the array.

Thanks, Bob

P.S. I looked into the DataObject, but it seems only to handle a string.

Excel 2003 - Copy Variant to Clipboard?
 

:)
glad to help

"INTP56" wrote in message
...
Patrick,

Thanks for telling me about the Range.CopyFromRecordSet method. It's just
what I was looking for.

Bob


"Patrick Molloy" wrote:

you need a connection and a recordset

open the connection to the database

then open the recordset ...


here's an example

Option Explicit
sub Fetch()
Dim rst As ADODB.Recordset
Dim db As Connection
Dim SQL As String

Set db = New Connection
With db
.CursorLocation = adUseClient
.Open "PROVIDER=MSDASQL;driver={SQL
Server};server=MyServerNameC;uid=;pwd=;database=My Database;"
End With

Set rst = New Recordset
SQL = "select itemnumber, description from Products"
rst.Open SQL, db, adOpenStatic, adLockOptimistic

Range("B1").CopyFromRecordset rst

rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

End Sub





"INTP56" wrote in message
...
When using ADO record sets, I can do something like the following:

Dim ADODB_Connection As ADODB.Connection
Dim ADODB_Command As ADODB.Command
Dim ADODB_RecordSet As ADODB.RecordSet
Dim varRecordSet As Variant, varTransposed As Variant

...(ADO setup stuff) ...
Set ADODB_RecordSet = ADODB_Command.Execute
varRecordSet = ADODB_RecordSet.GetRows

but if continue with something like

Worksheets(1).Range("SomethingAppropriate").Value = varRecordSet

it comes out in c,r format (limiting rows returned to 256) instead of
r,c
format.

Right now, I do something like the following:

For r = 1 to NumRows
For c = 1 to NumCols
varTransposed(r,c) = varRecordSet(c-1,r-1)
Next c
Next r

I was wondering if there was some way to move varRecordSet onto the
clipboard, then do something like

.PasteSpecial xlTransposed

instead of manually transposing the array.

Thanks, Bob

P.S. I looked into the DataObject, but it seems only to handle a
string.