| re: Suggestions: Best Way to Integrate Excel with Access - Pivot Tables
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:[color=blue]
> 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.[/color]
| 
