Programatically access Excel 2003 spreadsheet
 

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 ?

Programatically access Excel 2003 spreadsheet
 

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" wrote in message
...
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
?

Programatically access Excel 2003 spreadsheet
 

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 ?

Programatically access Excel 2003 spreadsheet
 

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


Regards

JY

"Edward Diener" wrote in message
...
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 ?