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

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

You can read an Excel Work­Book as a com­plete Data­Base, means an Excel file can act as Data­base. You can make range of Rows and Columns as the Tables of your Data­base which means Excel WorkBook.

This is one of the very cru­cial fea­ture excel pro­vides because imag­ine if you have an excel file con­tains very huge data, say more than 10000X500 cells filled with data and you have to fetch some data from the entire work­book based on some con­di­tions. Yes, you can nav­i­gate the entire Work­Sheet row wise or col­umn wise but it will take sig­nif­i­cant amount of time to nav­i­gate through each cell. That’s why we have Data­base and Table con­cept right.

Read Excel Work­Book as Data­Base fol­lows the steps below

    1. Store data in your excel file and save it some loca­tion in your sys­tem, say “DB Data.xlsx”
    2. Make Excel File as ODBC Source using Microsoft Excel Dri­ver (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. Cre­ate a “ADODB.Connection” Object

  1. Open the con­nec­tion and pro­vide the DSN name as the same name pro­vided in step 2.(In this caseit is “Sum­i­tODBC”)
  2. Write the query for fetch­ing the data, pro­vide the table name as the range from the “DB Data.xlsx
  3. Exe­cute the query and store the result in result­Set
  4. Read the data from the result­Set and dis­play in the newly cre­ated excel and close the resultset.
  5. Cre­ate a but­ton on newly cre­ated excel and assign the macro func­tion to it.

 

  • Store data in your excel file and save it some loca­tion in your sys­tem, say “DB Data.xlsx”
Use Excel as ODBC Source Example

Use Excel as ODBC Source Example

Set Con­nec­tion = CreateObject(“ADODB.Connection”)

  • Open the con­nec­tion and pro­vide the DSN name as the same name pro­vided in step 2.(In this caseit is “SumitODBC”)

                    Open “DSN=SumitODBC”

  • Write the query for fetch­ing the data; pro­vide the table name as the range from the “DB Data.xlsx”

str­Query = “SELECT * FROM [Sheet1$A1:Z500] where Dept = ‘IT

  • Exe­cute the query and store the result in resultSet

Set result­Set = Con­nec­tion.Exe­cute(str­Query)

  • Read the data from the result­Set and dis­play in the newly cre­ated excel and close the resultSet
Do While Not resultSet.EOF

intRowCounter = intRowCounter + 1

mainWorkBook.Sheets("Sheet2").Range("B" & intRowCounter).Value = resultSet.Fields("Name").Value

resultSet.movenext

Loop

resultSet.Close

Com­plete Code:

Sub ReadDB()
Dim mainWorkBook As Workbook
Dim intRowCounter
Set mainWorkBook = ActiveWorkbook
intRowCounter = 2
mainWorkBook.Sheets("Sheet2").Range("A2:Z100").Clear
Set Connection = CreateObject("ADODB.Connection")
Connection.Open "DSN=SumitODBC"
strQuery = "SELECT * FROM [Sheet1$A1:Z500] where Dept = 'IT'"
Set resultSet = Connection.Execute(strQuery)
Do While Not resultSet.EOF
    mainWorkBook.Sheets("Sheet2").Range("A" & intRowCounter).Value = resultSet.Fields("Emp Id").Value
    mainWorkBook.Sheets("Sheet2").Range("B" & intRowCounter).Value = resultSet.Fields("Name").Value
    mainWorkBook.Sheets("Sheet2").Range("C" & intRowCounter).Value = resultSet.Fields("Age").Value
    mainWorkBook.Sheets("Sheet2").Range("D" & intRowCounter).Value = resultSet.Fields("Dept").Value
    intRowCounter = intRowCounter + 1
  resultSet.movenext
Loop
resultSet.Close
End Sub

Read Database

Read Data­base

You may also like...

2 Responses

  1. patrick says:

    hi is there a Chance to write to db too?
    maybe you can offer a test­file?
    Kind regards patrick

Leave a Reply

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

%d bloggers like this: