# MS Excel - Connect to a SQL Server database A series of 2 article(s), in reading order. # MS Excel - Connect to a SQL Server database, run a query and get the results > Connect MS Excel to SQL Server with VBA. Run SQL queries, inject results directly into your spreadsheet, and preserve formatting. Learn the best method for data refresh or secure sharing. --- ![MS Excel - Connect to a SQL Server database, run a query and get the results](/img/v2/mssql.webp) **TL;DR** This article points to the author's `vba_excel_sql` GitHub project, which provides VBA code to run a SQL query against a SQL Server database directly from Excel and inject the results into a sheet while preserving its formatting. Two injection methods are offered: `AddQueryTable` (keeps the connection live for one-click refresh) and `CopyToSheet` (drops the connection/credentials, safer for sharing). > **Information:** > > Please read my new article [MS Excel - Connect to a SQL Server database, run a query and get the results - Step by step](/blog/vba-excel-sql-server-part-2) which is more complete than this one. Imagine you can execute a query like `SELECT customer_id, first_name, last_name, email FROM customers ORDER BY last_name ASC;` in your Excel sheet and that Excel will connect your [Microsoft SQL Server database](/blog/docker-mssql-server), run the query there, get the result and put the data directly in your sheet. Would be nice, no? Imagine your sheet has already a nice layout with colors, titles having filters enabled and f.i. has a name (like `rngMyCustomers`). It would be nice if the updated data still keep all the layouts and just extends the name; no? Stop imagining, it's just so easy! Years ago, I published for this. You'll retrieve in my GitHub repository all the explanations to do this i.e. a VBA code to add in your Excel file ([quick access](https://github.com/cavo789/vba_excel_sql/blob/master/src/SQL2Excel.xlsm/clsData.cls)) and a [demo](https://github.com/cavo789/vba_excel_sql/blob/master/src/SQL2Excel.xlsm/test.bas). This time, I won't rewrite the whole explanation in the form of a post because everything is already listed on GitHub; please follow this link: > **AddQueryTable or CopyToSheet:** > > You'll see there are two methods: `AddQueryTable` and `CopyToSheet`. With the first one, data will be injected into your Excel sheet as a *query table* and this gives immense powers like the requery one. In other words, the next time, you just need to right-click on your table and from the contextual menu, chose `Refresh` and hop, the query is executed back on SQL Server and you'll get the update in a matter of seconds. > > With `CopyToSheet`, you'll lose the connectivity: it'll be safe to send the sheet to someone else. The credentials needed to connect to the server won't be kept in the sheet contrary to the `AddQueryTable` feature. --- # MS Excel - Connect to a SQL Server database, run a query and get the results - Step by step > Connect MS Excel VBA to SQL Server with this step-by-step tutorial. Learn to run SQL queries, retrieve data, and output results into your spreadsheet. Docker setup included. --- ![MS Excel - Connect to a SQL Server database, run a query and get the results - Step by step](/img/v2/mssql.webp) **TL;DR** This detailed, step-by-step tutorial teaches you how to connect Microsoft Excel to a SQL Server database using VBA. It covers everything from setting up a SQL Server instance with Docker and creating a sample database to writing the VBA code to execute queries and import data into your spreadsheet. You will learn how to configure the connection, use a custom VBA class to handle data retrieval, and output the results directly into your worksheet, with or without a live connection for refreshing the data. In April 2024, I wrote a small [blog post](/blog/vba-excel-sql-server) 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](/blog/python-pandas-merge) 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. ![You got the list of customers](./images/worksheet.webp) ## 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](/blog/docker-mssql-server) 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](https://learn.microsoft.com/en-us/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16#download-ssms) if you don't have it yet. It's free. * Once installed, start SQL Server Management Studio. Use the values below for the authentication: | Label | Value | | -------------- | --------------------------- | | Server name | `localhost,1433` | | Authentication | `SQL Server Authentication` | | Login | `SA` | | Password | `2Secure*Password2` | You're now in SSMS. We'll create a dummy database. Using some AI, I've asked for a script, here it is: **create\_db.sql** ```sql -- 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 ``` ![The creation script](./images/creation_sql.webp) 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 SampleDB](./images/customers.webp) ## The Excel part Surf to 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). > **Information:** > > The GitHub repository is archived (read-only since Nov 2021). The VBA code remains fully functional; no further updates are planned. Please start Excel and create a new workbook. ![New workbook](./images/new_workbook.webp) 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. ![Insertion of a new class](./images/insert_class.webp) Go to and click on the *Copy raw file* button ![Copy raw file](./images/copy_raw_file.webp) 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. ![clsData](./images/clsData.webp) Now, please create a new module. ![Insertion of a new module](./images/insert_module.webp) Go to 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. ![Module test](./images/test.webp) Still in the `test` module, pay attention to the very first lines: ![Initialization of the module](./images/initialization.webp) 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: **clsData.bas** ```bas 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 ...) ``` ![With the initialization](./images/initialization_done.webp) Still in the VBA, please click on the `Tools` menu then select `References` ![Tools -> References](./images/tools_references.webp) In the list, scroll down until you find `Microsoft ActiveX Data Objects 2.8 Library` and once retrieved, please select it. ![Microsoft ActiveX Data Objects 2.8 Library](./images/activex_data_objects.webp) ### Let's play with CopyToSheet Still in the VBE interface, please select the `test` module and scroll down until the `CopyToSheet` subroutine. ![CopyToSheet](./images/copy_to_sheet.webp) > **We need to provide our SQL Server credentials:** > > As illustrated on the image above, no username or password has been provided. Like this, the connection will be made using our Windows account but, in this article, we haven't made the required configuration for this. > > Nevertheless, we've a SQL account called `SA` so we'll use it. Please update the subroutine and add two lines: **module.bas** ```bas 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) > **Danger:** > > IF USERNAME AND PASSWORD HAVE BEEN SUPPLIED, THIS INFORMATION WILL BE SAVED IN CLEAR IN THE CONNECTION STRING! This applies to both `AddQueryTable` and `RunSQLAndExportNewWorkbook` below, whenever `bPersist` is `True`. ### 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: **module.bas** ```bas 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 **module.bas** ```bas 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 **module.bas** ```bas 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](/blog/vba-excel-sql-server) if you just want the minimal snippet without the full Docker + SSMS setup. ---