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 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

  1. Open the connection and provide the DSN name as the same name provided in step 2.(In this caseit is “SumitODBC”)
  2. Write the query for fetching the data, provide the table name as the range from the “DB Data.xlsx
  3. Execute the query and store the result in resultSet
  4. Read the data from the resultSet and display in the newly created excel and close the resultset.
  5. Create a button on newly created excel and assign the macro function to it.

 

  • Store data in your excel file and save it some location in your system, say “DB Data.xlsx”
Use Excel as ODBC Source Example

Use Excel as ODBC Source Example

Set Connection = CreateObject(“ADODB.Connection”)

  • Open the connection and provide the DSN name as the same name provided in step 2.(In this caseit is “SumitODBC”)

                    Open “DSN=SumitODBC”

  • Write the query for fetching the data; provide the table name as the range from the “DB Data.xlsx”

strQuery = “SELECT * FROM [Sheet1$A1:Z500] where Dept = ‘IT’

  • Execute the query and store the result in resultSet

Set resultSet = Connection.Execute(strQuery)

  • Read the data from the resultSet and display in the newly created 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

Complete 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 Database

You may also like...

2 Responses

  1. patrick says:

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

Leave a Reply

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

%d bloggers like this: