writing ADODB recordset to excel sheet
 

How can I write the data in record set to cells in excel? I have the
following code and the query always returns only ONE row. I want the
result in data set to be put on the excel sheet like say Column1 in
cell A1, column2 in cell A2 and so on...

CODE
---------------------------------


Dim ProdConnectString As String
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
' String used for connecting to DB
ProdConnectString = "provider=msdaora.1;user
id=USER;password=PASS;data source =
DATASOURCE;option=1+2+8+32+2048+16384"
conn.ConnectionString = ProdConnectString
conn.CursorLocation = adUseClient
conn.Open
mySQLCriTeria = "SELECT A.Col1, A.Col2, B.Col2, B.Col3 from TBL A,
TBL B where A.Col1=B.Col1"'"
rs.Open mySQLCriTeria, conn

---------------------------------

Thanks for all the help.

-BS


--
bhavesh78
------------------------------------------------------------------------
bhavesh78's Profile: http://www.excelforum.com/member.php...o&userid=17851
View this thread: http://www.excelforum.com/showthread...hreadid=492208

writing ADODB recordset to excel sheet
 

Hi,

You need to use the CopyFromrecordset method of the range object.

For example:

range("a1").copyfromrecordset rs

This will copy the first 65525 rows to the activesheet, then you need a loop
to continue the copy on a new sheet if there are more than 65536 rows in the
recordset.

HTH

Philip

Philip

"bhavesh78" wrote:


How can I write the data in record set to cells in excel? I have the
following code and the query always returns only ONE row. I want the
result in data set to be put on the excel sheet like say Column1 in
cell A1, column2 in cell A2 and so on...

CODE
---------------------------------


Dim ProdConnectString As String
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
' String used for connecting to DB
ProdConnectString = "provider=msdaora.1;user
id=USER;password=PASS;data source =
DATASOURCE;option=1+2+8+32+2048+16384"
conn.ConnectionString = ProdConnectString
conn.CursorLocation = adUseClient
conn.Open
mySQLCriTeria = "SELECT A.Col1, A.Col2, B.Col2, B.Col3 from TBL A,
TBL B where A.Col1=B.Col1"'"
rs.Open mySQLCriTeria, conn

---------------------------------

Thanks for all the help.

-BS


--
bhavesh78
------------------------------------------------------------------------
bhavesh78's Profile: http://www.excelforum.com/member.php...o&userid=17851
View this thread: http://www.excelforum.com/showthread...hreadid=492208

writing ADODB recordset to excel sheet
 

Hi,

You need to use the copyfromrecordset method of the range object.

e.g.

[a1].copyfromrecordset rs

or

range("a1").copyfromrecordset rs

If there are more than 65536 records in the recordset, you need to use a
loop and keep calling copyfromrecordset on a new sheet until the eof property
of the recordset is true.

HTH

Philip


"bhavesh78" wrote:


How can I write the data in record set to cells in excel? I have the
following code and the query always returns only ONE row. I want the
result in data set to be put on the excel sheet like say Column1 in
cell A1, column2 in cell A2 and so on...

CODE
---------------------------------


Dim ProdConnectString As String
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
' String used for connecting to DB
ProdConnectString = "provider=msdaora.1;user
id=USER;password=PASS;data source =
DATASOURCE;option=1+2+8+32+2048+16384"
conn.ConnectionString = ProdConnectString
conn.CursorLocation = adUseClient
conn.Open
mySQLCriTeria = "SELECT A.Col1, A.Col2, B.Col2, B.Col3 from TBL A,
TBL B where A.Col1=B.Col1"'"
rs.Open mySQLCriTeria, conn

---------------------------------

Thanks for all the help.

-BS


--
bhavesh78
------------------------------------------------------------------------
bhavesh78's Profile: http://www.excelforum.com/member.php...o&userid=17851
View this thread: http://www.excelforum.com/showthread...hreadid=492208