Help Please

[NewVBguy]

Hi There,
Can anybody help me with the following:

1. I am trying to open an excel file using adodb connection but i got a run time error on the rs. open. Here is my code

Dim cnn As New ADODB.Connection, rs As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\XLS.XLS;Extended Properties=Excel 8.0;"
rs.Open "select * from data", cnn, adOpenStatic, adLockOptimistic, adCmdText

2. Can somebody show how to insert or delete or add a column of a recordset on the fly or at run time.

Many thanks.

Newvbguy

[Comatose]

Firstly, I don't believe Excel works with an ADO connection. You can create an excel object, and work with it, but as far as I know, SQL (Structured Query Languages), and Access (which are database systems) are the ones you can interface with using ADO. Did you read the sticky thread?

[NewVBguy]

Hi Comatose,
I just thought it will work because there is no error on opening the connection but just in that opening the recordset that I got the error. I haven't read yet the sticky one but i will try.

thnkx for more....

newvbguy

[NewVBguy]

Hi,
I already figured out how this connection work. Here is the code:

Dim cnn As New ADODB.Connection, rs As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\XLS.XLS;Extended Properties=Excel 8.0;"
rs.Open "select * from data", cnn, adOpenStatic, adLockOptimistic

I am also working with #2 but I'm still getting an error with this line.

rs.fields.append "fldname", adchar, 20

can anybody know why?

Many thanks.

Newvbguy

[Comatose]

Hmn, so you can connect to an excel spreadsheet using ado? I'll be damned, who what have known?

http://support.microsoft.com/default.aspx?scid=kb;EN-US;257819

[maheshsayani]

Here Is the sample code

The Mistake is for Rs.open there should be only 4 arguments

Dim cnn As New ADODB.Connection, rs As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\XLS.XLS;Extended Properties=Excel 8.0;"
rs.Open "select * from data", cnn, adOpenStatic, adLockOptimistic