Error on .CopyFromRecordset

[Colvic]

I get an error on this code line: xlWs.Cells(2, 1).CopyFromRecordset(rst)
The error is:
An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll
Additional information: No such interface supported


Can someone help me?

Thanks.

Using :
Microsoft Visual Basic .NET 2003
Microsoft Excel 2003

In the top off Module:
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports ADODB


This code is based on:
How To Transfer Data from an ADO Recordset to Excel with Automation

http://support.microsoft.com/kb/246335

Reference is set to:
Microsoft ActiveX Data Objects 2.8 Library.
Microsoft Excel 11.0 Objects Library


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object

Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer

Dim strDB As String
' Set the string to the path of your Northwind database
strDB = "c:\program files\Microsoft office\office11\samples\Northwind.mdb"

' Open connection to the database
cnt.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";")

' Open recordset based on Orders table
rst.Open("Select * From Orders", cnt)

' Create an instance of Excel and add a workbook
xlApp = CreateObject("Excel.Application")
xlWb = xlApp.Workbooks.Add
xlWs = xlWb.Worksheets("Sheet1")

'' Display Excel and give user control of Excel's lifetime
'xlApp.Visible = True
'xlApp.UserControl = True

' Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next

' Copy the recordset to the worksheet, starting in cell A2

xlWs.Cells(2, 1).CopyFromRecordset(rst)

'Note: CopyFromRecordset will fail if the recordset
'contains an OLE object field or array data such
'as hierarchical recordsets

' Auto-fit the column widths and row heights
xlApp.Selection.CurrentRegion.Columns.AutoFit()
xlApp.Selection.CurrentRegion.Rows.AutoFit()

' Display Excel and give user control of Excel's lifetime
xlApp.Visible = True
xlApp.UserControl = True

' Close ADO objects
rst.Close()
cnt.Close()
rst = Nothing
cnt = Nothing

' Release Excel references
xlWs = Nothing
xlWb = Nothing

xlApp = Nothing

End Sub

 

[ca8msm]

Using CopyFromRecordset

For efficiency and performance, CopyFromRecordset is the preferred method. Because Excel 97 supports only DAO recordsets with CopyFromRecordset, if you attempt to pass an ADO recordset to CopyFromRecordset with Excel 97, you receive the following error:
Run-time error 430:
Class does not support Automation or does not support expected interface.

That article has the above section that looks fairly similar to the error you are getting. Could it be related?

Also, that article is actually for VB6 not VB.NET.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.