MS Excel Pivot Table RRS feed

  • Question

  • This is my first post, so apologies if I've ot anything wrong.

    I need to display a PivotTable which will have 4 or so Row fields, and 2 or so Column fields per column entry as shown below.

                    |E              |               |               |
                    |«E_value1»     |«E_value2»     |etc            |
    A   B   C   D   |F      |G      |F      |G      |F      |G      |
    a   b   c   d	|«Fvals»|«Gvals»|«Fvals»|«Gvals»|«Fvals»|«Gvals»|
                d	|«Fvals»|«Gvals»|«Fvals»|«Gvals»|«Fvals»|«Gvals»|
            c   d	|«Fvals»|«Gvals»|«Fvals»|«Gvals»|«Fvals»|«Gvals»|
        b   c   d	|«Fvals»|«Gvals»|«Fvals»|«Gvals»|«Fvals»|«Gvals»|
            c   d	|«Fvals»|«Gvals»|«Fvals»|«Gvals»|«Fvals»|«Gvals»|
                d	|«Fvals»|«Gvals»|«Fvals»|«Gvals»|«Fvals»|«Gvals»|
    a   b   c   d	|«Fvals»|«Gvals»|«Fvals»|«Gvals»|«Fvals»|«Gvals»|
                d	|«Fvals»|«Gvals»|«Fvals»|«Gvals»|«Fvals»|«Gvals»|
            c   d	|«Fvals»|«Gvals»|«Fvals»|«Gvals»|«Fvals»|«Gvals»|
        b   c   d	|«Fvals»|«Gvals»|«Fvals»|«Gvals»|«Fvals»|«Gvals»|
            c   d	|«Fvals»|«Gvals»|«Fvals»|«Gvals»|«Fvals»|«Gvals»|
                d	|«Fvals»|«Gvals»|«Fvals»|«Gvals»|«Fvals»|«Gvals»|

    The number of E columns depends on the number of E values.  For any combination of A,B,C,D,E values, there will be at most only one entry for F and G, though it would be beneficial to test this and respond with a suitable error if multiple entries occur.

    However, all the values in the entire table especially «Fvals» & «Gvals» are TEXT (not numbers), and the Excel Pivot Table would seem unsuitable for this.

        As an aside, did Microsoft really just miss the fact that we might want to PivotTable text data?  Such an omission seems hard to believe!  Did they completely miss SQL's TRANSPOSE capabilities, or is that the Excel team has no knowledge of other products capabilities, or perhaps isn't allowed to tread on other product teams toes?

    So my boss said write your own - and walked off.  I guess he just assumes this would be a 5 min task, especially as he still terminates every line in Word with a paragraph mark because he's still not convinced Word will wrap the text, even though in 20+ years he hasn't yet had it fail!

    I've searched the Internet until I'm square eyed looking for examples of how to write a PivotTable, and found nothing.  My background is 30 years of DEC VMS/OpenVMS from the late 1970's (source code readily available from DEC, until Compaq started making a mess of the software and then clammed up), so one could analyse any routine before producing ones own. Moving across to MS Windows has, to say the least, been traumatic because of a lack of quality documentation.  For example, DEC, for each and every routine, listed: (a) every single error that the routine could produce, and (b) every single routine that it called.  This allowed programmers to write robust code.

    So, does anyone have an idiots guide to writing (NOT just using) a PivotTable Wizard in VBA for Excel 2000 onwards?

    I would need it to include:
    - Explanation as to how PivotTable works, from a programmer's point of view:
      - What it produces, and how it can update the PivotTable when data changes
      - How it can update any cell on the sheet, as this isn't normally available to Excel User Defined Functions
      - How Excel builds the calculation dependency data
    - Creating forms
    - Classes and Modules with the code
    - Producing the Wizard/Template etc.
    - Making it available to other Excel users
    - Everything else to make it work.

    That's probably a lot of info, so I'm hoping there's a number of MS websites that just happen to have all this info available, and an example for good measure that one can fully analyse (ie all code visible and editable).


    Friday, November 16, 2012 4:26 AM