Suggestions: Best Way to Integrate Excel with Access - Pivot Tables

[Johnny Meredith]

Dear All,

I have an Access database that tracks the progress of income tax
audits. When the taxing authorities make a change (an "Adjustment"), I
record the pertinent information in the database. At the end of the
audit cycle, these adjustments are used to compute the revised taxes
due. This computation is done in a fairly complex, but accurate and
easy to use, spreadsheet.

In the spreadsheet, there is a sheet that lists out all the adjustments
in a record format (ie - a row is a record, columns are fields, and the
range is named for easy reference.) On another sheet, there are pivot
tables that summarize this data in various ways. From there, other
parts of the spreadsheet reference these pivot tables to drive the
computation.

Currently, the spreadsheet and the database don't talk. I would like
to integrate them to prevent the redundant storage of the adjustments.
I would like your thoughts/war stories on the best way to integrate the
spreadsheet and database. Should I write something to pull the
adjustments over, and then refresh the pivot tables? Should I point
the pivot tables at a query in Access? I'm really looking for you
opinions before I start down a rabbit hole and have to climb back out
and try something different.

As always, TIA for you highly-valued opinions,
Johnny Meredith

[Chuck Grimsby]

Personally, since there are aspects of the Excel Spreadsheet that you
can't control, I'd recommend having Excel pull the data out of Access,
rather then having Access "send" the data to the Excel spreadsheet.
This will aso overcome some rather interesting aspects of file sharing
that are not really controlable from Access.

Anyways, any competent Excel programmer should be able to help you in
this task, or if you prefer, you can check out some of the Microsoft
KnowledgeBase articles on how to get data out of an Access database
from Excel.

Last time I looked, there were more then a few examples in the KB, as
well as some "templates" in the downloads section of
<http://www.office.microsoft.com/> , which is pretty much the _best_
resource for samples on the net. (IMHO, and no I don't have anything
there.)
Johnny Meredith wrote:

I have an Access database that tracks the progress of income tax
audits. When the taxing authorities make a change (an "Adjustment"), I
record the pertinent information in the database. At the end of the
audit cycle, these adjustments are used to compute the revised taxes
due. This computation is done in a fairly complex, but accurate and
easy to use, spreadsheet.
In the spreadsheet, there is a sheet that lists out all the adjustments
in a record format (ie - a row is a record, columns are fields, and the
range is named for easy reference.) On another sheet, there are pivot
tables that summarize this data in various ways. From there, other
parts of the spreadsheet reference these pivot tables to drive the
computation.
Currently, the spreadsheet and the database don't talk. I would like
to integrate them to prevent the redundant storage of the adjustments.
I would like your thoughts/war stories on the best way to integrate the
spreadsheet and database. Should I write something to pull the
adjustments over, and then refresh the pivot tables? Should I point
the pivot tables at a query in Access? I'm really looking for you
opinions before I start down a rabbit hole and have to climb back out
and try something different.