VBA-Excel: Make Excel File as ODBC Source(Database) using Microsoft Excel Driver

To use your Excel file as Database for your project, First you might need to make your excel file as ODBC source using Microsoft Excel Driver.

In order to make your excel file ODBC Data Source, please follow the steps below

1. Store data in your excel file and save it some location in your system.
2. Administrative Tools -> Data Sources (ODBC) -> Choose User DSN -> Choose 'Excel Files' from the list -> Press 'Configure'
3. Give the Data Source Name and description.
4. Click on the Select WorkBook
5. Browse and select the Excel file which you have created at step 1 and click ok
6. Data Souce will be visible in the List

To Read about how to use this Data Source for reading the Data Click Here

Store data in your excel file and save it some location in your system.

Say that this is the created file with name “DB Data.xlsx”

NOTE: First line of the range of data will become the Column Names of the Table.

Administrative Tools -> Data Sources (ODBC) -> Choose User DSN -> Choose 'Excel Files' from the list-> Press 'Configure'

Once you will click on Configure button the ‘Setup’ window will be displayed.

Note: If you have 64 bit operating system and 32 bit Microsoft Excel then you might get the Error “ODBC Driver is not found”

In that case you Close the ‘ODBC Data Source Administrator window’ and open “c:\windows\sysWOW64\odbcad32.exe”

Reference : http://answers.microsoft.com/en-us/office/forum/office_2007-excel/no-odbc-drivers-available-for-excel-or-access-in/001c234b-dfd5-4378-a325-c4f1482fb6fd

Give the Data Source Name and description.

Click on the Select WorkBook and Browse and select the Excel file which you have created at step 1

Click Ok

Data Souce will be visible in the List