How to read an Excel spreadsheet using ADODB

Reverend Jim 1 Tallied Votes 4K Views Share

If your Excel spreadsheet is laid out as regular columns and rows with a header row that identifies the columns then this code will allow you to read the data using ADODB the same way you would read records from a database table. To try this code

  1. Create a new project
  2. Add a ListBox control named ListBox1
  3. Add a project reference to adodb
  4. Replace the project code with the snippet code

To create a test Excel spreadsheet just download the attached and unzip it. The file, test.xls contains a table of authors, books and a few extra columns. Make sure to modify the string, file to reflect the location of where you saved test.xls.

If your spreadsheet does not contain a header row then you can use the following

con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.ConnectionString = "Data Source=" & file & ";Extended Properties=""Excel 8.0;HDR=NO"""

Note the use of double-double quotes for the Extended Properties in order to account for two extended properties separated by a semicolon. You will also notice in your output that if you do not use a header row, your field names will be F1, F2, etc.

'   Name:
'       Excel with ADODB
'   Description:
'       This sample shows how to read data from an Excel spreadsheet using ADODB.
'   Notes:
'       This code requires a ListBox named ListBox1 (I have it docked to fill the
'       form) and a reference to adodb. You can find this under the .NET tab when
'       you go to PROJECT -> ADD REFERENCE from the menu.
'       The spreadsheet in my exammple has the default name. The first row contains
'       the column names.
'       For more details please see
'   Audit:
'       2014-03-09  rj  original code

Public Class Form1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Dim file As String = "D:\temp\test.xls"

        Dim con As New ADODB.Connection
        Dim rec As New ADODB.Recordset

        con.Provider = "MSDASQL"
        con.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & file & ";"

        Dim qry As String = "SELECT * FROM [Sheet1$]"
        rec.Open(qry, con, ADODB.CursorTypeEnum.adOpenStatic)

        Dim recnum As Integer = 1

        Do Until rec.EOF

            Display("Record " & recnum)

            For i As Integer = 0 To rec.Fields.Count - 1
                Display(vbTab & rec.Fields(i).Name & " = " & rec.Fields(i).Value)

            recnum += 1



    End Sub

    Private Sub Display(text As String)
    End Sub

End Class
oussama_1 39 Posting Whiz in Training

is it required to add a microsoft excel object library reference here ?

Reverend Jim 4,780 Hi, I'm Jim, one of DaniWeb's moderators. Moderator Featured Poster

It is not required.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.