Connecting MS Excel as a Database

Most of my posts usually relate to the new technologies on the very edge, like Augmented Reality or innovations in hardware. Today, however, I decided to write a few words on the technology dated back to 2000. So, why would you want to use a MS Excel file as a database in 2017? Basically:

  • The need to analyze data has increased significantly in recent years and Data Mining is crucial in almost every business field
  • Many SMEs do not have database admins and cannot effectively afford to work with relational databases (like Oracle, MSSQL, etc.) even in the Age of Cloud
  • Working with MS Access, a reasonable alternative to this solution, requires a new skill set in your team (editing the MS Excel “database” can be done directly in MS Excel in the usual way)

Most advanced Excel users are probably familiar with the old-fashioned VBA programming language for Macros. It is a very easy way to do the miracles that would be too complex or even impossible with standard Excel functions.

Gathering the Data

When it comes to data analysis, you might often need to load data from external workbooks or export them to a summary workbook on the shared network drive. You would have two main options of tackling it using a VBA macro:

  • Open the workbook, look for the data and import/export to/from the target sheet
Dim SourceBook As Workbook, TargetBook As Workbook, SourceSheet As Worksheet
Set TargetBook = ActiveWorkbook
Set TargetSheet = ActiveSheet
﻿Set SourceBook = Workbooks.Open(book_path, xlUpdateLinksNever, True)
Set SourceSheet = SourceBook.Sheets(sheet_name)

TargetSheet.Cells(1,1) = SourceSheet.Cells(1,1)

SourceBook.Close SaveChanges:=False

This is an easy but a very, very slow way. Every time used, the actual workbook is opened by Excel, data are loaded and the workbook is closed. You can improve the performance by disabling screen updating, but still it takes up to seconds:

Application.ScreenUpdating = False
  • Create an ADODB ACE connection and work with SQL (just as if you were talking to a relational database), where workbook is a database and worksheet is a table
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Dim objConnection, objRecordset As Variant
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & source_path & ";" & _
        "Extended Properties=""Excel 12.0;HDR=Yes;"";"

Alternatively, you can use JET; JET is a bit faster but has problems with the “new” .xlsx Excel format:

myConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";"

SQL request example:

objRecordset.Open "Select " & selectStr & " FROM [" & table & "] WHERE " & whereStr, _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

﻿Set recordset = objRecordset

This way, Excel does not open the workbook the way it does in option 1, but uses the SQL query to get/set data from/to the target workbook. This is much faster and enables you to load data in real real-time, even for reloading combo boxes in your forms.

Thank you for reading, next time I will write more on working with ADODB, the proper syntax, how the “database” file should look like, and little more.

3 thoughts on “Connecting MS Excel as a Database


Leave a Reply
