How to read an Excel spreadsheet using ADODB

Reverend Jim

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 test.zip 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.

2,910 Views
About the Author

I completed my Computer Science degree at the University of Manitoba in 1976. I did two and a half years of programming in medical research followed by twenty-nine years at Manitoba Hydro (electric utility). Most of that was spent on doing development and maintenance on an AGC/SCADA (real-time programming/process control) system. The last ten years of that was spent doing application and infrastructure support and development. I have programmed in FORTRAN (mostly), APL, PL/1, COBOL, Lisp, SNOBOL, ALGOL, Assembler (several flavours), C, C++, Paradox, VB, vbScript and more recently, Python. I am married with two grown children of whom I am very proud, and a most beautiful wife. I am currently retired (and loving it).

'
'   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 http://support.microsoft.com/kb/257819
'
'   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 & ";"
        con.Open()

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

        Dim recnum As Integer = 1

        Do Until rec.EOF

            Display("")
            Display("Record " & recnum)
            Display("")

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

            rec.MoveNext()
            recnum += 1

        Loop

        rec.Close()
        con.Close()

    End Sub

    Private Sub Display(text As String)
        ListBox1.Items.Add(text)
    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 3,430 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 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.