Run SQL Queries in Excel with Excel VBA

The process involves using VBA to connect to a database, send SQL queries, and retrieve the data to Excel.

Running SQL Queries in Excel using VBA

What We Need:

1. VBA (Visual Basic for Applications): Excel’s built-in programming language to automate tasks.
2. ADO (ActiveX Data Objects): A set of COM (Component Object Model) libraries to interact with databases.
3. Database Connection String: This contains the details of the database you’re connecting to, such as the server, database name, and authentication credentials.

Step-by-Step Process:

1. Add Reference to ADO Library: Before writing the code, you must add a reference to the ADO library. This allows you to interact with the database.
   • Open Excel and press Alt + F11 to open the VBA editor.
   • In the VBA editor, go to Tools > References.
   • In the dialog box, scroll down and check « Microsoft ActiveX Data Objects x.x Library » (e.g., 6.1).
   • Click OK.
2. Setup Connection String: The connection string varies depending on the type of database you’re connecting to. Here are examples for some common databases:
   • SQL Server:
   • « Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DatabaseName;User ID=YourUsername;Password=YourPassword; »
   • MySQL:
   • « Driver={MySQL ODBC 8.0 Driver};Server=ServerName;Database=DatabaseName;User=YourUsername;Password=YourPassword; »
   • Access:
   • « Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\your\database.accdb; »
3. VBA Code to Execute SQL Queries:

Here’s a detailed code snippet to run SQL queries in Excel using VBA:

Sub RunSQLQuery()
    ' Declare connection and recordset objects
    Dim conn As Object
    Dim rs As Object
    Dim connString As String
    Dim query As String
    Dim sheet As Worksheet
    Dim rowNum As Long
    ' Create new connection object
    Set conn = CreateObject("ADODB.Connection")
    ' Setup your connection string here
    ' Replace with your actual connection details
    connString = "Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog=YourDatabaseName;User ID=YourUsername;Password=YourPassword;"
    ' Open the connection
    conn.Open connString   
    ' Setup the SQL query you want to execute
    query = "SELECT * FROM YourTableName;"  ' Example: Replace with your SQL query   
    ' Create a recordset to store the data
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open query, conn   
    ' Set the worksheet where you want to output the data
    Set sheet = ThisWorkbook.Sheets("Sheet1")  ' Replace with your sheet name   
    ' Start outputting data from row 2 (leave row 1 for headers)
    rowNum = 2   
    ' Write column headers from recordset field names
    For i = 0 To rs.Fields.Count - 1
        sheet.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i   
    ' Loop through the recordset and write each row to Excel
    Do While Not rs.EOF
        For i = 0 To rs.Fields.Count - 1
            sheet.Cells(rowNum, i + 1).Value = rs.Fields(i).Value
        Next i
        rs.MoveNext
        rowNum = rowNum + 1
    Loop   
    ' Close the recordset and connection
    rs.Close
    conn.Close   
    ' Clean up
    Set rs = Nothing
    Set conn = Nothing   
    ' Notify user
    MsgBox "Query executed successfully and data imported into Excel.", vbInformation
End Sub

Explanation of the Code:

1. Connection Object (conn):
   The connection object is used to open a connection to your database using a connection string.
2. Recordset Object (rs):
   The recordset is an object used to store the data returned by your SQL query. You can think of it as a container for the results of your SQL query.
3. Connection String:
   The connection string contains the details required to connect to the database. It typically includes the server address, database name, and user credentials.
4. SQL Query:
   You can modify the query variable to contain any valid SQL query. In this case, we’re selecting all rows (SELECT *) from a table called YourTableName.
5. Loop to Write Data:
   The code then loops through the Recordset and writes each field (column) and each row of data into Excel, starting from row 2 (row 1 is used for headers).
6. Column Headers:
   The field names from the SQL query are written to Excel as the headers of your table. The Fields.Count property gets the number of fields (columns) in your recordset, and the field names are written to the first row.
7. Closing and Cleanup:
   The Recordset and Connection are closed using rs.Close and conn.Close respectively, ensuring that resources are freed up after use.
8. Message Box:
   After the query is executed successfully and data is imported into Excel, a message box is shown to inform the user.

Things to Note:

• Error Handling:
  This code doesn’t include error handling. You may want to add On Error statements to handle potential issues like connection failures or query errors.
• Database Type:
  Make sure the connection string matches the database you’re using (SQL Server, MySQL, Access, etc.).
• Security:
  Be careful with sensitive data like database credentials. It’s always better to avoid hardcoding credentials in your code and use secure ways to store credentials if needed.
• Running the Code:
  To run the code, press Alt + F8, select the RunSQLQuery macro, and click Run. The data will be fetched from the database and populated in the specified worksheet.

Conclusion:

This approach allows you to integrate SQL queries directly within Excel using VBA, making it a powerful tool for extracting and analyzing data from external databases. You can customize the query, connection string, and output formatting as needed for your specific use case.