Excel VBA using ADO to extract data from SQL Server 
Author Message
 Excel VBA using ADO to extract data from SQL Server

Can someone help me on the following piece of code?  I am stucked at the
statement "ActiveCell.CopyFromRecordSet objRst", it won't run (error code
430: Class doesn't support automation).

Sub adoGetSQLData()
    Dim objConn As New ADODB.Connection
    Dim objRst As New ADODB.Recordset
    Dim objErr As ADODB.Error

    objConn.Open "DSN=hourlydata;UID=public;PWD=publicdata"

    objRst.Open _
        "SELECT site_id, rd_date FROM dbo.table_data1 WHERE
site_id='CA0001'", _
        objConn

    Sheets("KU4").Activate
    Range("A2").Select
    Application.ScreenUpdating = True
    ActiveCell.CopyFromRecordset objRst

    objRst.Close
    objConn.Close
    Set objRst = Nothing
    Set objConn = Nothing
End Sub

Thanks



Sat, 01 Feb 2003 10:52:31 GMT  
 Excel VBA using ADO to extract data from SQL Server

Can someone help me on the following piece of code?  I am stucked at the
statement "ActiveCell.CopyFromRecordSet objRst", it won't run (error code
430: Class doesn't support automation).

Sub adoGetSQLData()
    Dim objConn As New ADODB.Connection
    Dim objRst As New ADODB.Recordset
    Dim objErr As ADODB.Error

    objConn.Open "DSN=hourlydata;UID=public;PWD=publicdata"

    objRst.Open _
        "SELECT site_id, rd_date FROM dbo.table_data1 WHERE
site_id='CA0001'", _
        objConn

    Sheets("KU4").Activate
    Range("A2").Select
    Application.ScreenUpdating = True
    ActiveCell.CopyFromRecordset objRst

    objRst.Close
    objConn.Close
    Set objRst = Nothing
    Set objConn = Nothing
End Sub

I believe that CopyFromRecordset only supports DAO Recordsets.


Microsoft MVP (Visual Basic)



Sun, 02 Feb 2003 03:00:00 GMT  
 Excel VBA using ADO to extract data from SQL Server

Thanks, anyway I found out myself that CopyFromRecordSet for ADO is only
supported in Excel 2000.  For Excel 97, only DAO supports CopyFromRecordSet.


Quote:

> Can someone help me on the following piece of code?  I am stucked at the
> statement "ActiveCell.CopyFromRecordSet objRst", it won't run (error
code
> 430: Class doesn't support automation).
>
> Sub adoGetSQLData()
>     Dim objConn As New ADODB.Connection
>     Dim objRst As New ADODB.Recordset
>     Dim objErr As ADODB.Error
>
>     objConn.Open "DSN=hourlydata;UID=public;PWD=publicdata"
>
>     objRst.Open _
>         "SELECT site_id, rd_date FROM dbo.table_data1 WHERE
> site_id='CA0001'", _
>         objConn
>
>     Sheets("KU4").Activate
>     Range("A2").Select
>     Application.ScreenUpdating = True
>     ActiveCell.CopyFromRecordset objRst
>
>     objRst.Close
>     objConn.Close
>     Set objRst = Nothing
>     Set objConn = Nothing
> End Sub

> I believe that CopyFromRecordset only supports DAO Recordsets.


> Microsoft MVP (Visual Basic)



Tue, 04 Feb 2003 14:16:45 GMT  
 Excel VBA using ADO to extract data from SQL Server
Logon to MSDN and locate document Q246335, "HOWTO: Transfer data from an ADO
Recordset to Excel". The accompanying code shows how to do this using Excel
2000 *and* Excel 97. I've only tried the Excel 2000 part and can say the
CopyFromRecordset method is so fast it's almost scary <g>.

Another document you might want to have a look at is Q247412, "INFO: Methods
for Transferring Data to Excel from Visual Basic".

HTH


Quote:
> Thanks, anyway I found out myself that CopyFromRecordSet for ADO is only
> supported in Excel 2000.  For Excel 97, only DAO supports

CopyFromRecordSet.


Tue, 04 Feb 2003 03:00:00 GMT  
 Excel VBA using ADO to extract data from SQL Server
Refer to this page
http://support.microsoft.com/support/OfficeDev/vbasic.asp?LN=EN-US&SD...
0 for more information.


Quote:
> Logon to MSDN and locate document Q246335, "HOWTO: Transfer data from an
ADO
> Recordset to Excel". The accompanying code shows how to do this using
Excel
> 2000 *and* Excel 97. I've only tried the Excel 2000 part and can say the
> CopyFromRecordset method is so fast it's almost scary <g>.

> Another document you might want to have a look at is Q247412, "INFO:
Methods
> for Transferring Data to Excel from Visual Basic".

> HTH



Fri, 07 Feb 2003 08:42:02 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Excel 2002 hanging when using ADO (vba) + SQL Server 2000

2. Using ADO to export from SQL server to Excel

3. How to get SQL data from Server into Excel on Client using ASP

4. extracting data from an excel spreadsheet into vba code

5. copy data between sql servers using ADO VB visual basic

6. copy data between sql servers using ADO VB visual basic

7. Bug using ADO and data combo against SQL Server

8. HELP: Extracting data from remote oracle server to excel

9. extract data from Excel file with ADO

10. Extracting data from HTML documents using Excel

11. Linking or Extracting .mpp data for using VBA ASP.Net

12. ADO, SQL Server, VB problem posted to the microsoft.public.data.ado group as well

 

 
Powered by phpBB® Forum Software