Unanswered: Manipulating Pivot Table in MS Access 2000
Hi to one and all
OK this may be an easy one... but I've yet to find the answer.
I've got a form from which I generate a piece of SQL in VBA using a number of different options (have to do this because of multi-selects), and I want to display the results in a sub-report or sub-form pivottable in printable format. The issue is that the format is not always the same, so that the user might want to have x and y as rows, z as a column, and the sum/average/count of q as the datafield - in other words generate their own pivottable, but one that's pre-formatted and set up for printing.
Doing this is pretty simple in VBA for Excel, where you just build the pivottable in code as you go along, applying any specialist required formats. However, it appears that once you generate the pivottable in an Access 2000 form, it is impossible to edit or change any component of it except by opening it for editing. It also does not seem possible to change the datasource except by regenerating the whole form.
So to do this do I have to open an Excel instance in VBA and edit the pivottable? If so is it best to have the Excel pivottable linked as an OLE object and dump the data out to the file? Or is there a simpler way using some arcane property that I don't know of? The other way would be to TRANSFORM, and create a report, but I don't know how to do this being an Excel person primarily.
OK I've now got it working... sort of. Just needed to remember when constructing the array for the pivottable that the first element was 0...
I'm using echo to switch of access updates and screenupdating to switch off excel ones. How does one manage the crossover where you actually open the excel instance? Can you open an OLE object in a hidden instance, whilst retaining the semblance of normality on the form at the front, until pouf! all is mystically revealed?