Hello,

I have the following problem: I have a very long, complex SQL query
that I am using to back a PivotTable in Excel. The query gathers
numerous statistics for a given date range. We would like to enable a
user of the Excel PivotTable to supply a date range in two cells in the
same sheet, then click a button to have the PivotTable dynamically
refresh itself, using these new values as parameters for the SQL query.

To obtain the results for the PivotTable, I have been clicking the "Get
Data" option in the PivotTable Wizard and then pasting my SQL query
into the SQL dialog in Microsoft Query. MS Query tells me that the
query cannot be visualized or something to that effect, so I proceed
anyway. As far as I can tell, this rules parameter queries out at this
point, but I'm hoping I'm wrong.

In addition, I had initially heard that while setting up the PivotTable
in the PivotTable Wizard, one could somehow instruct the Wizard to
separate the query from the Excel application, i.e. in a different
file. If this were the case, I would have a VBA function find this
file, do a regular expression match on the date, and modify the date
range based on the inputs, and then refresh the PivotTable. Is this
approach even feasible? I'm unable to find an option to separate the
query.

I had also tried to use the VBA

ActiveSheet.PivotTableWizard SourceType:=... SourceData:=Array(
longSqlQuery) ...

But it seems that the 255 character limit for the string longSqlQuery
is causing a problem, as my query is way too big for such a small
string.

Does anyone have any ideas?

TIA,

-Nick