Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

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

 

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: