Filtering a pivot table with VBA

[Saintor]

I need to filter the label field "Family" (see attachment) on many values ("MC00", "MX00" for the sake of discussion). A bit of background. The original data source is a moderately large table with 24000 records SQL table . But the pivot table felt sluggish, and there will be multiple users so I chose to make a local copy at the opening every time (take 3s) and my pivot table is connected to this local copy.

So back to the question I tried two ways.

#1

Please Login or Register  to view this content.

If I understood what I read correctly, I can only filter on one value at a time. I need to do it from 1 to 40 values.

#2

Please Login or Register  to view this content.

As is, it is painfully slow.... minutes... so it doesn't work.

What are my options?

[rorya]

Do you actually need to retrieve the rest of the Family values from the server for other purposes?

[Saintor]

I am working with a global local copy because when connected to SQL, it felt sluggish.

Are you suggesting to have the SQL server do the filtering? One scenario I had in mind was to save the criteria (multiple family codes) to a temporary table through ADO and use a sql view as source to my PT. BTW, this list of family codes is selected from a list box on a form.

It is a bit complicated though and I hope to find a better method to filter the pivot table - with an acceptable speed. But my hope seems to vanish by the hour...

[rorya]

Yes, that's what I was suggesting - either using a temp table, or an IN clause when selecting the data. If you have PowerQuery, it would be simpler, but you could also use a table in the data model with Power Pivot if you have that?

Alternatively, if you are going to do it with code, you should at least set the pivot table's ManualUpdate property to True while looping through the items so that it doesn't try to update with each item's visibility being changed.

[Saintor]

All right, I don't see a better venue.

On a side note, I am trying to change the Data Source of my PT . From the interface, I have no access to the 2nd option "Use an external source" which is just greyed out.


I would prefer to do via VBA anyway.

Please Login or Register  to view this content.

WhReview1 is a copy of my sheet.
WhReview is an existing and tested SQL connection

I get a -2147024809 "Reference isn't valid". What part am I missing?

[rorya]

You don't use xlDatabase with an external connection. Just record a macro while creating a pivot using your SQL connection and you should get the basic syntax.

[Saintor]

I went with suggestion's above and tested a sample made with the help of a macro.

Even if I change the data source, it keeps adding a new iteration of the connection.

Please Login or Register  to view this content.

This will result in adding connection WhReview4 if WhReview3 already exists. Not surprised with the .Add instruction in the macro. Any method to just *change* it or is there another more direct way to redefine it.

[rorya]

The last line is the one you want if the connection already exists:

Please Login or Register  to view this content.