Modifying Powerpivot Query through VBA

[joey1744]

Hi,

I was wondering if/how you can update a PowerPivot query. There is the standard way of going to the Manage -> Design -> Table properties and change the query directly there.

I was wondering if you could create the query and run it though <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> code.

Example:
Lets say I have a large data set of locations with their population.
Key Location Population
1 Seattle 560,000
2 Detroit 240,000
3 New York 800,000
ect...

Select *
from TBL_location
where key in(1,3,6,10,11,12,18,22,35)

Say I want to pull only certain rows into my powerpivot. I dont want to go in every time and change the code directly since I have multiple powerpivots that run off the same chose row set. Is there a way I can add the list into and excel sheet and run a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> script to update all my queries and refresh my pivot tables?

Similar to this post but can I do it in <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> code instead? Modifying a Powerpivot Query

I have been looking all over for a solution. If you have a link to where I might be able to find more information that would be greatly appreciated too.

Thank you

 

[anvg]

Try with such a code (for an activesheet)

Public Sub ChangeQuery()
    Dim pSheet As Worksheet
    Dim pPTable As PivotTable
    Dim pPCache As PivotCache
    Dim pOLEConnection As OLEDBConnection
    Dim pODBCConnection As ODBCConnection
    Set pSheet = ActiveSheet
    For Each pPTable In pSheet.PivotTables
        Set pPCache = pPTable.PivotCache
        If pPCache.SourceType = xlExternal Then
            If pPCache.QueryType = xlODBCQuery Then
                Set pODBCConnection = pPCache.WorkbookConnection.ODBCConnection
                MsgBox pODBCConnection.CommandText
            ElseIf pPCache.QueryType = xlOLEDBQuery Then
                Set pOLEConnection = pPCache.WorkbookConnection.OLEDBConnection
                MsgBox pOLEConnection.CommandText
            End If
        End If
    Next
End Sub

Regards,

 

[FireFighter1005]

angv,

I tried your code there and it tiggered an unkown error while executing this line:

Set pOLEConnection = pPCache.WorkbookConnection.OLEDBConnection