Running a SQL Stored Procedure from Excel with Dynamic Parameters (and some common errors)

Step 1: Data tab – > From Other Sources -> From SQL Server

.

Step 2: Enter Credentials.

.

Step 3: Choose any small table or view. It doesn’t matter which one because we’ll be changing the connection anyway.

.

Step 4: In the Data Connection Wizard dialog, give your connection a friendly name that you can remember. This is how you’ll point to this connection in VBA. In this case, I’m calling this connection MYSERVER.

.

Step 5: Excel will pop up the Import Data dialog box – Click Properties here (NOT THE OK BUTTON).

.

Step 6: Click on the Definition tab. Here, you’ll want to change Command Type to SQL, and then enter your Stored Procedure name in the Command Text input. As you can see, the SQL statement is simply a call to the Stored Procedure along with the Parameter name (in this case, the procedure is expecting one parameter that accepts a market name).

.

Step 7: Excel warns you about something unimportant – Click Yes (which in this case, means go away).

.

Step 8: Excel will activate the Import Data dialog box again. This time click OK to fire the Stored Procedure and return the results.

.

Step 9: Note the results you get from your original hard-coded command you entered in Step 6. At this point, you know your connection is working.

.

Step 10: Create a mechanism to dynamically select and enter your parameters.

In my case, I need to pass a market name to my Stored Procedure. So I added a simple dropdown where my selection goes to Cell B2. The mechanism you choose to select your parameters is not the important thing here. The important thing is to note where the final selection will be housed. In this example, my market selection will end up in Cell B2.

.

Step 11: The final step is to copy and paste this Macro into a standard module. Notice that all we are dong here is changing the definition fo the CommandText property of the Connection. This basically means that as long as you can point to the correct connection (MYSERVER in this case), you can rebuild the CommandText on the fly. Here, I’m rebuilding the CommandText to point to my chosen cell range (cell B2 – where my market selection is housed).

1. Sub RefreshQuery()
3.     With ActiveWorkbook.Connections("MYSERVER").OLEDBConnection
4.         .CommandText = "EXECUTE dbo.Tng_Market_Feed '" & Range("B2").Value &"'"
5.     End With
6.     ActiveWorkbook.Connections("MYSERVER").Refresh
7.    
8. End Sub

.

At this point, it’s just a question of firing the Macro each time your parameter selection changes.

Now you’re cooking with gas!

Reference: http://datapigtechnologies.com/blog/index.php/running-a-sql-stored-procedure-from-excel-with-dynamic-parameters/

Error 1:

The query did not run, or the database table could not be opened. Check the database server or contact your DBA. Make sure the external database is available and hasn't been moved or recognized, then try the operation again

Solution: By wrapping your Stored Procedures with SET NOCOUNT ON … SET NOCOUNT OFF, you can fire all kinds of Stored Procedures from Excel:

Error 2:

Modify an embedded Connection String in microsoft excel macro

Solution: he connection string needs to have OLEDB; prepended to the string.

so this now works!!!

ActiveWorkbook.Connections("Transaction_Summary").OLEDBConnection.Connection = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ADCData_Doric;Data Source=doric-server5;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LHOLDER-VM;Use Encryption for Data=False;Tag with column collation when possible=False"