Excel 2010 Data Connection - Add Filter to Command Text

[Begbie00]

Two relevant workbooks (sitting in the same shared drive):

Historical Data.xlsx: with a table GLDATA, which includes a field cc
Template.xlsx: with a named range COSTCENTER

Question: How do I modify the Command Text in the Data Connections Properties dialog box (using either SQL or Table syntax) to only pull those records from GLDATA into Template.xlsx where cc = COSTCENTER

What have I tried so far

SQL Syntax: SELECT * FROM GLDATA WHERE GLDATA.cc = COSTCENTER

Error: Syntax Error in FROM clause.

 

[Fazza]

hi.

The cc value is text and needs delimiters - 'COSTCENTER'. These would not be required if cc was numeric.

SELECT * FROM GLDATA WHERE GLDATA.cc = 'COSTCENTER'

SELECT * FROM GLDATA WHERE GLDATA.cc = 123

regards

 

[Begbie00]

Sorry, I wasn't clear. COSTCENTER is a named range (1 cell) in which the value of cost center exists. So your second example (123) needs to change based on a cell value, not be hard coded into the command text.

I thought there was a way to do this with "?".

 

[Fazza]

I didn't read the original post carefully. Sorry. You clearly wrote about the named range.

Yes, there is an approach with a ?. Separately there is also VBA to change the SQL.

I think the "?" approach is what you want. Exactly how it is implemented might be version specific. I'll investigate and reply later about Excel 2010. Basic idea is to edit the SQL however you can (normal Excel worksheet interface or via VBA. One way in VBA is the immediate window) so that the SQL has a ?. that would be

SELECT * FROM GLDATA WHERE GLDATA.cc = ?

A while since I've done this, it may instead be with bracket delimiters.

SELECT * FROM GLDATA WHERE GLDATA.cc = [?]

Having done that & refreshed the query you should be prompted for a value for the cost center parameter. At this stage there should be a choice to select the named range that you want and also to select for the query to refresh on every change of that cell.

For info, the separate way with VBA might have some event code run on change to the named range and it simply edits the SQL (text) to have the named range's value loaded.

 

[Fazza]

Maybe right near the end of this link Excel 2007 - Getting External Data - Nick Hodge's Excel Blog - Excel User Group
that dialog box is like the one you see in Excel 2010?

I'll stop for now. Please let me know either way if you resolve this.

regards