Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    London UK
    Posts
    14

    Red face 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.

    Help!

    Thanks

    Nick

  2. #2
    Join Date
    Jul 2003
    Location
    London UK
    Posts
    14
    OK I've got to the point where the instance of Excel is open, but it won't refresh the data.

    Code is more or less this:
    dim objPT as object
    docmd.echo=false
    set objPT=Me!PivotTable
    objPT.Verb=acOLEVerbOpen
    obtPT.Action = acOLEActivate

    objPT.activesheet.Pivottables("Pivot").refreshtabl e
    etc etc etc. (hey know where I am from here!

    But it keeps losing references to the data.

    Any ideas?

  3. #3
    Join Date
    Jul 2003
    Location
    London UK
    Posts
    14
    Help!

    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?

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •