How to Read Excel Rows in VB.NET

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Read Excel Rows in VB.NET
Use LINQ to query a data set in Visual Basic. (Thinkstock Images/Comstock/Getty Images)

Knowing how to read Microsoft Excel rows from your Visual Basic.NET application can make your application more flexible. In VB, you can create SQL strings and query Excel spreadsheets based on criteria to avoid reading the entire sheet. This is accomplished through the use of a data adaptor and the data set object. You can use LINQ to manage the data returned and manipulate one row at a time in the data set.

Skill level:


  1. 1

    Launch Microsoft Excel and type "Name" in cell "A1," "Age" in "B1," "John" in "A2," "10" in "B2," "Susan" in "A3," "15" in "B3," "Tom" in "A4," "10" in "B4," "Bob" in "A5" and "10" in "B5." Save the spreadsheet in "C:\" as "ExcelFile.xlsx." Close Excel.

  2. 2

    Launch Microsoft Visual Basic.NET and click "New Project" from the left pane of your screen to launch the "New Project" dialogue window. Click "Windows Forms Application" and click "OK" to start a new project.

  3. 3

    Double-click "ListBox" from the "Toolbox" pane to add a new list box control to the form. Add a new button using the same technique.

  4. 4

    Double-click the form to create a form load event. Add the following code to define the path of the Excel spreadsheet you want to read and create the object variables you will use to read the file:

    Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\ExcelFile.xlsx;Extended Properties=Excel 12.0;"

        Dim my Connection As OleDbConnection = New OleDbConnection
        Dim ds As DataSet
        Dim da As OleDbDataAdapter
        Dim strSQL As String
  5. 5

    Define the SQL string to query rows where "Age" is equal to 10 and read the Excel spreadsheet using a data adaptor and a data set.

        strSQL = "SELECT NAME, AGE FROM [Sheet 1$] WHERE Age = 10"
        myConnection.ConnectionString = connString
        da = New OleDbDataAdapter(strSQL, myConnection)
        ds = New DataSet
        da.Fill(ds, "dataTable")
  6. 6

    Read the data returned from the data set and store it in the "ExcelRows" global array for future use.

        Dim excel Data = ds.Tables("dataTable").AsEnumerable()
        Dim row Query = From p In excel Data _
                        Select p!NAME, p!AGE
        rowCntReturned = rowQuery.Count
        ExcelRows = rowQuery.ToArray
  7. 7

    Add the following global variables above the "Form1_Load" event:

    Dim ExcelRows

    Dim rowCntr As Integer
    Dim rowCntReturned As Integer
  8. 8

    Switch back to the form design and double-click the button to create a click event. Add the following code to add one row at a time to the list box control.

    If (rowCntr < rowCntReturned) Then

            rowCntr += 1
        End If
  9. 9

    Press "F5" to run the program and click "Button1" to add the first row of the spreadsheet. Click the button again to add the second row and click it one last time to add the third row to the list box control.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.