Retrieve records in an Excel Sheet with VB.NET and OLE DB (Object Linking and Embedding Database)
You can access an Excel file as a database and read (or update) the contents with VB.NET by using SQL statements or reading the whole file into a VB.NET DataSet for further processing.
1. Add two references to your .NET project:
2. Define some variables in your VB.NET method:
3. Check if the file can be accessed:
Then you can read in the data from the Excel file into a new DataSet:
In this case you can access the columns e.g. by index number. We recommend using a mapping to an existing DataSet (called DataSet1 in this example):
You should also set the DataType to System.Object so the OLE DB interface is not trying to guess the data type.
Now we can close the connection:
7. Finally you can access the data in the DataSet: