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 Data­base 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 fol­low the steps below

  1. Store data in your excel file and save it some loca­tion in your system.
  2. Admin­is­tra­tive Tools -> Data Sources (ODBC) -> Choose User DSN -> Choose ‘Excel Files’ from the list -> Press ‘Configure’
  3. Give the Data Source Name and descrip­tion.
  4. Click on the Select Work­Book
  5. Browse and select the Excel file which you have cre­ated at step 1 and click ok
  6. Data Souce will be vis­i­ble in the List


To Read about how to use this Data Source for read­ing the Data Click Here

 

Store data in your excel file and save it some loca­tion in your system.

Say that this is the cre­ated file with name “DB Data.xlsx”

NOTE: First line of the range of data will become the Col­umn Names of the Table.

Use Excel as ODBC Source Example

Use Excel as ODBC Source Example

Admin­is­tra­tive Tools -> Data Sources (ODBC) -> Choose User DSN -> Choose ‘Excel Files’ from the list-> Press ‘Configure’

Once you will click on Con­fig­ure but­ton the ‘Setup’ win­dow will be displayed.

 

ODBC Data Source Administrator

ODBC Data Source Administrator

 

Note: If you have 64 bit oper­at­ing sys­tem and 32 bit Microsoft Excel then you might get the Error “ODBC Dri­ver is not found”

Error

Error

In that case you Close the ‘ODBC Data Source Admin­is­tra­tor win­dow’ and open “c:\windows\sysWOW64\odbcad32.exe”

Ref­er­ence : 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 Admin­is­tra­tor Setup

Click on the Select Work­Book and Browse and select the Excel file which you have cre­ated at step 1

ODBC Data Source Administrator - Select WorkBook

ODBC Data Source Admin­is­tra­tor — Select WorkBook

Click Ok

ODBC Data Source Administrator Setup 2

ODBC Data Source Admin­is­tra­tor Setup 2

Data Souce will be vis­i­ble in the List

Created ODBC Source

Cre­ated ODBC Source

 

 

You may also like...

Leave a Reply

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

%d bloggers like this: