Results 1 to 5 of 5
  1. #1
    goldenthroat is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Mar 2016
    Posts
    3

    Can't create Excel Pivot Table from PivotCache.CreatePivotTable.


    My first post.

    Here is the code that is not working. I figured out how to create the PivotCache (and that took some doing), but I can't get the pivot table created. The problem seems to be in the one required argument TableDestination. In the Microsoft topics I've seen "A3" and Range("A3"), and even a mention of a Range object. I've tried all 3 and keeping getting errors. Here is the code. The underlined line is the line that generates the errors. I've stepped through this I don't know how many times.

    Code:
    Dim ptPivotTable as PivotTable
    Dim wksPT as Worksheet
    
    ...lots of stuff...
    
    Set ptPivotTable = pcPivotCache.CreatePivotTable(TableDestination:=wksPT.Range("A3"))
    I've also tried:

    Code:
    Set ptPivotTable = pcPivotCache.CreatePivotTable(TableDestination:="A3")
    Please let me know if you need any more information. I really appreciate your help!

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    the lots of stuff part may help. im not sure what pcPivotCache is. This is for excel? you are on the access forums.

  3. #3
    goldenthroat is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Mar 2016
    Posts
    3

    Yes, I am running an Excel application from within Access VBA

    Thanks for your interest!
    ptPivotTable is declared like this:
    Dim ptPivotTable As PivotTable ' To create pivottable
    and it is being created by the .CreatePivotTable of a pivot cache
    Dim pcPivotCache As PivotCache ' To create a pivot table from its pivotcache

    Here's the code:
    strPTRangeName is the nameof the range being passed to PivotCaches.Create. rngTableRange is the range with the RefersTo for the Pivot Table.

    Code:
            strPTRangeName = DLookup("[PTRangeName]", strNames, _
            "[QueryID] =" & strQueryID)
            wkb.Names.Add Name:=strPTRangeName, RefersTo:=rngTableRange.Address
            Set pcPivotCache = wkb.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:=strPTRangeName, Version:=xlPivotTableVersion15)                                                       ' Passing a named range, per MS online doc'n
    I've identified a problem. The pivot cache gets created, but Excel doesn't seem to know about it. It's not showing up in the PivotCaches collection. Some of its properties are valid, like .Version, and .SourceData. But its .Index is zero, and like I said it's not showing up in .PivotCaches. So that may be why it's not creating a PivotTable successfully. Any thoughts? I googled this a few different ways and looked at the documentation and I seem to be doing it correctly.

    Quote Originally Posted by Perceptus View Post
    the lots of stuff part may help. im not sure what pcPivotCache is. This is for excel? you are on the access forums.

  4. #4
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Interesting I just grabbed this vba using the macro recorder in excel, and your code looks like it should be working.

    Excel's standard VBA
    Code:
    Tub Macro3()
    '
    ' Macro3 Macro
    ''
        Sheets.Add
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Untitled Analysis!R1C1:R1048576C4", Version:=xlPivotTableVersion14). _
            CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
            , DefaultVersion:=xlPivotTableVersion14
        Sheets("Sheet1").Select
        Cells(3, 1).Select
    End Sub
    Your VBA
    Code:
       strPTRangeName = DLookup("[PTRangeName]", strNames, _
            "[QueryID] =" & strQueryID)
            wkb.Names.Add Name:=strPTRangeName, RefersTo:=rngTableRange.Address
            Set pcPivotCache = wkb.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:=strPTRangeName, Version:=xlPivotTableVersion15)

    Excel's breakdown.
    1.ActiveWorkbook.PivotCaches.Create(SourceType:=xl Database, SourceData:= "Untitled Analysis!R1C1:R1048576C4"
    2.Version:=xlPivotTableVersion14).CreatePivotTable
    3.TableDestination:="Sheet1!R3C1"
    4.TableName:="PivotTable1"
    5.DefaultVersion:=xlPivotTableVersion14


    I wonder if the order of excels events have to be followed? Create-DefineSource,Create-settableversion?,Destination, name, defaultversion again???


    Sorry, I wasn't able to provide any more assistance, I thought after doing a breakdown something would come to light.


    Edit*

    What error do you get when you try to run the code? Any codes?
    Last edited by redbull; 04-04-2016 at 01:54 PM. Reason: Additional thought

  5. #5
    goldenthroat is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Mar 2016
    Posts
    3

    Changed approach: using the macro code now that creates Pivot Cache and Pivot Table in one line

    The error I get is error 5 "Invalid procedure call or argument". I have given up on creating the pivotcache first and now use the code the macro gives, where you make both in one line. But I still get the same error. Here is the crucial part of the code. I include the full function at the bottom for reference, but thought that might be a bit of a job to wade through:

    Code:
        wkb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _   
     strPTRangeName, Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:=strTableDestination, TableName:=strPTName
    I'm beginning to think there may be something wrong with the computer since this code looks exactly like what the Excel macro puts out and like what I see on forums on the net.

    Versions:
    Windows 7 Professional Service Pack 1
    Microsoft Office Professional Plus 2013


    Code:
    ' Creates a new pivot table on a new sheet
    
    Private Function funCreatePivotTable1(wkb As Workbook, _
    rngTableRange As Range, strTableTabName As String, _
    strQueryID As String) As Boolean
    On Error GoTo ErrHandler
    
    
        Dim wksPT As Worksheet                                    ' New sheet for pivot table
        Dim strPTSheet As String                                ' Name of pivot table sheet
        Dim strPTName As String                                    ' Name for pivot table
        Dim strPTRangeName As String                            ' Name of named range to add to Names collection
        Dim strPTRangeRefersTo                                    ' RefersTo for new named range
        Dim strTableDestination As String                        ' RefersTo for pivot table's upper left corner
                                                                ' Includes name of tab
    
    
        ' Create named range in workbook
        
        strPTRangeName = DLookup("[PTRangeName]", strNames, _
        "[QueryID] = " & strQueryID)                                                   ' Get name of range from Names query
        strPTRangeRefersTo = "=" & funCreateRangeAddress _
        (strTableTabName, rngTableRange.Address)                               ' Create the refers to for the named range
        wkb.Names.Add Name:=strPTRangeName, _
        RefersTo:=strPTRangeRefersTo                                                ' Add the name to the Names collection
    
    
        ' Create destination sheet for pivot table
        
        strPTSheet = DLookup("[PTTabName]", strNames, _
        "[QueryID] =" & strQueryID)                                                   ' Get sheet's name from Names query
        Set wksPT = wkb.Worksheets.Add                                            ' Create the sheet
        wksPT.Name = strPTSheet                                                      ' Name the sheet
        strTableDestination = "'" & strPTSheet & "'!A3"                           ' Create the range for the destination
        
        ' Get the name for the pivot table from Names query
        
        strPTName = DLookup("[PTName]", strNames, _
        "[QueryID] =" & strQueryID)
        
        ' Create the pivot table. Standard code from Macro in
        ' Excel workbook, with variables added
        ' This one uses the name of a named range for the
        ' SourceData argument. I have also tried using the
        ' RefersTo itself. That didn't work either.
        
        wkb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        strPTRangeName, Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:=strTableDestination, TableName:=strPTName
    
    
    GoTo EndFunction
    ErrHandler:
        LogError Err.Number, Err.Description, Err.Source, "funCreatePivotTable1 in MenuFunctions Module)"
    EndFunction:
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-06-2015, 11:52 AM
  2. Creating a pivot table in Excel
    By crowegreg in forum Programming
    Replies: 7
    Last Post: 01-20-2014, 08:32 PM
  3. Pivot table - access to excel
    By antagonia in forum Access
    Replies: 3
    Last Post: 12-16-2011, 01:57 PM
  4. Replies: 19
    Last Post: 10-20-2010, 01:27 AM
  5. Pivot Table from Excel Spreadsheet
    By Cgil32 in forum Access
    Replies: 0
    Last Post: 08-26-2010, 05:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums