Passing Dynamic Query Values from Excel to SQL Server

By:   |   Comments (60)   |   Related: > Microsoft Excel Integration


We have always used Excel as a presentation layer to import data from SQL Server for analysis. For queries using parameters - if we have to change the values of parameters, we have to go to the Connection and make the change which isn't an easy way of doing it. In this tip we will learn about passing parameters from Excel cells to a Query at run time to import the data from SQL Server using Visual Basic.


We have to create the necessary Data Connection to Connect to SQL Server, we will be passing the values entered in the cells to the Query by the click of an ActiveXControl Command Button. The click of the command button will be coded in Visual Basic to pass the values from the cells to the data connection and import the data to the spreadsheet.

For our illustration, I have used a query from the AdventureWorks2014 database to find the List Price of all products by their SellStartDate and SellEndDate.

To make our work easier, I created a stored procedure using the below query with SellStartDate and SellEndDate as parameters.

CREATE PROCEDURE dbo.ProductListPrice @SellStartDate as Date, @SellEndDate as Date 
-- SET NOCOUNT ON added to prevent extra result sets from

-- Insert statements for procedure here
    PR.[Name] ProductName
       ,PS.Name SubCategory
       ,PC.NAME ProductCategory
       ,PM.Name ProductModel
       ,CAST([SellStartDate] AS DATE) SellStartDate
       ,CAST([SellEndDate] AS DATE) SellEndDate
FROM [AdventureWorks2014].[Production].[Product] PR
INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] PS 
ON PR.[ProductSubcategoryID]=PS.[ProductSubcategoryID] 
INNER JOIN [AdventureWorks2014].[Production].[ProductCategory] PC 
ON PS.ProductCategoryID=PC.ProductCategoryID 
INNER JOIN [AdventureWorks2014].[Production].[ProductModel] PM 
ON PR.ProductModelID=PM.ProductModelID
WHERE SellStartDate>=@SellStartDate AND SellEndDate<=@SellEndDate
ORDER BY SellStartDate,productname


Open a new Excel spreadsheet and enter our input parameters SellStartDate and SellEndDate as Labels in cells A3 and A4 and make B3 and B4 as their input fields, format them as input cell styles. Open a new spreadsheet

Create a Connection to SQL Server by clicking the "From Other Sources" and select the "From SQL Server" option from the Data ribbon. Select the From SQL Server Connection

Enter the server name in the Data Connection Wizard.  Enter the Server Name

I have used my AdventureWorks2014 database for this demo, select it from the drop down list and select any table. select the database

Rename the connection as "AdventureWorksConnection" in the "File Name" and "Friendly Name" fields. rename the connection

Enter the cell you want to import the data from SQL Server, I would like to populate my data from cell A7 onwards. Select the Cell Location

Click the Properties button and go to its Definition tab and change its Command Type to "SQL" and in the Command Text box enter the name of stored procedure we created above and pass the parameters initially using empty quotes for the parameter values as shown below. intialize the parameters

Click OK and you will see the field names from the stored procedure get populated from cell A7 as a table. populate table structure

We are done with the data connection process, our next step is to pass the parameters to the query for which we will be using an ActiveX Command Button and Visual Basic code behind it to pass the parameters to the query. Please make sure you have the Developer tab enabled in your spreadsheet. If not go to File > Options > Customize Ribbon and Enable the Developer tab. Enable Developer Tab

Go to the Developer tab we enabled, in the Insert option, double click the Command Button from the ActiveX Control tab. ActiveXControl Command Button

It will open a Command Button in the Spreadsheet and position the Button where you want, enable the Design Mode and double click the Command Button. It will take you to the VB Script.

Enter the following code in the Command Button function:

Private Sub CommandButton1_Click()

Dim SellStartDate As Date  'Declare the SellStartDate as Date
Dim SellEndDate As Date    'Declare the SellEndDate as Date

SellStartDate = Sheets("Sheet1").Range("B3").Value   'Pass value from cell B3 to SellStartDate variable
SellEndDate = Sheets("Sheet1").Range("B4").Value     'Pass value from cell B4 to SellEndDate variable

'Pass the Parameters values to the Stored Procedure used in the Data Connection
With ActiveWorkbook.Connections("AdventureWorksConnection").OLEDBConnection
.CommandText = "EXEC dbo.ProductListPrice '" & SellStartDate & "','" & SellEndDate & "'"
End With
End Sub

Click Save and Save the Spreadsheet as a Macro Enabled format.

Uncheck the Design mode option. Enter the input parameters as 09/12/2011 and 09/12/2013 in the input cells B3 and B4 and click CommandButton1. Enter the input parameters

You will see the data gets populated in the Selected Range. Data gets Imported

Also we can see the parameter values passed to the Stored Procedure by going to the Data Connection, you will see the parameters we entered in the input cells. Passed values
Next Steps
  • Try Creating a Message Box to prompt the user about doing a data refresh with parameter values shown in the Message Box before the refresh.
  • Take a look at these other Excel / SQL Server tips

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Junaith Haja Junaith Haja is a Senior Business Intelligence Consultant with Browse Info Solutions, Inc and leads a Microsoft SQL Server and BI team.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, April 14, 2021 - 4:12:23 AM - Dave Back To Top (88529)
My only paramter is IDname (I do not have start and end dates) and it works as it should. Thank you. However my IDname column has many rows, how can I run a query for every row (row after row) and get a result for all of them in same table.

Wednesday, March 10, 2021 - 5:57:17 AM - Ewa Back To Top (88371)
Is it possible to use this solution when columns will be variable as well?

Wednesday, March 10, 2021 - 5:28:54 AM - Ewa Back To Top (88370)
Hi again. I have my stored procedure with dynamic pivot table. So number of columns will be different each time depending on parameter. But when I try to use exactly the same method to get data from that procedure to excel It fails. Is it becouse of variable number of columns?

Wednesday, November 25, 2020 - 2:01:12 PM - Greg Robidoux Back To Top (87848)
Hi Ewa, take a look at this article:


Tuesday, July 21, 2020 - 7:53:14 AM - Ewa Back To Top (86163)

Great tutorial. It worked for simple select query. But I have query which pivots data in dynamic way, so contains also parameters. I am not sure how to modify your method so that it works with below query:

Create procedure dbo.ContractProcedure @col as nvarchar(MAX) 
, @query as nvarchar(max)
, @Contract_ID as NVARCHAR(10)
As Begin
-- SET NOCOUNT ON added to prevent extra result sets from
select @col = STUFF((Select ',' +QUOTENAME(ContractProductID) from [dbo].[SWAP_Contracts2020]
where ContractProductID not like 'B%' and ContractProductID not in ('SVC_PLN','SSPPG') 
and @Contract_ID =[ContractID]
group by ContractProductID
order by ContractProductID
set @query = 'Select [ContractID],[SerialNumber],' + @col + 'from
select distinct [ContractID],[SerialNumber],[ContractProductID],sum([ContractObjectNetPrice]) 
as ContractObjectNetPrice
from [dbo].[SWAP_Contracts2020] as p
where @Contract_ID = [ContractID] and p.ContractProductID not in (''SVC_PLN'',''SSPPG'') 
and p.ContractProductID not like ''B%''
group by [ContractID],[SerialNumber],[ContractProductID]
) as pp
(  sum(ContractObjectNetPrice) for ContractProductID in ('+ @col + ')
) ppp'
execute (@query);

Tuesday, April 28, 2020 - 1:09:09 PM - Ste McK Back To Top (85488)

Fantastic, I appreciate your sharing this.

I was changing it to take the data from the selection of a drop down list so had to add for my variable DropDownChoice="" then set the variable to the drop down list selection.  It was adding blank space otherwise.

Also, a note to anyone using the guide, read what Junaith says when he typed"select any table".

I was not reading and then wondering why I could not see my procedure.

Wednesday, October 2, 2019 - 6:25:44 AM - Jijo Back To Top (82645)


I used this example, it was awesome, but when I copied my excel file to another computer the connection failed. We got visual basic error "Run-time error 445", Object doesn't support this action. How we can run this file in a different machine?

Tuesday, May 7, 2019 - 9:02:57 AM - harish Back To Top (79971)


I have an excel sheet with column having values which are generated by using Excel Functions most of the cases, and one of the column with macros. I have migrated this to SQL Server through Query Wizard, but the values transformed remained static values not as the functions in the Excel sheet.

Is there a solution or procedure to do it, this is what we are taking about converting thousands of rows with one cell value having different as others for the same column.

Kindly help me out with the solution and explain with an example in case possible.



Wednesday, September 19, 2018 - 9:20:46 AM - Elena Back To Top (77655)

 It was really helpful, thanks

Wednesday, August 1, 2018 - 5:29:44 AM - Ansie Back To Top (76925)

 Hi Junaith,

I have been using this example in one of my project and it was awesome, awesome.


I tried it again with another stored procedure again with different parameter.  I do not think that is the problem tough.  IT is giving

me the errory "The query did not run, or the database table could not be opened. This is happenning when I change the table connection to SQL and

add the execution of the stored procedure. it is completing the stored procedure but the connection remain to be "table" because it isnt finishing the update.


Could you perhaps have some answers. please





Wednesday, July 18, 2018 - 3:24:30 AM - Ansie Back To Top (76668)

 This has been so helpful.   WOW!!!


Monday, April 16, 2018 - 1:34:38 PM - Alex Dyer Back To Top (75715)

 Very helpful article. However I'm having a couple of issues I'm running Microsoft 2010. When I tried to pass the empty values I get an error on the connection properties in excel. The error saying that report has no parameters and arguments were supplied. Also the vba code I'm getting error user defined type not defined.

Can you please advise.


Thursday, January 4, 2018 - 10:27:29 AM - Sanjeev Back To Top (74763)

 Hi Junaith,

Thank you for the tip. But i need to pass multiple Values in one of the parameter. i see few people have asked the same question, but i dont see an answer.

So i have 3 variables. First 2 are start and end date. Third variable is for region, like India,China,Japan,US,UK etc. i need to pass multiple selections like region in ('India','China','Japan')

Thanks again for your help.


Monday, December 11, 2017 - 10:38:40 AM - Jignasha Back To Top (73880)


 Thanks for the useful post.It's really helpful.

Tuesday, October 17, 2017 - 9:20:15 AM - Benjamin Back To Top (68477)

Hi Junaith, thanks for such a wonderful post.  Could you show us how to add a message box if there's no record returned by the query?


Tuesday, October 17, 2017 - 12:43:28 AM - Randy Davis Back To Top (68461)

Great tip--almost exactly what I was looking for.  Is it possible (OK, I know almost anything is possible from a VBA coding perspective). Can a parameter field be set up so that multiple values can be chosen? I expect that there would be a variable number of cells (1 - N) that would be populated by the end user and would be used to create an IN clause (WHERE CustomerCode IN('value1','value2',...,'valueN') have need to include a multi-value parameter. How might this be possible?

Saturday, August 19, 2017 - 1:51:40 AM - Bhuvneshwar Back To Top (65036)

I am getting an error saying 'Cannot convert from nvarchar to datetime' when I write script in connection property and after run.

Please help me asap.

code is given below

CREATE PROCEDURE [dbo].[SOC_Report] @StartDate as datetime, @EndDate as datetime

select OCMQ_Number as 'SR Number',
--expr1 as'SLA Breach'
and [op_time] between @StartDate and @EndDate




Wednesday, August 16, 2017 - 5:38:08 AM - Mary Back To Top (64905)

 Hi. I need the reverse solution. how can I send textbox value from excel to SQL by VBA?

kindly please hint me. Thanks.

Monday, July 10, 2017 - 6:13:15 AM - Ben Back To Top (59183)


I am getting an error saying 'Cannot convert from varchar to date' when I click the command button.

Any help will be much appreciated.


Michele, I note that you had come across the same error. Would you be able to share the solution, please..


Thank you.





Friday, June 9, 2017 - 10:26:19 AM - prakash Back To Top (57016)

 thanks for the post. this is helpful to use parameter in sql query without going for msquery.

thanks a lot


Tuesday, June 6, 2017 - 2:15:21 PM - Michele Back To Top (56895)



Grateful for the detail and step by step in this post. Thank you!

I followed this verbatum except I only needed one date filter. The issue I ran into was the error 'Cannot convert from varchar to date'. It turned out that, in my procedure, I had to cast as date the @xyzdate in the where clause - even with the declaration at the start of the procedure.

Version SSMS: 2016

Version Excel: 2013

Wednesday, May 24, 2017 - 4:44:47 AM - Manish Sharma Back To Top (55998)



IS there any way to run HIVE queries and pass the parameters values? I am able to connect with the HIVE database and can see the data, but as you are calling the procedure and passing the variables. Is there any way to do the same with a select query ? say "select * from table where t1 = ? and t2 =? " where t1 and t2 will be user given in the button input section.

Monday, February 27, 2017 - 7:13:01 AM - Parvesh Kathuria Back To Top (46727)



Article is good...but I have a query...we have a database for our ERp which is developed by third party and I do not want to create SP in the dtabase, rather I want, I should be able to run the SQL Query from within Excel to fetch the records from SQL Server....this way I shall not be disturbing our ERP Developer Software...How I can do this...


Please advise and help...


parvesh kathuria.

Sunday, February 26, 2017 - 1:29:16 AM - Paul Back To Top (46693)

 Is there a why to pass multiply value parameter to sql?

Wednesday, February 8, 2017 - 11:37:55 AM - Jason Brandwist Back To Top (46112)

Hi Junaith,

You have been a great help with this code, so thank you for this. Quick question though, is there a way I can run more than one stored procedure in the code you have provided, as all the examples I have seen show only one sp and the output used. However I need to have two stored procedures fire from the same database, without modifying the exisiting stored procs I was wondering if it's possible and if you or the readers can help me out?




Tuesday, August 9, 2016 - 4:11:46 AM - anthony Back To Top (43081)

Just a quick query (i hope) im able to replicate your connection when using an Adventure Works Db, but when i try to connect to a different db on the same server using this methodology i get the following error.

"The query did not run or the database table could not be opened."

Now i've run the procedure manually both in PQ and SQL and it works fine and if i just leave it as the table connection that works fine. However adding this procedure to the connection causes it too fail.

Any ideas?





Wednesday, July 13, 2016 - 7:35:02 AM - sam Back To Top (41879)


This was so informative and helpful, I tried doing this but replaced the table statement with sql query , but when I enter the value in the cell it is not pulling the value to the query but it simply refreshes. Can you pls help on this

Tuesday, May 24, 2016 - 2:28:24 PM - Al B Back To Top (41553)

 I am using 'With ActiveSheet.ListObjects.Add' using an ODBC driver to query some mainframe tables.  The user enters the query into a cell and I take it out, put it into a String and put the String variable in the CommandText statement. (ex - .CommandText = sSQL)  If it's longer than 255, I do something like  .CommandText = sSQL & sSQL2.  Sometimes we have very large SQL statements and since a cell can hold many more characters, it would be good to pass the cell contents.

My question is, can you pass a cell or reference to a cell into the .CommandText value? I can't find examples anywhere or know what the reference would look like. 

Thanks, Al




Thursday, March 24, 2016 - 12:55:46 PM - Beniella Back To Top (41048)

 This was sooo helpful!!! Thank you soo much!!! I dont have much experience with macros but with stored procs so I can say my first try was pretty awesome!!


Thanks and continue to help others :)

Friday, January 29, 2016 - 6:00:44 PM - Kaitlyn Back To Top (40550)

Im getting an arror running this code, Error being "Msg 102, Level 15, State 1, Procedure TEST, Line 25

Incorrect syntax near '@EndDate'." What am i doing wrong?



CREATE PROCEDURE dbo.TEST @StartDate as Date, @EndDate as Date



-- SET NOCOUNT ON added to prevent extra result sets from



-- Insert statements for procedure here

















  FROM [BIAnalytics ].[dbo].[Collectionskbw]

  where [OrderDeliveryActual] between @StartDate and @EndDate




Wednesday, January 27, 2016 - 7:36:29 AM - David Heutel Back To Top (40513)

 I located the problem.  "Background Refresh" must NOT be enabled in the Properties section of the data table.  Why?  I don't know. But it definitely fixed the issue


Sunday, January 24, 2016 - 5:09:43 PM - David Heutel Back To Top (40487)

 I used your instructions exactly as written.  Everything works fine with the command button, however the table will only refresh if the dates entered into the INPUT fields are a longer period than the previous entry.  For example, if I start with the range 12/1/2015 - 12/31/15, the table refreshes and provides the correct data.  If I then enter 11/1/2015 - 12/31/15, I get updated results.  No problem.  But if I change the dates back to 12/1/2015 - 12/31/2015, the data does not refresh.  I have to manually refresh the table to get the correct results.

I examined the Parameters in the Command Text, and they are being passed correctly.  Here is my VBA:



Private Sub CommandButton1_Click()

Dim StartDate As Date  'Declare the StartDate as Date

Dim EndDate As Date    'Declare the EndDate as Date


StartDate = Sheets("COMBINED Totals").Range("K1").Value   'Pass value from cell K1 to StartDate variable

EndDate = Sheets("COMBINED Totals").Range("K2").Value     'Pass value from cell K2 to SellEndDate variable


'Pass the Parameters values to the Stored Procedure used in the Data Connection

With ActiveWorkbook.Connections("spCombinedSalesList1").OLEDBConnection

.CommandText = "EXEC dbo.spCombinedSalesList1 '" & StartDate & "','" & EndDate & "'"


End With

End Sub 


Wednesday, December 30, 2015 - 4:30:57 PM - Tania Back To Top (40333)

 This is exactly what I was looking for. I am very new to this whole sql realm, can you explain how we can modify the stored procedure to meet our needs? 




Friday, October 2, 2015 - 9:21:06 AM - osvaldo Back To Top (38812)

Fantastic, really helpful thank you so much!!!!

Wednesday, September 30, 2015 - 11:44:54 AM - Kiki Back To Top (38795)



Thanks for this piece of information. It has helped tremendously.


I am struggling, however, with the same issue you have, @Braden.


I cant seem to get the button to refresh the data in excel. When I look at the connection properties, the parameters actually feed in to the command text area ( and in to the SP). So there seems to be an issue with the refresh part of this process.


I have used this method for one of my other reports and had no issues.


Thank you in advance.

Monday, June 29, 2015 - 2:39:04 PM - Vinay MV Back To Top (38063)
I need a command that has to take list of names form the excel , insted on ? can i give a command that has to take list of names from the excel and has to retrive only the selected names given in the command.
Please note i have set of 200 names so i cant type all the names in the query so i can ref the name with the excel sheet.
Please assist.

Thursday, June 25, 2015 - 1:43:24 PM - Jeff Stubing Back To Top (38038)


THANK YOU SO, SO MUCH for taking the time to create such an in depth and easy to follow solution to a very complex problem.  I have been pulling my hair out on this project, and your guide  made it easy.  Thank you so much!!!


Jeff Stubing

(Irving, TX)

Sunday, June 21, 2015 - 4:51:18 AM - Khushal Back To Top (37973)

Suppose in one report I have three queries. And the data of each queryy is to be pasted on different worksheet in a same workbook.

And the parameter for each query is same.

How can I run all the three queries at a time using the same parameter.

As refreshing all the queries one after the other would consume a lot of time.


I hope there's some solution to this.

Thanks in advance!

Thursday, June 4, 2015 - 10:36:23 AM - Junaith Haja Back To Top (37485)

@kavish and @aditya: Yes, you can use the sql query but you have to use it in the VB script not in the Command text.Replace the EXEC statement found in the above code with your select statement.

Once you replace the VB code with SQL Query you will see the SELECT statement passed to command text.



Thursday, June 4, 2015 - 1:08:46 AM - Kavish Back To Top (37375)

do u have any sample code..because i tried.. but i could not get solution..

Wednesday, June 3, 2015 - 3:10:07 PM - Aditya Back To Top (37373)

I there any way we can use sql query instead using store procedure in comandtext and pass paramters from excel cell and get the data

Tuesday, June 2, 2015 - 7:29:44 AM - Kavish Back To Top (37354)

is it possible to do with sql queries instead of using store procedure.

Because i don't have any access to create procedure to my database

Friday, May 15, 2015 - 6:27:31 PM - Braden Back To Top (37186)

@ Junaith: Yes I have that line in my code. I've also tried different variations of it and still no dice.

Friday, May 15, 2015 - 3:21:36 PM - Junaith Back To Top (37182)

@Kevin S: you should give it in the command box as "EXEC sp_EXPORT_SURVEYS_AS_FLAT_FILE ' "& siteid & " ' "

Check my above code in the CommandButton Function.

@Braden: Do you have the ActiveWorkbook.Connections("Your excel connection Name").Refresh in your code?

Also check  your Refresh options in your connection properties.

Thursday, May 14, 2015 - 9:46:21 AM - KevinS Back To Top (37173)

This is a very thorough and excellent example.

I am very new to importing into excel from SQL.

I am trying to Import my SQL Stored Procedure into excel with a Parameter called @siteid which is a INT.

Excel is giving this "Error Converting datatype varchar to INT"

How would I word it in the Command Text box to work around this?

This is how I am wording it now that causes the error.


Wednesday, May 13, 2015 - 1:05:53 PM - Braden Back To Top (37167)


I've checked all of the names and everything checks out. I've ran some tests on it and it stops refreshing the second time button is clicked. Meaning - I enter the parameter, click the button and it works, change the parameter, click the button and it doesn't work. The file extension is .xlsm and the names of everything are fine. Would it be the refresh option in the VBA?

Tuesday, May 12, 2015 - 12:18:05 AM - Manik Back To Top (37153)

Please ignore my previous request. I was able to solve the issue by using format function in below section of the code.

With ActiveWorkbook.Connections("TEST_TMS_DATA").OLEDBConnection
.CommandText = "EXEC DBO.TMS_DATA_TEST  '" & Format(STARTDATE, "yyyy-mm-dd") & "','" & Format(ENDDATE, "yyyy-mm-dd") & "'"

Thanks for your help!!

Monday, May 11, 2015 - 11:56:26 PM - Manik Back To Top (37152)

Thanks Junaith. Even after using the date function the date parameters in connection string in excel keeps changing back to the format below.

EXEC DBO.TMS_DATA_TEST '1/05/2015','6/05/2015'

On SP I am using convert function on the actual date field (TMS_date) and also on the parameters (STARTDATE AND ENDDATE)


My SP retrieve data correctly in SQL.

I am thinking problem is within the code below:

Dim STARTDATE As Date  'Declare the StartDate as Date
Dim ENDDATE As Date    'Declare the EndDate as Date

STARTDATE = Format(Sheets("Sheet1").Range("B2").Value, "YYYY-mm-dd") 'Pass value from cell B2 to StartDate variable
ENDDATE = Format(Sheets("Sheet1").Range("B3").Value, "yyyy-mm-dd") 'Pass value from cell B3 to EndDate variable

'STARTDATE = Sheets("Sheet1").Range("B2").Value 'Pass value from cell B2 to StartDate variable
'ENDDATE = Sheets("Sheet1").Range("B3").Value 'Pass value from cell B3 to EndDate variable


'Pass the Parameters values to the Stored Procedure used in the Data Connection
With ActiveWorkbook.Connections("TEST_TMS_TEST").OLEDBConnection
.CommandText = "EXEC DBO.TMS_DATA_TEST  '" & STARTDATE & "','" & ENDDATE & "'"
End With
End Sub



Monday, May 11, 2015 - 11:41:44 AM - Junaith Haja Back To Top (37147)

@Manik: Yes it defaults to MM-DD-YYYY type.You have to custom define the cell in YYYY-MM-DD format for it to work.You can achieve this in 2 ways.

1. Right Click the Cell where you input the parameter and open Format Cells. In the Custom tab type as YYYY-MM-DD, it gets changed.

I tried it now, it works for me.

2. Use the function CONVERT(CHAR(10),STARTDATE,126) in your SP and will convert your input variable to YYYY-MM-DD format.

Monday, May 11, 2015 - 11:28:03 AM - Junaith Haja Back To Top (37146)

@Braden: Am glad it helped you, Looks like you haven't configured the ActiveX Command Control Button to work properly.Please double check your file with the VBA code part mentioned in this tip and I would recommend you to do the following as sanity check.

Make sure the sheet name and the cell name are changed in your VB code as per your Excel file, Re Check your connection name and parameters for the SP in the VB code.Check if you have inserted the above code in the same method for the button.

Make sure you have saved your file as Macro Enabled format (.xslm).let me know if the issue persists.


Monday, May 11, 2015 - 8:14:46 AM - Manik Back To Top (37145)

Thanks for the post Junaith. I am trying to convert date parameter in YYYY-mm-dd format however when ever I click on Command button it default back to dd-mm-yyyy. Could you please help on how I can achieve this. I have tried various date function but no luck. On my stored proc I have STARTDATE AND ENDDATE AS DATETIME VARIABLES.

Thanks again



Saturday, May 9, 2015 - 6:22:26 PM - Braden Back To Top (37139)

So I've gotten your amazing example to work and I thank you! However, when I change the parameter and click the button again, it does not refresh the query. The only way to refresh the query is through the debugger, or manually with the Refresh All button on the Data tab. I'm running Excel 2007. Is there a way to do this by clicking the Button every time a parameter is changed?


Thanks in Advanced - Braden

Wednesday, April 15, 2015 - 3:00:14 PM - Junaith Back To Top (36944)

@Rees: Appreciate your kind words and glad it helped you..!!

Wednesday, April 15, 2015 - 12:17:19 PM - Rees Back To Top (36940)


Just wanted to say thanks for the post.  Works very nicely and will be very useful for the future.

The only thing that tripped me up (apart from my typing) was that I needed to create a db_executor role in SQL Server and grant that to the users.  Thankfully I don't have to fight with Sharepoint.


Friday, April 3, 2015 - 10:39:01 AM - Junaith Haja Back To Top (36815)

@Martin Bierbaumer: I have published the file in my sharepoint and I see its working without any issues.

Looks like Security Options in your Internet Explorer is High and please do reduce it to Medium to have ActiveX control work.

Also, when you published the file it should prompt you to Allow ActiveX control to use.

Try adding your sharepoint to the Trusted sites and you should be all good.

Friday, April 3, 2015 - 2:54:47 AM - Martin Bierbaumer Back To Top (36805)

@Junaith Haja: Thanks for the ideas, works quite well in Excel but as soon as i publish onto SharePoint with Excel services the button is gone. Any ideas for that?

Friday, February 6, 2015 - 12:11:50 PM - Junaith Haja Back To Top (36177)

@Szabti: Thanks for your feedback. You can publish it to Sharepoint and I would advise you to publish only the tab we are using instead of publishing entire book, it may ask for couple of security warnings - accept it. I tested in my sharepoint, it did worked.Do let me know if you face any issuues.

@Colleen: sorry am not aware of it without using VB.

@Henn: we wanted to have control over the data refresh so did it this way.

Thursday, February 5, 2015 - 1:31:30 AM - szabti Back To Top (36156)

That's great! Thanks!

But what about when I'm going to publish the workbook to the SharePoint 2010 Excel Services? How can I make it work with dynamic parameters?

Monday, January 26, 2015 - 1:16:15 PM - Colleen Back To Top (36058)

I am looking for a way to dynamically change the *connection string*.

That is, we have databases on many servers, If people put the account number in a cell, I would like to lookup the server from a table, then do the rest of the lookups by changing the server name on other data connections.

How do you do this?

DBA no longer VB-coder 

Monday, January 26, 2015 - 1:17:30 AM - Henn Back To Top (36051)


We are using for same purpose more simple way


1. Data connection is MS Query

2. Query contain ? parameter placeholders

3. On MS Excel I define parameter bounded CELLs and check mark that query will rerun when cell values changing

get free sql tips
agree to terms