Apply a procedure only to specific pivot tables (MDX)

Dear all,

I have been trying to create a macro that:
1) opens all my excel files in a folder
2) adds a new connection to each file
3) for each pivot table (of each sheet in each file) that is linked to my data cube, I instruct the macro to replace the existing connection and replace it with the new connection I added (see point 2).
I am only interested in the pivots that are connected to the cube, and therefore if a pivot is connected to an SQL query or if a pivot is sourced from another table then I do not want to touch them.

In order to identify the pivots that are sourced from the cube, I used one of their properties: MDX, so I instructed

If Left(CrntPvtTbl.MDX, 6) = "SELECT" Then
CrntPvtTbl.ChangeConnection crntfl.Connections("MynewConnection")

I though by doing this I would exclude all other pivots (which is what I want).
However, it is not working. I get an error every time the code reaches a table that is sourced from another table (a range).

So my question to you guys is:
is there a better way of identifying those pivots that are linked to a cube and exclude the other ones?

What I have tried to do is to set an object as MDX and when that object isnothing it means that it is not sourced from cube so ignore it. This did not work.
I have also tried to replace If Left(CrntPvtTbl.MDX, 6) = "SELECT" Then

with

If Left(CrntCnctn.OLEDBConnection.Connection, Len("OLEDB;Provider=MSOLAP")) = "OLEDB;Provider=MSOLAP" Then

but it did not work. An error I often get is 1004

Thank you in advance!

Hello all -
just to let you know this question has been answered here: http://www.mrexcel.com/forum/excel-q...ml#post3690974


all the best