I've been given a workbook that has a pivot table which gets its data
from an Access database by way of MS Query. Once the pivot table has
been updated it is sent to users who can view data based on the Month
and Year selected in the Page area.

I need to add a row that displays data outside of the pivot table,
based on the month and year the user has selected. The data I'm adding
is a summary of other data in the same dataset. I can capture the
month and year selected by the user, but I need to know how to access
the data. I'm thinking the data is in memory when the workbook is
being used. How do I get to it?

tod

hi,
An MSQ returns a recordset and dumps in into excel. once the MSQ has
finished, it kills the record set so there would be nothing in memory for you
to get to.
The dataset is on your sheet. you can summerize it via macro or formulas.
a second suggestion would be a second MSQ that brings in the summary data
via access query instead of access tables.(i am not sure which you first MSQ
uses. both a viable import media) If you do have a second MSQ then you can
set both MSQs to run back to back with a single click.
my thoughts.
regards
FSt1

"todtown" wrote:

I've been given a workbook that has a pivot table which gets its data
from an Access database by way of MS Query. Once the pivot table has
been updated it is sent to users who can view data based on the Month
and Year selected in the Page area.

I need to add a row that displays data outside of the pivot table,
based on the month and year the user has selected. The data I'm adding
is a summary of other data in the same dataset. I can capture the
month and year selected by the user, but I need to know how to access
the data. I'm thinking the data is in memory when the workbook is
being used. How do I get to it?

tod

Thanx for the info. This is what I don't get. The pivot table is
refreshed using the MS Query. But the data is still there even after
the workbook is not attached to any data source. So it has to be
someplace. It's in the pivot table, but there is no sheet of raw data,
not even a hidden sheet. I'm trying to access THAT data, wherever it
is. My alternate idea is to do similar to what you've said, have a
second query that gets the summary data I need at the same time the
pivot table is refreshed, put that summary data in a hidden sheet,
then have my vba code utilize that data. But I'd rather be able to
access the mystery super double hidden data that the pivot table is
reading. Am I making any sense?

tod

On Oct 2, 8:55 pm, FSt1 wrote:
hi,
An MSQ returns a recordset and dumps in into excel. once the MSQ has
finished, it kills the record set so there would be nothing in memory for you
to get to.
The dataset is on your sheet. you can summerize it via macro or formulas.
a second suggestion would be a second MSQ that brings in the summary data
via accessqueryinstead of access tables.(i am not sure which you first MSQ
uses. both a viable import media) If you do have a second MSQ then you can
set both MSQs to run back to back with a single click.
my thoughts.
regards
FSt1



"todtown" wrote:
I've been given a workbook that has a pivot table which gets its data
from an Access database by way of MSQuery. Once the pivot table has
been updated it is sent to users who can view data based on the Month
and Year selected in the Page area.

I need to add a row that displays data outside of the pivot table,
based on the month and year the user has selected. The data I'm adding
is a summary of other data in the same dataset. I can capture the
month and year selected by the user, but I need to know how to access
the data. I'm thinking the data is in memory when the workbook is
being used. How do I get to it?

tod- Hide quoted text -

- Show quoted text -