Welcome to Blogs @ Andrew Qu
Blog Index
All blogs
Search results

Using Excel Worksheet as a Database

Summary

An Excel worksheet table can be used as a database. SQL queries can be performed on the table with normal query commands. The advantage of using excel as a data source: a) All data and table views contained in one excel file. b) No separate database is required. c) This will save database management cost.

Preparation work

Before you start, you need to prepare you excel work book so that it can run macros, enabled developer view (VBA) etc.

  1. Create a new workbook, named dbdemo
  2. Enable developer view: Right click menu area -> [Customize Ribon] -> [Main Tabs] -> [Developer]
  3. Enable macro: [Developer] -> [Macro security] -> [Macro Settings] -> [Enable all macros(not...)]
  4. Such files will be saved as "*.xlsm" where m is for macro.
  5. Change "Sheet1" to "Data"
  6. Add columns "First Name", "Last Name", "DoB", "Phone" to [Data]
  7. Format column "DoB" as Date, "Phone" as Text
  8. Enter some data so that the table may look like below:
  9. Change "Sheet2" to "View"
Do not forget to save the work book.

Adding UI to [View] sheet

We will add a combobox and button as shown below:

  1. In "Developer" Ribbon, make sure [Design Mode] is high lighted.
  2. Click [Insert] -> [ActiveX controls] -> [ComboBox]
  3. Once created, right click the combobox, select "Properties". Change (Name) to "SelEmployee"
  4. Click [Insert] -> [ActiveX controls] -> [Button]
  5. Once created, right click the buttton, select "Properties".
  6. Change (Name) to "CreateTable", and Caption to "Create Table"
In the combobox, we will add the list of employees from [Data] sheet. The user can then select an employee to display personal details. When button [Create Table] is clicked, details for the selected employee will be shown in the current view.

Do not forget to save the work book.

Adding Data Connection Module

In order to have data functionality, we need to import a library into out work boot. We can then use [Data] sheet as a database by adding some VBS functions that initializes and connects to the [Data] sheet as a database connection.

  1. From [View] Sheet, click [Visual Basic] to open Microsoft Visual Basic for Application window
  2. In Visual Basic window, click [Tools] -> [References] -> Microsoft ActiveX data Objects 6.1 Library
  3. In Visual basic Project Explorer, right click [VBAProject(dbdemo.xlsx) -> [Insert] -> [Module]
  4. Module1 will be created. In the code pane, add the following code:
Option Explicit
Public dbcnxt As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String

Public Sub OpenDB()
    If dbcnxt.State = adStateOpen Then dbcnxt.Close
    dbcnxt.ConnectionString = "Driver={Microsoft Excel Driver " & _
       "(*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
       ActiveWorkbook.Path & Application.PathSeparator & _
       ActiveWorkbook.Name
'    MsgBox dbcnxt.ConnectionString
    dbcnxt.Open
End Sub

Public Sub closeRS()
    If rs.State = adStateOpen Then rs.Close
    rs.CursorLocation = adUseClient
End Sub

The above code will create a database connection and allows obtaining query set by SQL SELECT statement to be discussed below.

Coding the View Sheet Using Query Set

In this section, we will add a trigger so that whenever [View] sheet is activated, employee name will be obtained from [data] sheet and added to the combo box for selection.

  1. Goto Visual Basic window, double click on Sheet2(View). Then, [Worksheet] - [Activate], see below picture::

Add the following code:
Private Sub Worksheet_Activate()
   constStrSelect = "Select Employee"
   strSQL = "Select Distinct [First Name],[Last Name] From [Data$] Order by [Last Name]"
   closeRS
   OpenDB

   rs.Open strSQL, dbcnxt, adOpenKeyset, adLockOptimistic
   SelEmployee.Clear
   SelEmployee.AddItem constStrSelect
   SelEmployee.Value = constStrSelect
   
   If rs.RecordCount > 0 Then
      Do While Not rs.EOF
         SelEmployee.AddItem rs.Fields(0) & " " & rs.Fields(1)
         rs.MoveNext
      Loop
   Else
      MsgBox "Database is empty.", vbCritical + vbOKOnly
      Exit Sub
   End If
End Sub
Also add to the top of the file:
Dim constStrSelect As String
Now, exit Excel and save the work book as "Excel macro enabled work book (*.xlsm)".  Reopen the worksbook and change sheet from Data to View, you should notice that the combo box is now filled with employee names from Data sheet.


Creating view table for selected employee

Now that employee names can be selected from the combo box. In this section, we will get employee details and display the details in a table.

  1. Goto sheet "View" and double click the "Create Table" button
  2. Visual Basic code window should open. In the function stub, add the following code:
Private Sub CreateTable_Click()
   Dim EmpName As String
   Dim FirstLastName() As String
   
   EmpName = SelEmployee.Value
   If EmpName = constStrSelect Then
     Exit Sub
   End If
   FirstLastName() = Split(EmpName, " ")
   
   strSQL = "Select [First Name],[Last Name],[DoB],[Phone] " & _
        "From [Data$] " & _
        "WHERE [Last Name] = '" & FirstLastName(1) & _
        "' AND " & "[First Name] = '" & FirstLastName(0) & "' " & _
        "Order by [Last Name]"
'    MsgBox strSQL
   closeRS
   OpenDB

   rs.Open strSQL, dbcnxt, adOpenKeyset, adLockOptimistic

   Range("A6").Value = rs.Fields(0)
   Range("B6").Value = rs.Fields(1)
   Range("C6").Value = rs.Fields(2)
   Range("D6").Value = rs.Fields(3)
End Sub

Source code download
Download full source code

The zip file contains the demo excel sheet with all source codes. If you do not enable macros, you can still view the source code embedded in the file. You can then cut/paste into your Excel document.
Ads from Google
Dr Li Anchor Profi
www.anchorprofi.de
Engineering anchorage plate design system
©Andrew Qu, 2015. All rights reserved. Code snippets may be used "AS IS" without any kind of warranty. DIY tips may be followed at your own risk.