Apply a procedure only to specific pivot tables (MDX)

[nomad1980]

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!

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

You haven't shown your entire procedure, but I suspect you have an "On Error Resume Next" statement that is causing the unexpected results.

This example shows one way that can happen. For pivots with non-OLAP sources, the code will error on the expression pt.MDX. However, due to the way the code is written, the code will print the "...has MDX Property" message regardless of whether the pivot has an OLAP source.

Sub Incorrect_ErrorHandling()
 Dim pt As PivotTable

 On Error Resume Next
 For Each pt In ActiveSheet.PivotTables
   If Len(pt.MDX) > 0 Then
      ' if pt.MDX returns an error, this next line will be executed
      ' even though the test expression was not True
      Debug.Print pt.Name & " has MDX Property"
   Else
      Debug.Print pt.Name & " MDX Property returns Error"
   End If
 Next pt
End Sub

Try instead a something like this....

Sub PT_Info()
 Dim pt As PivotTable
 Dim sTest As String

 For Each pt In ActiveSheet.PivotTables
   Err.Clear
   On Error Resume Next
   sTest = pt.MDX
   If (Err.Number = 0) Then
      Debug.Print pt.Name & " has MDX Property"
   Else
      Debug.Print pt.Name & " MDX Property returns Error"
   End If
   On Error GoTo 0
 Next pt
End Sub

 

[nomad1980]

Dear Jerry,
Many thanks for your reply (sorry I did get not back to you earlier but our week end is fri-sat). Will try this and report back

Have a great day!

 

[nomad1980]

Dear Jerry,

it worked magic! Many thanks