Be the first user to complete this post

  • 0
Add to List

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.

Use Excel as ODBC Source Example
Use Excel as ODBC Source Example

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.

ODBC Data Source Administrator
ODBC Data Source Administrator

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

Error
Error

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.

ODBC Data Source Administrator Setup
ODBC Data Source Administrator Setup

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

ODBC Data Source Administrator - Select WorkBook
ODBC Data Source Administrator - Select WorkBook

Click Ok

ODBC Data Source Administrator Setup 2
ODBC Data Source Administrator Setup 2

Data Souce will be visible in the List

Created ODBC Source
Created ODBC Source