1

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.

Edited by Reverend Jim

Attachments
'
'   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

Computer languages in which I have developed applications

Assembler (DEC, Data General, 8080, GE, SEL, IBM 360)
WATFOR (Waterloo FORTRAN)
FORTRAN (SEL)
APL (IBM 360, IBM VSAPL)
PL/1
C/C++
Borland Paradox
VB.net
vbScript

2
Contributors
2
Replies
44
Views
3 Years
Discussion Span
Last Post by Reverend Jim
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.