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

 

[JerryKlmns]

I think this
rst.Open("Select * From Orders", cnt)
should be
rst.Open "Select * From Orders", cnt

 

[Colvic]

Hi,

VB change this:
rst.Open "Select * From Orders", cnt

to

rst.Open("Select * From Orders", cnt)

If I remarks this sentence: xlWs.Cells(2, 1).CopyFromRecordset(rst)

It will write the field name from the Tables to the Excel.

 

[johnwm]

Read faq222-2244 for guidance on forum usage. You will get better answers to VB.NET questions in the VB.NET forum, forum796

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller