Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Passing Dynamic Query Values from Excel to SQL Server


By:   |   Read Comments (46)   |   Related Tips: More > Microsoft Excel Integration

Attend these FREE SQL Server 2017 webcasts >> click to register


Problem

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.

Solution

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 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
   SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT
    PR.[Name] ProductName
       ,PS.Name SubCategory
       ,PC.NAME ProductCategory
       ,PM.Name ProductModel
       ,[StandardCost]
       ,[ListPrice]   
       ,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

END

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.

MSSqlTips.com 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.

MSSQLTips.com Select the From SQL Server Connection

Enter the server name in the Data Connection Wizard.

MSSQLTips.com  Enter the Server Name

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

MSSQLTips.com select the database

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

MSSQLTips.com 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.

MSSQLTips.com 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.

MSSQLTips.com intialize the parameters

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

MSSQLTips.com 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.

MSSQLTips.com Enable Developer Tab

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

MSSQLTips.com 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.

MSSQLTips.com

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 & "'"
ActiveWorkbook.Connections("AdventureWorksConnection").Refresh
    
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.

MSSQLTips.com Enter the input parameters

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

MSSQLTips.com 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.

MSSQLTips.com 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


Last Update:


signup button

next tip button



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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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

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

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
AS
BEGIN


select OCMQ_Number as 'SR Number',
OP_TIME,
SEVERITY,
DESCRIPTION,
OCMQ_ASSIGNEE as'ASSIGNEE NAME',
OCMQ_ASSIGN as 'ASSIGNMENT',
ZCATEGORY1 as 'CATEGORY',
CLOSE_TIME,
RESOLUTION,
CLOSED_BY,
ZSTATUS as 'STATUS'
--expr1 as'SLA Breach'
from
dbo.CALL_REGISTER_SR where OCMQ_ASSIGN='SOC Team'
and [op_time] between @StartDate and @EndDate


END

 

 


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

 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

Hi 

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 09, 2017 - 10:26:19 AM - prakash Back To Top

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

thanks a lot

 


Tuesday, June 06, 2017 - 2:15:21 PM - Michele Back To Top

 

 

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

 Hi,

 

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

 

 Hello

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...

Regards

parvesh kathuria.


Sunday, February 26, 2017 - 1:29:16 AM - Paul Back To Top
  •  

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


Wednesday, February 08, 2017 - 11:37:55 AM - Jason Brandwist Back To Top

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?

 

Regards,

Jason


Tuesday, August 09, 2016 - 4:11:46 AM - anthony Back To Top

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

Hi,

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

 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

 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

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

AS

BEGIN

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

   SET NOCOUNT ON;

 

-- Insert statements for procedure here

SELECT

  [SKU]

      ,[SKUDesc]

      ,[ReceivedQty]

      ,[ShippedQty]

      ,[AccountID_InvoiceTo]

      ,[InvoiceTo_Name]

      ,[NotifyTo_Name]

      ,[DCLocation_Name]

      ,[Year]

      ,[Month]

      ,[Billable]

      ,[SUSR5]

      ,[OrderDeliveryActual]

      ,[SKUParent]

      ,[SKUParentID]

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

  where [OrderDeliveryActual] between @StartDate and @EndDate

 

 

 


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

 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

 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 & "'"

ActiveWorkbook.Connections("spCombinedSalesList1").Refresh

End With

End Sub 

 


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

 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? 

 

Thanks

 


Friday, October 02, 2015 - 9:21:06 AM - osvaldo Back To Top

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


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

Hello, 

 

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
Hello,
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

Junaith,

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!!!

Sincerely,

Jeff Stubing

(Irving, TX)


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

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 04, 2015 - 10:36:23 AM - Junaith Haja Back To Top

@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 04, 2015 - 1:08:46 AM - Kavish Back To Top

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


Wednesday, June 03, 2015 - 3:10:07 PM - Aditya Back To Top

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 02, 2015 - 7:29:44 AM - Kavish Back To Top

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

@ 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

@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

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.

sp_EXPORT_SURVEYS_AS_FLAT_FILE'@siteid'


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

@Junaith

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

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") & "'"
ActiveWorkbook.Connections("TEST_TMS_DATA").Refresh

Thanks for your help!!


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

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)

A.TMS_DATE BETWEENCONVERT(CHAR(10),@DATESTART,126)ANDCONVERT(CHAR(10),@DATEEND,126)

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

'MsgBox STARTDATE

'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 & "'"
ActiveWorkbook.Connections("TEST_TMS_DATA").Refresh
End With
End Sub

Thanks,

 


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

@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

@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

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

Manik

 


Saturday, May 09, 2015 - 6:22:26 PM - Braden Back To Top

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

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


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

Hi

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.

Rees


Friday, April 03, 2015 - 10:39:01 AM - Junaith Haja Back To Top

@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 03, 2015 - 2:54:47 AM - Martin Bierbaumer Back To Top

@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 06, 2015 - 12:11:50 PM - Junaith Haja Back To Top

@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 05, 2015 - 1:31:30 AM - szabti Back To Top

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

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

 

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


Learn more about SQL Server tools