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
- Create a new project
- Add a ListBox control named ListBox1
- Add a project reference to adodb
- 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.