MS query return multiple columns in Excel

[Guest]

I am using the MS query feature in Excel to import data from a SQL database.
I am inputing several parameters (start date/time, stop date/time, tagname)
from parameters linked to Excel cells (user modifiable).
The data is returned as a date/time column and corresponding data column.
The query works fine but only when I limit it to returning the date/time and
one other column of data.
If I try and return multiple data columns (in addition to the date/time) I
get error message:
"parameters are not allowed in queries that can't be represented graphically"
Is there any way around this without resorting to programing the query with
VB?

I tried resorting to a Pivot table for the data but I could not figure out
how to automatically update the Pivot table when the query updated?

{It also took me forever to figure out how to link the MS Query parameters
to Excel cells - you use ?'s in the query and then must supply something to
fill-in the parameter as you close MS Query. Then you can link the
parameters to Excel cells by right clicking on the data field - none of this
is well documented}

Thanks

 

[JakeyC]

The problem with the "Parameters are not allowed in Queries that cannot
be displayed graphically" will occur more and more often as your
queries become more advanced I'm afraid. Why it can't cope, I haven't
got a clue but I expect you were using some form of subquery or
'EXISTS' clause or similar?

Try re-writing the query using a different structure, or (as I often
have to), write two separate queries that refresh together, reside next
to each other and share parameters but generate the same end result
visually.

If your data is able to be represented by a PivotTable, then once you
click Get Data... it will go to MSQuery and no doubt the same problem
will occur. If you get that far, to refresh the PivotTable, use
something like
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh in VB,
preferably in the same event that causes the data refresh (a cell value
change, etc) otherwise see Help for the before- and after-refresh
events.

Yes, parameters are ? in MSQuery and it has an annoying habit of
forgetting them, requiring endless re-entering during the development
stage. To give the prompt box a meaningful name, use '[Enter Date]' (no
quotes) in the parameters row.