Why can't I seem to get data from MS Query to subtotal? (Excel 2007)

[RRinTetons]

I'm very accustomed to getting a whole bunch of data from SQL Server into Excel via a query and then slicing and dicing it there. Since moving up to Excel 2007 this seems to be unreliable.

The query results are returned as usual, and filtering has been helpfully turned on for each column. Unfortunately the Subtotal tool in the ribbon is grayed out and nothing I can do seems to change that short of saving the whole worksheet as plain text and reopening it!

Is this a feature I've missed?

-
Richard Ray
Jackson Hole Mountain Resort

 

[RRinTetons]

Hmmmm. It won't Group, either. It seems like Excel isn't recognizing that the data is sorted. I resorted it a few times to try to get Excel to 'see' that, but no joy.

-
Richard Ray
Jackson Hole Mountain Resort

 

[xlbo]

How is the data returned? Using a querytable (Data>Get External Data) or ADO through VBA?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[RRinTetons]

Data -> Get External data using a stored procedure with parameters drawn from the workbook via {Call dbo.xxxxx(?,?,...)}.

In an Excel 2003 workbook subtotal and Group are available as expected - same query, same server, same DB, same parms.

If I select the entire data table returned, copy it, add a new worksheet to the workbook and Past Special, Values Only, Paste Special, Formats and add the filtering back in it look exactly like the data returned from the query, but it allows subtotal and group to work.

-
Richard Ray
Jackson Hole Mountain Resort