Excel VBA – Import SQL Server Data

The following post (Excel VBA – Import SQL Server Data) will demonstrate how to establish a connection to SQL Server, then pull data into your Excel Worksheet.  Further, I will show how to setup a couple buttons for clearing the data in Excel and executing the SQL command to import refreshed data.  This is a simple macro to allow a user to refresh data in Excel, without having to request a new data pull from a technical resource.  I’ve used this in the past to quickly distribute data to multiple users.  All I need to do once the data is refreshed in SQL server is notify the users so they can refresh the data in their Excel file.

The actual VBA code is quite simple and can be copied into your VBA Project (modify to suit your needs).  As always, I encourage you to copy down the code and give it a try for yourself.

Setup:  Excel VBA – Import SQL Server Data

First, open a new Excel file and save it as a “Microsoft Excel Macro Enabled Excel Worksheet” with the .xlsm file extension.

Next, add three Command Buttons to the worksheet.  Choose the “ActiveX Controls” button (top-left object in that section)

Excel VBA Import SQL Data

Name the buttons as follows:

  • cmdClear
  • cmdImport
  • cmdImportVariable

Below is a suggested layout for your Excel Worksheet, once you have added the buttons.  Notice I’ve also added a location where the user can enter in a search criteria (cells A9 and A10).  This criteria will be fed into the VBA code as a parameter for the SQL search, via Stored Procedure call.  This will be handled as a LIKE search in SQL.  There is also a location where a count of the search results will be displayed (cells A12 and A13).  This count will be added as a formula, as part of the Excel VBA macro.

Excel VBA Import SQL Data

Once you are set with the Excel Worksheet setup, you can double-click each button and add the following VBA macro code:

Note:  Once you open the Visual Basic window to add/edit the code, you will also need to make sure the following references are set, within the “Tools – References” window:

Excel VBA Import SQL Data

 

Add buttons and macro code

Now, add the three buttons to your Excel Worksheet

Button:  cmdClear

Button:  cmdImport

Button:  cmdImportVariable

Execute the macro

That should do it from a setup standpoint.  Once you click on the button “Import SQL Data Query”, you should see results similar to the following (click on image to enlarge):

Excel VBA Import SQL Data

Click on the “Clear” button to wipe out the search results and results count.  Then, add a search string in cell A10 and click on the “Import SQL Data with Variable” button.  you should see results similar to the following, which queries the SQL table with a LIKE search, returning any records that contain the search string you entered in cell A10 (click on image to enlarge):

Excel VBA Import SQL Data

Updates to Post

3/25/2016:

Added code to button “cmdImportVariable” which makes sure the variable entered in Excel is 3 characters.

Added code to buttons “cmdImport” and “cmdImportVariable” to clear past search results when doing a new data pull.

Per Don’s comment on 3/24/2016, here is the sample SQL Stored Procedure I used to build the post:

 

If you would like to post any additional code examples, please add them to the comments below.

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

2 thoughts on “Excel VBA – Import SQL Server Data”

  1. Thanks for the post, it works well, but I can’t seem to get the last macro to work “cmdImportVariable_Click()”. Could you post a sample of the actual stored procedure used in this example?

    • Don, I added the stored procedure code in the “Updates” section at the bottom of the post. Let me know if you have any additional questions. I also added code to make sure the search parameter entered in Excel is three characters. Also added code to each button to clear previous search results prior to pulling new data from SQL.

Leave a Comment