none
Stuck building a pivot table via VBA-edit 2 RRS feed

  • Question

  • I have a blank Excel 2010 file that includes a number of empty/pre-labeled tabs and a macro that is builds a series of pivot tables/charts from data that is programmatically added to the blank file via a scheduled app.

    The scheduled code opens the blank Excel file, stuffs data into a 'raw data' tab, saves and mails the file to the person who needs it. Once received, the person runs a single macro that converts the tab containing data into a range and then builds the pivot tables.

    The code was built using the 'record macro' function. I modified it to figure out the size of the data for the current file (it varies each time the extraction routine runs).

    The convert to table works fine; I can confirm that the range is correct.

    When the code tries to build the pivot table, it errors out with ' Error 5, invalid procedure call or argument'. The tableDestination is the name of a pre-existing worksheet tab and I've confirmed there is not a spelling error.

    I've tried manually setting different values into sourceData and tableDestination without any luck.

    I've searched the web, used built in help, and I am out of brain cells.

    What am I missing?

    Thanks for any advice.

    <edit> So I know the problem is with the TableDestination parameter. If I convert it to "", then the code works.

    BUT, the problem is that the new pivot table/chart are created on a new sheet, not on the pre-defined sheet I want them on.

    Is there a way to force a pivot to be added to an existing, blank, named sheet?

        'Convert Raw Data into a Table so we don't need to play with ranges - *************************************
        Sheets("Raw RMA-Compl Data").Select
        lastRow = Sheets("Raw RMA-Compl Data").UsedRange.Rows.Count
        srcString = "$A$1:$L$" & lastRow
        ActiveSheet.ListObjects.Add(xlSrcRange, Range(srcString), , xlYes).Name = "Table1"
        Range("Table1[#All]").Select
        ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium2"
        'Range("A2").Select
        '==========================================================================================================
    'Build pivot table/chart for complaints by program - **********************************************************

    'This fails with the error: Error 5, invalid procedure call or argument

        ActiveWorkbook.PivotCaches.Create( _
           SourceType:=xlDatabase, _
           SourceData:="Table1", _
           Version:=xlPivotTableVersion14).CreatePivotTable _
           TableDestination:="pvt-CompByProg!R1C1", _
           TableName:="PivotTable1", _
           DefaultVersion:=xlPivotTableVersion14

    Edit #2: Before Woulter replied I went at the problem a different way.

    In the Excel template file I removed all of the blank pre-named tabs.

    In the macro, I add a sheet then immediately name it. This is working well and meets my requirements so I'll leave the code as is. I'll revisit the design and try Woulter's method after I have a working app.

        ActiveWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=srcStringRC, _
            Version:=xlPivotTableVersion14).CreatePivotTable _
            TableDestination:="", _
            TableName:="PivotTable1", _
            DefaultVersion:=xlPivotTableVersion14

        ActiveSheet.Name = "pvt-CompByProg"

    • Moved by George123345 Thursday, September 11, 2014 2:33 AM
    • Edited by Dogubob Thursday, September 11, 2014 1:28 PM Found working solution.
    Wednesday, September 10, 2014 5:28 PM

Answers

  • Hello,

    I have similar code to build a pivot from scratch. For me this works.

    The only difference I see, is the was to adress the location. instead of "pvt-CompByProg!R1C1"

    try:

    dim ws As Worksheet

    Set ws = ActiveWorkbook.Worksheets("pvt-CompByProg")

    TableDestination:=ws.Cells(1, 1)

    My code:

     With ActiveWorkbook.PivotCaches.Add(xlExternal)
            .Connection = "ODBC;DSN=xxx;UID=xxx;PWD=xxx;"
            .CommandType = xlCmdSql
            .CommandText = "SELECT *  FROM xxx" 
            Call .CreatePivotTable(ws.Cells(1, 1), "ptNew")
        End With 'hope this helps

    Thursday, September 11, 2014 8:56 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Thursday, September 11, 2014 2:33 AM
  • Hello,

    I have similar code to build a pivot from scratch. For me this works.

    The only difference I see, is the was to adress the location. instead of "pvt-CompByProg!R1C1"

    try:

    dim ws As Worksheet

    Set ws = ActiveWorkbook.Worksheets("pvt-CompByProg")

    TableDestination:=ws.Cells(1, 1)

    My code:

     With ActiveWorkbook.PivotCaches.Add(xlExternal)
            .Connection = "ODBC;DSN=xxx;UID=xxx;PWD=xxx;"
            .CommandType = xlCmdSql
            .CommandText = "SELECT *  FROM xxx" 
            Call .CreatePivotTable(ws.Cells(1, 1), "ptNew")
        End With 'hope this helps

    Thursday, September 11, 2014 8:56 AM
  • Woulter, thank youi, I'll give it a try.
    • Edited by Dogubob Thursday, September 11, 2014 10:38 AM
    Thursday, September 11, 2014 10:38 AM