VBA Coding Pattern: Importing DB Query Results to Excel

Introduces the VBA coding pattern to implement DB Query result import to Excel.

Related articles: VBA Coding Pattern: Create ADO DB Connection String

1. Excel screen and operation method

1.1. excel screen

Enter the connection string to connect to the DB in the “B1” cell, and enter the SQL Query to extract the data in the “B2” cell.

Click “Development Tools > Insert > ActiveX Control > Command Button” to add a button and change the name to “Execute Query”.

An example screen is as follows.

1.2. How to import DB Query results to Excel

It works in the same way as the picture above.

1. Click the “Execute Query” button added to Excel
2. DB connection from VBA code
3. Execute Query in the connected DB
4. Return the result of Query executed in DB
5. Display Query result in Excel sheet

2. Select Provider for DB access

“2. This is the provider used in “Connecting DB in VBA Code”. It can be specified as the Provider property of the ADO.Connection object or as a ConnectionString.

Usually, it is designated as ConnectionString, and the example is as follows.

• MS OLE DB Provider for ODBC Drivers
  • Provider=MSDASQL.1; Password=[password]; Persist Security Info=True; UserID=[UserID]; Data Source=[DSN set in ODBC Control Panel]; Initial Catalog=[DB name]
• MS OLE DB for SQL Server (SQL Server Authentication)
  • Provider=SQLOLEDB.1; Password=[password]; Persist Security Info=True; UserID=[UserID]; Initial Catalog=[DB name];Data Source=[Server address (IP or host name)]
• MS OLE DB for SQL Server (Windows Authentication)
  • Provider=SQLOLEDB.1; Password=[password]; Integrated Security=SSPI; Persist Security Info=False; UserID=[UserID]; Initial Catalog=[DB name];Data Source=[Server address (IP or host name)]
• Oracle Provider for OLE DB
  • Provider=OraOLEDB.Oracle.1;Password=[Password]; Persist Security Info=True; UserID=[UserID]; Data Source=[Server address (IP or host name)]

If a suitable provider is installed on the PC, you can connect to RDBMS servers such as Oracle, MS SQL Server, MySQL, MariaDB, PostgreSQL, and local RDBMS such as MS Access and SQLite.

ConnectionString can be created in the following way.

How to create ConnectionString 1. Using UDL file

A UDL (Universal Data Link) file is a concept included in Microsoft Data Access Components (MDAC).

Reference: Configuring Universal Data Links (UDL) – OLE DB Driver for SQL Server | Microsoft Docs

Execute the following procedure.

1. Right-click on the desktop > New > Text Document
2. Change the file name to test.udl and press Enter
3. Double-click the test.udl file to open it, select a provider in the “Provider” tab, enter the server, user name, password, database, etc. in the “Connection” tab, and then “Test connection” and then “Confirm” and save
4. You can get the ConnectionString by opening the saved test.udl file with an editor (notepad, etc.)

In this method, the provider is displayed according to the 32-bit or 64-bit platform of Windows OS. That is, only 64-bit Provider is displayed in 64-bit OS. How to display 32-bit Provider in 64-bit OS will be posted in a separate article.

How to create a ConnectionString 2. VBA code

previous post VBA Coding Pattern: Create ADO DB Connection String has been described in detail in In summary.

• Added Microsoft ActiveX Data Objects 2.8 Library Reference
• Added Microsoft OLE DB Service Component 1.0 Type Library Reference
• Create a ConnectionString with the following code

Private Sub btnBuildConnectionString_Click()
On Error GoTo 0

    Dim cn As ADODB.Connection, MSDASCObj As MSDASC.DataLinks, oCurrentRange As Range
    Dim eOrgXlEnableCancelKey As XlEnableCancelKey

    Set oCurrentRange = ActiveCell

    Set MSDASCObj = New MSDASC.DataLinks

    eOrgXlEnableCancelKey = Application.EnableCancelKey
    Application.EnableCancelKey = xlDisabled
    Set cn = New ADODB.Connection
    cn.ConnectionString = oCurrentRange
    If MSDASCObj.PromptEdit(cn) = True Then
        oCurrentRange = cn.ConnectionString
    End If

    Set cn = Nothing
    Set MSDASCObj = Nothing

    Application.EnableCancelKey = eOrgXlEnableCancelKey
End Sub

Provider-related notices

The provider you want to use and the 32-bit and 64-bit platform of Excel must be the same. If you are using 32-bit Excel, you must use 32-bit Provider, and if you are using 63-bit Excel, you must use 64-bit Provider.

If you are using a 64-bit Windows OS and 32-bit Excel, you may get an error saying “The provider could not be found” when you try to connect to the Database with the ConnectionString created by the first method.

In this case, check if the platform of Excel and the Provider is the same, and use a suitable Provider.

3. Implementation of importing DB Query results to Excel

3.1. Add Reference

Execute “Tools > Add Reference” in the VBA code editor menu and add “Microsoft ActiveX Data Objects 2.8 Library” as a reference.

3.2. Import DB Query result to Excel VBA code

Write the following code in the button click event procedure added to the Excel sheet.

Private Sub cmd_RunQuery_Click()
    Dim sSQL As String
    Dim oCon As ADODB.Connection, oRS As ADODB.Recordset
    Dim sConnectionString As String

    sConnectionString = Range("B1").Value2
    sSQL = Range("B2").Value2

    Set oCon = New ADODB.Connection
    Set oRS = New ADODB.Recordset
    oCon.ConnectionTimeout = 10
    oCon.Open sConnectionString
    oCon.CommandTimeout = 60

    oRS.Open sSQL, oCon, adOpenForwardOnly, adLockReadOnly
    
    Dim oSht As Worksheet, lColIdx As Long
    Set oSht = Worksheets.Add(After:=Me)
    For lColIdx = 0 To oRS.Fields.Count - 1
        oSht.Range("A1").Offset(0, lColIdx).Value2 = oRS.Fields(lColIdx).Name
    Next lColIdx
    oSht.Range("A2").CopyFromRecordset oRS

    oRS.Close: Set oRS = Nothing
    oCon.Close: Set oCon = Nothing
End Sub

The explanation of the above code is as follows.

• Line 6: Get the “B1” cell value of the Excel sheet into the sConnectionString variable.
• Row 7: Import the “B2” cell value of the Excel sheet as an sSQL variable.
• Line 12: Connect to the database by passing the sConnectionString value to the ADODB.Connection object oCon.
• Line 15: Execute a SQL Query on the database and get the result into the ADODB.Recordset object oRS.
• Line 18: Create a sheet oSht to display the SQL Query execution result.
• Lines 19-21: Output the list of fields (columns) of the object oRS on the first line of the sheet oSht.
• Line 22: Outputs the contents of oRS (SQL Query execution result) in cell “A2” of the oSht sheet.

3.3. Download example file

You can check the example Excel file containing the code described above in the github link below.
File Link: github DAToolset/VBACode/VBA Coding Patterns - DB Query1.xlsm