Automate the import of Data from an external Database into Excel VBA

Objective:

The goal of this code is to connect Excel to an external SQL Server database, execute a query, and import the results into a specific Excel worksheet.

Prerequisites:

1. Access to an external database (e.g., SQL Server).
2. Database connection details (server name, database name, username, password).
3. Add a reference to the « Microsoft ActiveX Data Objects Library » in VBA:
   • Open the VBA editor (Alt + F11).
   • From the Tools menu, choose References.
   • Check the box for « Microsoft ActiveX Data Objects x.x Library » (x.x depends on your Office version).

VBA Code for Data Import:

Here is an example of VBA code that connects Excel to a SQL Server database, executes a query, and imports the results into a worksheet.

Step 1: Set up the database connection

Sub ImportData()
    ' Declare variables
    Dim conn As Object
    Dim rs As Object
    Dim ws As Worksheet
    Dim connectionString As String
    Dim sqlQuery As String
    Dim i As Integer
    Dim j As Integer   
    ' Initialize the destination worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.Cells.Clear ' Clear old data   
    ' Database connection string
    connectionString = "Provider=SQLOLEDB;Data Source=YourServer;Initial Catalog=YourDatabase;" & _
                       "User ID=YourUsername;Password=YourPassword;"   
    ' Create the ADO connection object
    Set conn = CreateObject("ADODB.Connection")
    conn.Open connectionString   
    ' SQL query to execute
    sqlQuery = "SELECT * FROM YourTable"  ' Replace with your SQL query   
    ' Create the ADO Recordset object to store query results
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sqlQuery, conn   
    ' Check if any data was returned
    If Not rs.EOF Then   
        ' Fill the headers in Excel
        For i = 1 To rs.Fields.Count
            ws.Cells(1, i).Value = rs.Fields(i - 1).Name
        Next i       
        ' Fill the data into Excel
        i = 2 ' Start at row 2
        Do While Not rs.EOF
            For j = 1 To rs.Fields.Count
                ws.Cells(i, j).Value = rs.Fields(j - 1).Value
            Next j
            rs.MoveNext
            i = i + 1
        Loop
    End If   
    ' Close the ADO objects
    rs.Close
    conn.Close   
    ' Release the objects
    Set rs = Nothing
    Set conn = Nothing  
    MsgBox "Data imported successfully!", vbInformation
End Sub

Detailed Explanation of the Code:

1. Declaring Variables:
   • conn: The ADO connection object to connect to the database.
   • rs: The Recordset object that will hold the query results.
   • ws: The worksheet object where data will be imported.
   • connectionString: The connection string containing the database details (replace with your actual values).
   • sqlQuery: The SQL query that will fetch the data (replace with your own SQL query).
2. Creating the ADO Connection:
   • Set conn = CreateObject(« ADODB.Connection »): Creates the ADO connection object.
   • conn.Open connectionString: Opens the connection to the database using the connection string.
3. Executing the SQL Query:
   • Set rs = CreateObject(« ADODB.Recordset »): Creates a Recordset object to hold the query results.
   • rs.Open sqlQuery, conn: Executes the SQL query and fills the Recordset with the returned data.
4. Importing the Data into Excel:
   • The first loop For i = 1 To rs.Fields.Count fills the headers (column names) in the first row of the worksheet.
   • The second loop Do While Not rs.EOF goes through each record in the Recordset and imports the data into Excel row by row.
5. Closing the Connection:
   • rs.Close and conn.Close: Close the Recordset and the database connection once the import is complete.
   • Set rs = Nothing and Set conn = Nothing: Release the objects to free up memory.
6. Displaying a Confirmation Message:
   • A message box will pop up to inform you that the data import was successful.

Customization:

• Server, Database, and Credentials: Replace YourServer, YourDatabase, YourUsername, and YourPassword with your actual database connection information.
• SQL Query: You can modify sqlQuery to retrieve the specific data you need, such as:
  • « SELECT column1, column2 FROM table WHERE condition »
• Destination Worksheet: Replace « Sheet1 » with the name of the Excel worksheet where you want the data to be imported.

Considerations:

• Ensure your network allows access to the database and that the SQL Server permits remote connections if needed.
• You can automate this process to run at specific intervals or trigger it with a button in the Excel workbook.

This VBA code enables you to automate the process of importing data from an SQL Server database into Excel, making it easy to work with external data directly within your workbook.