My Blog

Open Excel File and Read Data using ACE

In continuation from here

Now that we have learned how to create an Excel File using Ace and write data to it, let’s now try and read from it. We will read the data from the file that we created in the previous section

Let’s create a button in the form that we created and name it Open.

And now we are ready to code.

Creating our connection string

To open an file we simply provide the name of a existent file in the connection string. This will open the file.

Private Const connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Book1.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

Command for reading data from the Excel File

To open a table in an Excel workbook, run the SELECT command. The syntax of the command is

SELECT * From [Sheet1$]

Where Sheet1 is the name of the worksheet. Now either you can read data from the entire sheet or you can read a specific range from a worksheet.

To read data from the entire sheet, you have to use

SELECT * From [Sheet1$]

and to read from a specific range, say A1:F10, you have to use

SELECT * From [Sheet1$A1:F10]

If you are not sure of the last row in Column F then you can also use the below

SELECT * From [Sheet1$A1:F]

This is how our final code will look like

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim olecon As OleDbConnection = New OleDbConnection
        Dim olecmd As OleDbCommand = New OleDbCommand
        Dim FilePath As String = "C:\"
        Dim FileName As String = "Book1.xlsx"

        '~~> Construct your connection string
        Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                   "Data Source=" & FilePath & FileName & ";" &
                                   "Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

        olecon.ConnectionString = connstring

        olecmd.Connection = olecon

        '~~> Command to open and read the file
        olecmd.CommandText = "Select * From [Sheet1$]"
        'olecmd.CommandText = "Select * From [Sheet1$A1:F10]"
        'olecmd.CommandText = "Select * From [Sheet1$A1:F]"

        Dim rdr As OleDbDataReader = olecmd.ExecuteReader

        '~~> Since we have specified HDR=YES in the connection string, the data will loop from
        '~~> Row 2 onwards
        Do While rdr.Read()
            Debug.WriteLine(System.String.Format("{0, -10}{1,-20}{2,-20}{3, -15:dd/MM/yyyy}{4,-10:F2}{5,-10:F2}", _

        '~~> Close the connection 
    End Sub

Now you are ready to run your project.

When executed, the program will read the data from the Excel file and display it in the output window as shown below.