Background:
Based on a PowerPivot import I retrieve multiple sources that have the same information to manipulate within excel and use Pivot tables to display basic calculations, in this process I automate the PT creation as well and use relationships to relate fields and then display values from each of the tables. Let us say I have two queries 1. "Warehouse_North_Data" 2. "Warehouse_South_Data": they have as common a column called "Product" and we would like to see the "Sales" of the summarized operations related to the product itself (hence the relationship created among tables)
Constraints: I cannot combine the two into one file (it will be useless to add a column that said where the data came from) The example provided is just to summarize the process/problem, there are actually nth queries imported, overcoming easily 10m rows of data, but power pivot is able to handle that.

Problem:
I have not found a way on how to retieve correctly the order to get "Sales" of "Warehouse_North_Data" or "Warehouse_South_Data" or for the matter "Warehouse_Location_Data" so VBA is able to create automatically the Pivot Table desired by the user.
Example of desired outcome

Code:
By using macro recorder I got the following

ActiveSheet.PivotTables("PT_Test").CubeFields.GetMeasure _
    "[Warehouse_South_Data].[Total Sales]", xlSum, _
    "Sum of Total Sales"
ActiveSheet.PivotTables("PT_Test").AddDataField ActiveSheet.PivotTables( _
    "PT_Test").CubeFields("[Measures].[Sum of Total Sales 2]"), _
    "Sum of Total Sales"

So I assumed the position of sales (as in the example "2") would be based on the position that the query is in (since it orders alphabetically), but it was not the case since I tried with a third location, let us say "Warehouse_West_Data" as being 3 but this gave an error since the element 2 was not loaded, having a not desired effect sometimes of allocation West as position 2 instead of 3. It is important to note that all queries were created at the same time.
Further thoughts
I tried to use xlDataField mixing with the code above, but it is no use.

Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("PT_Test")

With pt
 With .PivotFields("[Warehouse_South_Data].[Total Sales]")
       .Orientation = xlDataField
       .Function = xlSum
       .Name = "Sum of Sales in South"
   End With
   End With

Question:
Is there a way to build a Cubefields/DataField as "values" from queries refering to its name directly instead of having to reference it as number (as shown in first section in the block "Code")?

CodePudding user response：

Solution
I tried another approach that has resulted way better: I made a function to retrieve the CubeField to use

Function Return_CubeFieldForTitle(PTToWorkIn As PivotTable, TxtQueryFrom As String, TxtFieldToLoad As String) As CubeField
    Set Return_CubeFieldForTitle = PTToWorkIn.CubeFields.GetMeasure("[" & TxtQueryFrom & "].[" & TxtFieldToLoad & "]", xlSum, "Sum of " & TxtFieldToLoad)
End Function

The main code will be able to call this function and correctly append the query column without the need of having numbers:

Sub Test()
Dim CubeFieldForTitle As CubeField
Dim PTToWorkIn as PivotTable
Dim TxtWareHouseAnalyzed as String:TxtWareHouseAnalyzed = "Warehouse_South_Data"
Set PTToWorkIn = Sheets("PT_Test").PivotTables("PT_Test")
With PTToWorkIn 
Set CubeFieldForTitle = Return_CubeFieldForTitle(PTToWorkIn, TxtWareHouseAnalyzed,"Sales")
.AddDataField CubeFieldForTitle, "Sum of Sales at " & TxtWareHouseAnalyzed 
End with
End Sub