I've been scouring the internet for the better part of a week trying to figure this out, so I'm hoping there's an Excel/data ninja here that can help. Problem: I have several SQL queries embedded in my workbook, but need the end user to be able to pass values from cells to the query. For the most part, it'll be a couple of simple values, such as start date, end date, and username. Most of the solutions I've found say to go to Connection Properties > Definitions and add parameters, then add them to the query at the bottom of the box, but this field is always greyed out for me - whether it's regarding a Query or a connection. Additionally, the query I'm running is a monster (I'm trying to make the server do the processing rather than Excel on my local machine), so I need a solution that lets me pass values to a complex query, not just a single table. This can't be very difficult, but I can't for the life of me get any of the answers I've found to work. Also, many of the answers I've found on StackOverflow and other forums are 6+ years old. Attempted Solutions:https://www.experts-exchange.com/questions/28689038/Excel-SQL-Data-Connection-Reference-Cell-with-Date-Value.html was answered by telling the user to add parameters in the Connection Properties > Definitions> Command Text box. When I go there, that entire tab is grayed out. I've found ways to setup parameters, but can't figure out how to add them to the query without kicking a SQL error at runtime.This https://support.office.com/en-us/article/customize-a-parameter-query-addf7cb7-ddf0-442f-a60c-cd7280e201bd page has instructions that should be valid for my version of Excel (2016), but again, they suggest adding parameters and the entire Definitions tab is grayed out. (Also, the images in this page show the ribbon/icons with a blue background, which is from an older version of Excel. So, something was updated?)This post has what looks like the answer, but again, they're trying to edit the query in the Definitions tab, whichI've also seen solutions that involved entering a string of code that looked like VBA code into the query, but those would always just cause syntax errors. I don't have any examples of these handy. What I'm hoping for:I would be eternally grateful for a simple explanation as to why the Definition tab is always grayed out, and a guide on how to make this work. I'm running Excel 2016 (Office365) and the SQL Server 2008. Thanks in advance!

Hi Matt Appel How have you embedded the queries? Is using Power Query an option for you? If so you could paste the SQL into the "Advanced" option box when connecting to your database. I've an article here showing how to use a named cell as a parameter for Power Query https://accessanalytic.com.au/powerquery_namedcells_parameters/

Matt Appel [Content][Column1]{0} is just combining several steps into one. Alternative step by step way using only user interface, except this first step where we call current workbook Here we may filter on StartDate and click on Table in Content column to expand it Right click on first cell and drill it down Result is Generated code will be let Source = Excel.CurrentWorkbook(), #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "StartDate")), StartDate = #"Filtered Rows"{[Name="StartDate"]}[Content], Column1 = StartDate{0}[Column1] in Column1 That's okay to do all above steps manually first few times, but most probably one day you'll want to open Advanced Editor and combine all together let Source = Excel.CurrentWorkbook(), StartDate = Source{[Name="StartDate"]}[Content]{0}[Column1] //#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "StartDate")), //StartDate = #"Filtered Rows"{[Name="StartDate"]}[Content], //Column1 = StartDate{0}[Column1] in StartDate It will be more understandable if you know concept of lists and records and how to work with them in Power Query. That's another story.

I've imported them via Data> Get & Transform Data > Get Data > From Database > SQL Server. From here I add the server, DB, and the query. Usually I'll load to a worksheet, but I've tried loading to a connection only, but have the same issues. This is a good guide, and I think I'm a step closer now, but it still doesn't provide a way to feed my values into the query before sending to SQL for processing. When I get to the Advanced Query editor the existing code doesn't have the same fields as in your example. Keep in mind this is a very simple query I'm using just to get this figured out, then I'll bring out the real, big one. I'm not against learning how to modify the code, but I'd like to have avoided that seemingly unnecessary complexity.

Matt, it looks like StartDate = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content][Column1]{0}, EndDate = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content][Column1]{0}, Source = Sql.Database("Server", "Database", [Query="SELECT * From Invoices Where Workdate BETWEEN '" & Date.ToText(StartDate) & "' AND '" & Date.ToText(EndDate)] & "'")

Matt Appel did you ever figure this out? It is driving me absolutely bananas.

Matt Appel

Copper Contributor

Jun 21, 2018

How do you do a parameter query when not using Microsoft Query?

I've been scouring the internet for the better part of a week trying to figure this out, so I'm hoping there's an Excel/data ninja here that can help.

Problem: I have several SQL queries embedded in my workbook, but need the end user to be able to pass values from cells to the query. For the most part, it'll be a couple of simple values, such as start date, end date, and username. Most of the solutions I've found say to go to Connection Properties > Definitions and add parameters, then add them to the query at the bottom of the box, but this field is always greyed out for me - whether it's regarding a Query or a connection.

Additionally, the query I'm running is a monster (I'm trying to make the server do the processing rather than Excel on my local machine), so I need a solution that lets me pass values to a complex query, not just a single table.

This can't be very difficult, but I can't for the life of me get any of the answers I've found to work. Also, many of the answers I've found on StackOverflow and other forums are 6+ years old.

Attempted Solutions:

https://www.experts-exchange.com/questions/28689038/Excel-SQL-Data-Connection-Reference-Cell-with-Date-Value.html was answered by telling the user to add parameters in the Connection Properties > Definitions> Command Text box. When I go there, that entire tab is grayed out. I've found ways to setup parameters, but can't figure out how to add them to the query without kicking a SQL error at runtime.
This https://support.office.com/en-us/article/customize-a-parameter-query-addf7cb7-ddf0-442f-a60c-cd7280e201bd page has instructions that should be valid for my version of Excel (2016), but again, they suggest adding parameters and the entire Definitions tab is grayed out. (Also, the images in this page show the ribbon/icons with a blue background, which is from an older version of Excel. So, something was updated?)
This post has what looks like the answer, but again, they're trying to edit the query in the Definitions tab, which
I've also seen solutions that involved entering a string of code that looked like VBA code into the query, but those would always just cause syntax errors. I don't have any examples of these handy.

What I'm hoping for:

I would be eternally grateful for a simple explanation as to why the Definition tab is always grayed out, and a guide on how to make this work.

I'm running Excel 2016 (Office365) and the SQL Server 2008.

Thanks in advance!

2018

PowerQuery

sql

11 Replies

bwdur
Copper Contributor
Feb 20, 2020
Matt Appel did you ever figure this out? It is driving me absolutely bananas.
- Matt Appel
  Copper Contributor
  Feb 20, 2020
  Sorry, bwdur I never did. I've poured over every forum and StackOverflow post I could find and there's always solutions posted (like the one above), but I can never get them to work.
  
  To update this thread, I'm still running the latest version of Office 365 Edition of Excel, the SQL databases I'm querying are SQL Server 2016 and 2008. PowerQuery is now built into Excel, and i'm using it extensively, but there's still not a clear way to pull a value from a cell and inject it into the SQL code before sending it to the server.
  
  The workbook that spawned all this is still in use and has about 8 different SQL queries that all need the date updated to refresh. What I ended up doing is setting the dates in each query to variables, then just updating the variable dates each time I need to pull data. It's not ideal, but at least it works.
  
  Edit: added some clarification
  - bwdur
    Copper Contributor
    Feb 20, 2020
    Matt Appel
    
    Crazy this used to work fine and dandy with Microsoft Query but lately all of my spreadsheets have been breaking randomly. I did just get this to work finally on a file using this thread with some modifications.
    
    First I named some cells.
    
    Then I went down the 2349 clicks to Advanced Editor and did what the above poster did, except I had to put DateTime instead of Date.
    
    let
    StartDate = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content][Column1]{0},
    EndDate = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content][Column1]{0},
    Source =
    Sql.Database("server", "database", [Query="select myColumn from myTable where MyDate between '" & DateTime.ToText(StartDate) &"' and '" & DateTime.ToText(EndDate) & "' and Annoyed = yes"])
    in
    Source
    
    The location of the ' and " really matter around the parameters to get it to go. I'm surprised there aren't more threads about this online honestly, it is way too difficult to do. I guess maybe other people are returning the entire table then filtering afterwards in Excel?
Wyn Hopkins
MVP
Jun 22, 2018
Hi Matt Appel

How have you embedded the queries?

Is using Power Query an option for you?

If so you could paste the SQL into the "Advanced" option box when connecting to your database.

I've an article here showing how to use a named cell as a parameter for Power Query

https://accessanalytic.com.au/powerquery_namedcells_parameters/
- Matt Appel
  Copper Contributor
  Jun 22, 2018
  I've imported them via Data> Get & Transform Data > Get Data > From Database > SQL Server. From here I add the server, DB, and the query. Usually I'll load to a worksheet, but I've tried loading to a connection only, but have the same issues.
  
  This is a good guide, and I think I'm a step closer now, but it still doesn't provide a way to feed my values into the query before sending to SQL for processing. When I get to the Advanced Query editor the existing code doesn't have the same fields as in your example. Keep in mind this is a very simple query I'm using just to get this figured out, then I'll bring out the real, big one.
  
  I'm not against learning how to modify the code, but I'd like to have avoided that seemingly unnecessary complexity.
  - SergeiBaklan
    Diamond Contributor
    Jun 22, 2018
    Matt, it looks like
    
    StartDate = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content][Column1]{0}, EndDate = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content][Column1]{0}, Source = Sql.Database("Server", "Database", [Query="SELECT * From Invoices Where Workdate BETWEEN '" & Date.ToText(StartDate) & "' AND '" & Date.ToText(EndDate)] & "'")

Forum Discussion

How do you do a parameter query when not using Microsoft Query?

11 Replies

Resources