Excel as Database to pull bulk records VBA example

Excel as Database to pull bulk records VBA example

Database

Since Excel itself considered as a basic database which support various relationships, joins, SQL queries etc. In this article we will see how we connect a Excel workbook as database quickly step by step using ADODB COM (Component Object Model) and pull bulk records using VBA (Visual Basic for Applications).

Objects

  1. ConnectionString : string having database details like name, credentials
  2. Connection : Connection the database
  3. Command : refers to a valid SQL statement which designed to get the data
  4. Recordset : holds result from the database in Row/Column format
  5. Fields : refers to individual column in recordset

ADODB complete reference

Source file

Create a excel file which contains information like shown below and name excel file as “data.xlsx

Reference ADODB

Open Visual Basic Editor add reference for “Microsoft ActiveX Data Object 6.0 Library“, there are many different versions like 2.0, 2.1, 2.5, 2.6, 2.7 2.8 refers various releases based on Excel versions. Since I am using Office 2016 hence referring the latest 6.0 in this example.

  1. Navigate to Tools menu
  2. Select References
  3. Select “Microsoft ActiveX Data Object 6.0 Library” as shown below

Connection String

Write connection string. You have to change your file name and path in given string:

Dim strConnection As String
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                       & "User ID=Admin;" _
                       & "Data Source=C:\Users\nawazish\Downloads\data.xlsx;" _
                       & "Mode=Read;" _
                       & "Extended Properties=""HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;"";" _
                       & "Jet OLEDB:Engine Type=34;"

Create connection

Dim oConnection As ADODB.Connection
Set oConnection = New ADODB.Connection

Open connection

  1. ConnectionString : a valid string
  2. UserID: username if database is password protect
  3. Password: password if database or file is password protect
  4. Options : its long variable and optional

Code example

oConnection.Open strConnection

Recordset

Dim oDataRecordset As ADODB.Recordset
Dim strCommand As String
'SQL statement
strCommand = "SELECT * FROM [emp$]"
'Execute sql on database and get result
Set oDataRecordset = oConnection.Execute(strCommand)

Note: emp# is the sheet name in my data.xlsx file which turns as table name in ADODB

Populate result

ActiveSheet.Range("A1").CopyFromRecordset oDataRecordset

Close recordset

oDataRecordset.Close
'Memory cleanup
Set oDataRecordset=Nothing

Close connection

oConnection.Close
'Memory cleanup
Set oConnection=Nothing

Complete code example

Public Sub PullBulkRecordset()
	'variables and objects
    Dim strConnection As String
    Dim strCommand As String
    Dim oDataRecordset As ADODB.Recordset
    Dim oConnection As ADODB.Connection
    
	'Connection string
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                       & "User ID=Admin;" _
                       & "Data Source=C:\Users\Downloads\data.xlsx;" _
                       & "Mode=Read;" _
                       & "Extended Properties=""HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;"";" _
                       & "Jet OLEDB:Engine Type=34;"
    
	'SQL statement
    strCommand = "SELECT * FROM [emp$]"
    
	'Connection initiation
    Set oConnection = New ADODB.Connection
    
	'Open connection
    oConnection.Open strConnection
    
	'Execute connection and get result
    Set oDataRecordset = oConnection.Execute(strCommand)
    
	'Populate records in excel
    ActiveSheet.Range("A1").CopyFromRecordset oDataRecordset
    
	'Close recordset
    oDataRecordset.Close
    
	'Memory cleanup
    Set oDataRecordset = Nothing
    
	'Close Connection
    oConnection.Close
    
	'Memory cleanup
    Set oConnection = Nothing
End Sub

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Leave a Reply

Your email address will not be published.