I need to produce a highly formated pivot table report in Access and can only achieve so much. However, if I could programatically select the pivot table (equivalent of keys [Ctrl]+) then copy and paste into Excel, I can use VBA to format the report in Excel.

My problem is that I cannot find a way to programatically select the pivot table in Access. I have tried Sendkeys "^A" as a last resort but this doesn't work. Exporting to Excel doesn't work either as it exports the pivot table, not the result.

Hi Rick,

Suggest that you try send the recordset (if it is small enough) to an Excel spreadsheet with can contain your pivot table .

I have an App which is too large for this so I work through the recordset and use VBA to write the data into the appropriate cells of my report spreadsheet. Both methods work OK

Thanks for your interest however the pivot table in Access can do things which I don't think Exel can. In particular, I do not want to perform any kind of summary analysis on the data e.g. Sum, Count etc. The Access Pivot table can show 'detail' e.g. text as well as summarised 'data'. It is the detail I want and can produce in Access, however I also want contitional formating and a few other things which pivot table forms can't do.

I therefore thought I would copy and paste the Access pivot table to Excel where I can complete the formatting using VBA but I am really stuck for a way to automate "Select All" i.e. pressing the "Control" key and the "A" key together.

Every other way I have tried e.g. exporting to Excel opens an Excel Pivot table. This applies the Count function to the text details I am reporting which is not what I want.

Hi Rick,

Have you had any luck resolving this problem?

I am also trying to copy the results from a pivot table control and paste the results into Excel.

KD

Try this.

"str_spreadsheet_name" holds the name of a file that has already been exported and Excel is open and running.

"obj_xls" then picks up the open spreadsheet.

It then creates pivot table called "Holdings" in the Excel sheet at AQ1, with rows being "rec_id", columns being "fund_name" and pivot field being "sum of fund_value".

It then copies the pivot table and does a patsespecial - values only. This removes all of the pivot table formatting.

It then tidies up the headings etc. If you run the process in debug you'll be able to see it working line by line.

Hope this is of use.

KD

Private Function reformat_fund_totals()