Excel in vb.net

  • Thread starter Thread starter ZR
  • Start date Start date
Z

ZR

Hi,

The following code used to work perfectly, this copies a recordset on to
excel (it saves you having to loop through all the recs.)

code : "WST.Range("A5").CopyFromRecordset(RsStyles)"

"WST" is an excel worksheet object, and "RsStyles" is a classic ADO
recordset.

This stopped working once I installed Office 2003, I cannot figure out why.
I
am getting the following error.

"Run-time exception thrown : System.InvalidCastException - No such interface
supported"

Can someone please help me. I am going crazy here.


Thanks in advance
ZR
 
ZR,
I'm not that familiar with the CopyFromRecordset but have you noticed that
in the Excel Documentation that the datatype is VOID? You might want to
explicitly reference the older version of the excel object library.

Dan
 
Hi Dan

That didn't work. originally I left the old references, and I got the same
error.

Thanks a lot for your help.
ZR
 
I got the follow to run without problem using ADO classic and Excel 11:

Const ConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\db.xls;" & _
"Extended Properties=Excel 8.0;" & _
"Persist Security Info=False"
Dim app As New Excel.Application
app.Visible = True
Dim wb As Excel.Workbook = app.Workbooks.Add()
Dim ws As Excel.Worksheet = CType(wb.Worksheets(1), Excel.Worksheet)
Dim Con As New ADODB.Connection
Con.Open(ConString)
Dim rs As ADODB.Recordset = Con.Execute("SELECT * FROM [Sheet1$]")
ws.Range("A1").CopyFromRecordset(rs)
rs.Close()
Con.Close()

--
 
Thanks a lot for your help.

Looks like its a bug, I am using an ado recordset , through an odbc
connection to a DB2 database on an AS400 OS.
Ther must be some bug along the way.

Thanks again

ZR



jamieuk said:
I got the follow to run without problem using ADO classic and Excel 11:

Const ConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\db.xls;" & _
"Extended Properties=Excel 8.0;" & _
"Persist Security Info=False"
Dim app As New Excel.Application
app.Visible = True
Dim wb As Excel.Workbook = app.Workbooks.Add()
Dim ws As Excel.Worksheet = CType(wb.Worksheets(1), Excel.Worksheet)
Dim Con As New ADODB.Connection
Con.Open(ConString)
Dim rs As ADODB.Recordset = Con.Execute("SELECT * FROM [Sheet1$]")
ws.Range("A1").CopyFromRecordset(rs)
rs.Close()
Con.Close()

--

"ZR" <[email protected]> wrote in message
Hi Dan

That didn't work. originally I left the old references, and I got the same
error.

Thanks a lot for your help.
ZR

on
to out
why.
Click to expand...
Click to expand...
 
Back
Top