Programatically access Excel 2003 spreadsheet

[Edward Diener]

I remember in the past Microsoft distributing an Active library which
would allow a developer to access the various elements of an Excel
spreadsheet. I have Excel 2003 on my computer and yet I do not see such
an ActiveX library listed when I try to import such a library into
Visual Studio .net. Does such an ActiveX library still exist for Excel
or, better yet, a .net asssembly, which allows one to extract data from
an Excel 2003 spreadsheet ?

[Jean-Yves]

Hello Edward,

ou can inddedd use Microsoft activeX Data Oject 2.x library ( ADO)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim X As Integer,
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With

rs.Open "Select * from [MyWorksheet$] ;", cn, adOpenDynamic, adLockReadOnly
'copy every thing
Range("A1").copyfromrecordset rs
'or get the column names
For x = 0 To rs.Fields.Count - 1
Debug.Print rs(x).Name
Next x

'write the data per line
x=0
do while rs.eof=false
range("A1").offset(x,0) = rs.fields("MyCol_Head").value
rs.MoveNext
x=x+1
loop


rs.close
cn.close
set rs=nothing
set cn=nothing

Regards
Jean-Yves

"Edward Diener" <ediener@no_spam_incomm.com> wrote in message
news:u9Hxi%[email protected]...
>I remember in the past Microsoft distributing an Active library which would
>allow a developer to access the various elements of an Excel spreadsheet. I
>have Excel 2003 on my computer and yet I do not see such an ActiveX library
>listed when I try to import such a library into Visual Studio .net. Does
>such an ActiveX library still exist for Excel or, better yet, a .net
>asssembly, which allows one to extract data from an Excel 2003 spreadsheet
>?

[Edward Diener]

Jean-Yves wrote:
> Hello Edward,
>
> ou can inddedd use Microsoft activeX Data Oject 2.x library ( ADO)
>
> Dim cn As ADODB.Connection
> Dim rs As ADODB.Recordset
> Dim X As Integer,
> Set cn = New ADODB.Connection
> Set rs = New ADODB.Recordset
>
> With cn
> .Provider = "Microsoft.Jet.OLEDB.4.0"
> .ConnectionString = "Data Source=C:\MyWorkbook.xls;" & _
> "Extended Properties=Excel 8.0;"
> .Open
> End With
>
> rs.Open "Select * from [MyWorksheet$] ;", cn, adOpenDynamic, adLockReadOnly
> 'copy every thing
> Range("A1").copyfromrecordset rs
> 'or get the column names
> For x = 0 To rs.Fields.Count - 1
> Debug.Print rs(x).Name
> Next x
>
> 'write the data per line
> x=0
> do while rs.eof=false
> range("A1").offset(x,0) = rs.fields("MyCol_Head").value
> rs.MoveNext
> x=x+1
> loop
>
>
> rs.close
> cn.close
> set rs=nothing
> set cn=nothing

Thanks ! Is there any documentation about accessing Excel as an ADO ( or
ADO .net ) data source anywhere ?

[Jean-Yves]

Micosoft support
Help files on ADO (connection string) in VBA
Goole !


Regards

JY

"Edward Diener" <ediener@no_spam_incomm.com> wrote in message
news:[email protected]...
> Jean-Yves wrote:
>> Hello Edward,
>>
>> ou can inddedd use Microsoft activeX Data Oject 2.x library ( ADO)
>>
>> Dim cn As ADODB.Connection
>> Dim rs As ADODB.Recordset
>> Dim X As Integer,
>> Set cn = New ADODB.Connection
>> Set rs = New ADODB.Recordset
>>
>> With cn
>> .Provider = "Microsoft.Jet.OLEDB.4.0"
>> .ConnectionString = "Data Source=C:\MyWorkbook.xls;" & _
>> "Extended Properties=Excel 8.0;"
>> .Open
>> End With
>>
>> rs.Open "Select * from [MyWorksheet$] ;", cn, adOpenDynamic,
>> adLockReadOnly
>> 'copy every thing
>> Range("A1").copyfromrecordset rs
>> 'or get the column names
>> For x = 0 To rs.Fields.Count - 1
>> Debug.Print rs(x).Name
>> Next x
>>
>> 'write the data per line
>> x=0
>> do while rs.eof=false
>> range("A1").offset(x,0) = rs.fields("MyCol_Head").value
>> rs.MoveNext
>> x=x+1
>> loop
>>
>>
>> rs.close
>> cn.close
>> set rs=nothing
>> set cn=nothing
>
> Thanks ! Is there any documentation about accessing Excel as an ADO ( or
> ADO .net ) data source anywhere ?