Basic Exel?

I have an exel file thats looks like this

A B
Bill bill@hotmail.com

Every row is a new record. I would like to be able to extract someones name and email from colum A and B and put it into a list box. Any ideas?

Thanks so much

I would probably create a dataset that is populated using the OLEDBDataAdapter. This Microsoft Article (http://support.microsoft.com/kb/311731) looks pretty good. Once the dataset is created you can bind it directly to your List Box.

I think the sample uses a datagrid, but the principles are the same.

doesnt show how to do this, just shows about aspx forms!

Yes, most of the links originating from that URL seem to show how to READ a database and place the values within Excel. I'm a bit unsure how to USE Excel as the source within ADO.Net, but I'm sure that the correct (onnection String should do the trick...

I don't have the time to kick this around right now, but I'll try to get to this tomorrow if I can. In the mean-time, hopefully someone else will be able to help out.

I would suggest a solution using Automation of Excel, but for your purposes (merely reading off the data) this would likely create compatiblity issues depending on which version of Excel is installed (Excel '97 through Excel 2003), whereas using ADO.Net would not have this problem.

So, hopefully someone else will drop in and be able to give a suggestion... I'll stop by again myself to give this a try when I can.

Ok, my bad... that link that Wayneph provided actually looks excellent. You just need to ignore the parts that are Web-Application specific.

I don't have time to kick this around myself, but more or less, you want to do steps 1-7 and then create a new Windows Application in Visual Studio and then skip down to step 11. Then step 12 is the key, using ADO.Net code using Excel as the Database.

Step 12 also seems to imply that you'll need to add a DataGrid to your Windows Form. But you could also pull out individual pieces of data from the objDataset object one at a time and use that...

I can't test this myself right now, but that code looks like a very good start.

I have asked this question on several other forums but no responce :S

How can i modify this code to put everything in colums A1 and A2 into a datagrid.

Dim myDataset As New DataSet()

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\query_Excel.xls;" & _
"Extended Properties=""Excel 8.0;"""

''You must use the $ after the object you reference in the spreadsheet
Dim myData As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
Dim names As String = myData.ToString
myData.TableMappings.Add("Table", "ExcelTest")
myData.Fill(myDataset)
DataGrid1.DataSource = myDataset.Tables(0).DefaultView

Threads merged.

Did you take a look at that Microsoft Article (http://support.microsoft.com/kb/311731) that Wayneph provided for you?

Particularly, I would pay attention to steps 1-7 and then create a new Windows Application in Visual Studio and then skip down to step 11. Then step 12 is the key, using ADO.Net code using Excel as the Database.

i looked at it but to no success! I get blue underlined exeptions all over the place!

And te SQL style querys like select A1 from [workbook1] dont work:S:S

Stuart

Ok, well I gave it a shot:Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Create variables that are used in code sample.
Dim wbPath As String
Dim i, j As Integer

' Create connection string variable. Modify the "Data Source" parameter as
' appropriate for your environment.
wbPath = "C:\Documents and Settings\Owner\My Documents\ExcelData.xls"
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & wbPath _
& ";" & "Extended Properties=Excel 8.0;"

' Create the connection object by using the preceding connection string.
Dim objConn As New OleDbConnection(sConnectionString)

' Open connection with the database.
objConn.Open()

' The code to follow uses a SQL SELECT command to display the data from the worksheet.

' Create new OleDbCommand to return data from worksheet.
Dim objCmdSelect As New OleDbCommand("SELECT * FROM myRange1", objConn)

' Create new OleDbDataAdapter that is used to build a DataSet
' based on the preceding SQL SELECT statement.
Dim objAdapter1 As New OleDbDataAdapter

' Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect

' Create new DataSet to hold information from the worksheet.
Dim objDataset1 As New DataSet

' Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData")

' Build a table from the original data.
DataGrid1.DataSource = objDataset1.Tables(0).DefaultView
' DataGrid1.DataBind() ' <-- Commented Out.

' Clean up objects.
objConn.Close()
End Sub And it works fine. Note that I had to change how they set up their path from a Web-based Path to a folder based path, so I used this: wbPath = "C:\Documents and Settings\Owner\My Documents\ExcelData.xls" which became part of the connection string.

I also had to comment out the line:DataGrid1.DataBind() for this seems to exist only for Web Forms and not for DataGrid controls on Windows Forms. (The compiler complained as such anyway.)

Hope this helps...

Mike

I get An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

Is this the line that causing the error?

objAdapter1.Fill(objDataset1, "XLData")


If so, make sure that the name of your Worksheet is myRange1 or if you have define a name for your selection make sure you named it myRange1 otherwise you will need to change myRange1 to the name of your worksheet or to the name of your defined selection. It generates error because there's no information that has been retrieved because myRange1 does not exists in your Excel workbook.

so this line

Dim objCmdSelect As New OleDbCommand("SELECT * FROM myRange1", objConn)

would be replaced with

Dim objCmdSelect As New OleDbCommand("SELECT * FROM WorksheetName", objConn)

or with

Dim objCmdSelect As New OleDbCommand("SELECT * FROM RangeName", objConn)


and also make sure that your path and file are correct.

Mike_R example was completely based on the example given from the link and the Excel file in his example was customized based on the instruction provided in the example.

Hey, thanks Duane, I think you likely hit both issues on the head: (1) the worrect File Path and (2) the "myRange1" RangeName.

Stuart, I re-worked the example a little, adding an ExcelData.xls workbook and changing the code so that it should be able to find the correct wbPath regardless of where to where you download this folder: wbPath = Application.StartupPath
Dim iPos As Integer = wbPath.LastIndexOf("\bin")
If iPos > 0 Then wbPath = wbPath.Substring(0, iPos)
wbPath += "\ExcelData.xls" The expectation here is that when you run this, the EXE will be created in the "..\bin" directory. I think it should work. Give it a try. :)