ODBC Source

You can read an Excel WorkBook as a complete DataBase, means an Excel file can act as Database. You can make range of Rows and Columns as the Tables of your Database which means Excel WorkBook.

This is one of the very crucial feature excel provides because imagine if you have an excel file contains very huge data, say more than 10000X500 cells filled with data and you have to fetch some data from the entire workbook based on some conditions. Yes, you can navigate the entire WorkSheet row wise or column wise but it will take significant amount of time to navigate through each cell. That’s why we have Database and Table concept right.

Read Excel WorkBook as DataBase follows the steps below

1. Store data in your excel file and save it some location in your system, say “DB Data.xlsx”
2. Make Excel File as ODBC Source using Microsoft Excel Driver (give the name as SumitODBC)
3. Open a new excel file in which you will fetch the data from “DB Data.xlsx”
4. Open the VB Editor
5. Create a “ADODB.Connection” Object

Read moreVBA-Excel: Read Excel WorkBook as DataBase using ODBC Source

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

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