Search code examples
excelvbapivot-tabledatamodel

Excel VBA adding table to Pivot Table Data model

Good day,

I'm trying to insert a pivot table from a table but also add it to a data model via VBA. I've recorded the process manually and also followed the example of this link: https://powerpivotpro.com/2014/07/adding-tables-to-a-model-from-vba-in-excel-2013/

I change the variables for my data path and workbook name that has been set previously in the code. When I run the macro I get the following error on the first line: "Run-Time error 5: Invalid procedure call or argument".

I've never added a pivot table to a data model via VBA before, thus not sure how the code should look (except what I've seen online and mine seem to adhere to the example)

    MainWB.Connections.Add2 "WorksheetConnection_" & MainWB.Name & "!Table_SDCdata", "", _
        "WORKSHEET;" & DataPath & "\" & MainWB.Name & ", " & MainWB.Name & "!Table_SDCdata", 7, True, False

    MainWB.PivotCaches.Create(SourceType:=xlExternal, SourceData:=MainWB.Connections("WorksheetConnection_" & MainWB.Name & "!Table_SDCdata" _
        ), Version:=6).CreatePivotTable TableDestination:=wsPivotModel.Name & "!R1C1", TableName:="PvtSDCmodel", DefaultVersion:=6

The variables are as follows: (** added to replace for security in this question, both variables are correct and used many times in the code without problems)

MainWB.name: **2791 SDC Inland 12.08.2020.xlsbCOMBINED.xls

DataPath: C:_Store\Simone_d_drive\Desktop*\2019\Macros*\raw data**\2791\2020.08.16

Any help or point in the right direction will be great thank you!

Solution

  • I managed to find my errors:

    Updated code:

        MainWB.Connections.Add2 "WorksheetConnection_" & MainWB.Name & "!Table_SDCdata", "", _
        "WORKSHEET;" & DataPath & "\" & MainWB.Name, MainWB.Name & "!Table_SDCdata", 7, True, False

    I had to remove 2 of the quotation marks, one at the end of the connection string and the 2nd one at the beginning of the command text.