Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 Clipboard Question | Excel Discussion (Misc queries) | |||
Excel 2003 Clipboard | Excel Discussion (Misc queries) | |||
Transfer clipboard from Task pane clipboard(office?) content to Excel (windows?) clipboard? | Excel Programming |