Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access 2003: transferspreadsheet writes over existing spreadsheet | Excel Discussion (Misc queries) | |||
access a website from excel 2003 spreadsheet | New Users to Excel | |||
Programatically access Excel's Solver? | Excel Programming | |||
how to access programatically added controls | Excel Programming | |||
Programatically saving XML file as Excel spreadsheet | Excel Programming |