How to read an Excel spreadsheet using ADODB

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