MS Excel - Connect to a SQL Server database, run a query and get the results - Step by step

In April 2024, I wrote a small blog post about a VBA script that will connect to a MS SQL database, run a SELECT query to retrieve data and put them in an Excel worksheet.

Let's rewrite this article in a full tutorial. We'll install and run a SQL Server database using Docker, download MS SQL Server Management Studio, connect to our SQL Server, create a new database with dummy data and, finally, in Excel, retrieve the list of our customers.

Excel is a comfortable front-end, but a poor place to keep large volumes of data. When the volume grows, Pandas - Merge two or more files and create a merged one explains why, and what to do instead.

Here's the result: a live query against SQL Server, its rows landing straight in an Excel sheet.

Why It Works​

• A single VBA class (clsData) wraps ADO, so every subroutine just asks it for a recordset instead of hand-rolling a connection string each time.
• CopyToSheet copies data once — fast, but no live link with the database.
• AddQueryTable keeps the connection alive instead, so the end user can hit Data → Refresh in Excel at any time to get fresh data.

Download SQL Server and create a dummy database​

You can skip this step if you already have a SQL Server instance you can connect to.

Read the full blog post Play with Microsoft SQL Server 2022 using Docker for more information about how to run your own SQL Server instance.

In short:

• Start a console (can be DOS, PowerShell or Linux),
• Run docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=2Secure*Password2" -p 1433:1433 --name sqlserverdb -h mysqlserver -d mcr.microsoft.com/mssql/server:2022-latest to download SQL server and run an instance of it in a Docker container,
• Download SQL Server Management Studio if you don't have it yet. It's free.
• Once installed, start SQL Server Management Studio.

Use the values below for the authentication:

You're now in SSMS. We'll create a dummy database. Using some AI, I've asked for a script, here it is:

-- Create a new database
CREATE DATABASE SampleDB;
GO?

-- Switch to the new database
USE SampleDB;
GO?

-- Create a sample table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),?
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    City VARCHAR(50)
);
GO?

-- Insert sample data
INSERT INTO Customers (FirstName, LastName, Email, City) VALUES
('John', 'Doe', 'john.doe@example.com', 'New York'),
('Jane', 'Smith', 'jane.smith@example.com', 'London'),
('David', 'Lee', 'david.lee@example.com', 'Paris'),
('Emily', 'Brown', 'emily.brown@example.com', 'Tokyo'),
('Michael', 'Davis', 'michael.davis@example.com', 'Sydney'),
('Sarah', 'Wilson', 'sarah.wilson@example.com', 'Berlin'),
('Robert', 'Garcia', 'robert.garcia@example.com', 'Madrid'),
('Jennifer', 'Rodriguez', 'jennifer.rodriguez@example.com', 'Rome'),
('William', 'Martinez', 'william.martinez@example.com', 'Toronto'),
('Linda', 'Anderson', 'linda.anderson@example.com', 'Chicago');
GO?

-- Verify the data
SELECT * FROM Customers;
GO?

We just need to click on the Execute button or press F5 to run it i.e. to create our SampleDB database.

Now, right-click on the Databases node (in the top left tree-view) and you'll see, we've now our DB:

The Excel part​

Surf to https://github.com/cavo789/vba_excel_sql to retrieve my VBA code. That code is a VBA Class for Excel that makes it really easy to access records stored in SQL server and output these data in an Excel sheet, keeping or not the connection alive (so you can do a Refresh at any time).

Please start Excel and create a new workbook.

Press ALT+F11 to open the VBE interface.

Right-click on Microsoft Excel Objects in the left Project - VBAProject pane and insert a new class.

Go to https://github.com/cavo789/vba_excel_sql/blob/master/src/SQL2Excel.xlsm/clsData.cls and click on the Copy raw file button

Paste the content in the VBE editor and remove the first lines as displayed below. Also, see in the bottom left Properties pane, click on the (Name) field and type clsData as new name.

Now, please create a new module.

Go to https://github.com/cavo789/vba_excel_sql/blob/master/src/SQL2Excel.xlsm/test.bas and click on the Copy raw file button.

Paste the content in the VBE editor and remove the first line as displayed below. Also, in the bottom left Properties page, click on the (Name) field and type test as new name.

Still in the test module, pay attention to the very first lines:

You'll need to update these values to match yours. If you've created the SQL Server instance as explained here above, please use these values:

Private Const cServerName = "localhost,1433"   ' <-- You'll need to mention here your server name?
Private Const cDBName = "SampleDB"       ' <-- You'll need to mention here your database name?
Private Const cSQLStatement = "SELECT * FROM dbo.Customers" ' <-- You'll need to mention here a valid SQL statement (SELECT ...)?

Still in the VBA, please click on the Tools menu then select References

In the list, scroll down until you find Microsoft ActiveX Data Objects 2.8 Library and once retrieved, please select it.

Let's play with CopyToSheet​

Still in the VBE interface, please select the test module and scroll down until the CopyToSheet subroutine.

Please update the subroutine and add two lines:

Sub CopyToSheet()

Dim rng As Range?

    cData.ServerName = cServerName?
    cData.DatabaseName = cDBName
    ' highlight-next-line
    cData.UserName = "SA"
    ' highlight-next-line
    cData.Password = "2Secure*Password2"

    Set rng = cData.SQL_CopyToSheet(cSQLStatement, ActiveSheet.Range("A1"))?

End Sub

We're ready. Put your cursor in the CopyToSheet function, anywhere and press F5 to execute it.

Nothing has happened? Are you sure?

Please switch from the VBE interface to your Excel worksheet and tadaaa — the same result already shown at the top of this article.

The List of Features (optional deep dive — full code for each subroutine)​

CopyToSheet subroutine​

Description​

Get a recordset from the database and output it into a sheet. This function makes a copy not a link => there is no link with the database, no way to make a refresh.

Advantages​

Fast

Drawback​

Don't keep any link with the DB, records are copied to Excel

Sample code:

cData.ServerName = cServerName
    cData.DatabaseName = cDBName
    ' highlight-next-line
    cData.UserName = "SA"?
    ' highlight-next-line
    cData.Password = "2Secure*Password2"?

    ' When cData.UserName and cData.Password are not supplied
    ' the connection will be made as "trusted" i.e. with the connected
    ' user's credentials.

    Set rng = cData.SQL_CopyToSheet("SELECT * FROM dbo.Customers", ActiveSheet.Range("A1"))?

AddQueryTable subroutine​

Description​

Create a query table in a sheet : create the connection, the query table, give it a name and get data.

Advantages​

Keep the connection alive. The end user will be able to make a Data -> Refresh to obtain an update of the sheet. If the user doesn't have access to the database, the records will well be visible but without any chance to refresh them

Drawback​

If the parameter bPersist is set to True, the connection string will be in plain text in the file (=> avoid this if you're using a login / password).

Parameters​

• sSQL : Instruction to use (a valid SQL statement like SELECT ... FROM ... or EXEC usp_xxxx)
• sQueryName : Internal name that will be given to the querytable
• rngTarget : Destination of the returned recordset (f.i. Sheet1!$A$1)
• bPersist : If true, the connection string will be stored and, then, the user will be able to make a refresh of the query

Sample code

cData.ServerName = cServerName
    cData.DatabaseName = cDBName
    ' highlight-next-line
    cData.UserName = "SA"?
    ' highlight-next-line
    cData.Password = "2Secure*Password2"?

    ' When cData.UserName and cData.Password are not supplied?
    ' the connection will be made as "trusted" i.e. with the connected
    ' user's credentials.

    sSQL = "SELECT * FROM dbo.Customers"

    Call cData.AddQueryTable(sSQL, "qryTest", ActiveCell, True)?

RunSQLAndExportNewWorkbook subroutine​

Description​

This function will call the AddQueryTable function of this class but first will create a new workbook, get the data and format the sheet (add a title, display the "Last extracted date" date/time in the report, add autofiltering, page setup and more.

The obtained workbook will be ready to be sent to someone.

Parameters​

• sSQL : Instruction to use (a valid SQL statement like SELECT ... FROM ... or EXEC usp_xxxx)
• sReportTitle : Title for the sheet
• bPersist : If true, the connection string will be stored and, then, the user will be able to make a refresh of the query

Sample code

cData.ServerName = cServerName
    cData.DatabaseName = cDBName
    ' highlight-next-line
    cData.UserName = "SA"?
    ' highlight-next-line
    cData.Password = "2Secure*Password2"?

    sSQL = "SELECT * FROM dbo.Customers"

    Call cData.RunSQLAndExportNewWorkbook(sSQL, "My Title", False)?

Conclusion​

One VBA class, one SQL Server container, and Excel goes from a static spreadsheet to a live query surface — copy the data once with CopyToSheet, or keep it refreshable with AddQueryTable. See the first, shorter version of this article if you just want the minimal snippet without the full Docker + SSMS setup.