R
RickW
I am using a pivot table form in Access 2003 to get report data into the
layout required, but the Access pivot table doesn’t do everything I want e.g.
conditional formatting, merging blocks of identical data etc.
I can however manually copy the pivot table into Excel where VBA does all
the formatting required to present the report, however have met one big
problem in automating the process of getting the pivot table from Access to
Excel. The methods I have tried end with one of two results:
1. An Excel pivot table – this is no good as Excel has to do analysis on the
data e.g. sum, or count whereas my data is simply text which I want to remain
as text.
2. The original data from the query is returned as though it had not been
put in a pivot table.
So it’s back to Selecting the pivot table on the Access form, copying to
clipboard and pasting to a new spreadsheet. The problem is I can only select
manually by using the keys Control and A together, and there seems to be no
way to automate this – not even with the dreaded Sendkeys (which has
absolutely no effect).
Finally I have looked at using a crosstab query but this is no good as it
can only accept one column item and one value item.
Thanks in anticipation
Rick
layout required, but the Access pivot table doesn’t do everything I want e.g.
conditional formatting, merging blocks of identical data etc.
I can however manually copy the pivot table into Excel where VBA does all
the formatting required to present the report, however have met one big
problem in automating the process of getting the pivot table from Access to
Excel. The methods I have tried end with one of two results:
1. An Excel pivot table – this is no good as Excel has to do analysis on the
data e.g. sum, or count whereas my data is simply text which I want to remain
as text.
2. The original data from the query is returned as though it had not been
put in a pivot table.
So it’s back to Selecting the pivot table on the Access form, copying to
clipboard and pasting to a new spreadsheet. The problem is I can only select
manually by using the keys Control and A together, and there seems to be no
way to automate this – not even with the dreaded Sendkeys (which has
absolutely no effect).
Finally I have looked at using a crosstab query but this is no good as it
can only accept one column item and one value item.
Thanks in anticipation
Rick