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).
- ConnectionString : string having database details like name, credentials
- Connection : Connection the database
- Command : refers to a valid SQL statement which designed to get the data
- Recordset : holds result from the database in Row/Column format
- Fields : refers to individual column in recordset
ADODB complete reference
- Various Connection strings to connect any Excel file (xlsm, xls, xlsx, xlsb etc.)
- Different types of locking in ADODB
- Different cursor types in ADODB
- Understand Recordset object
Create a excel file which contains information like shown below and name excel file as “data.xlsx“
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.
- Navigate to Tools menu
- Select References…
- Select “Microsoft ActiveX Data Object 6.0 Library” as shown below
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;"
Dim oConnection As ADODB.Connection Set oConnection = New ADODB.Connection
- ConnectionString : a valid string
- UserID: username if database is password protect
- Password: password if database or file is password protect
- Options : its long variable and optional
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
oDataRecordset.Close 'Memory cleanup Set oDataRecordset=Nothing
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.