Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    With help from here, I was able to create pivot tables via VBA in Excel using Access to automate the process. *Until recently, the following code worked fine, and now I get an error on the line :

    "* Set PT = PTCache.CreatePivotTable(WSH.Range("A3"), strPTName, , xlPivotTableVersion10)"*

    [attachment=89100:ErrorMessageCapture.JPG]




    Below is the code prior to the above line.




    **Dim xlWrkbk As Excel.Workbook
    **Dim xlChartObj As Excel.Chart
    **Dim xlSourceRange As Excel.Range
    **Dim xlColPoint As Excel.Point
    **Dim xlApp As Excel.Application
    **Dim strPTName As String
    **
    **
    **Dim blnTimer As Boolean
    **Dim strAction As String
    **'blnTimer = Me.chkTimer
    **
    **strAction = "Start creating the pivots."
    **'===========================================
    **' Added per Pivot Table Book
    **Dim WSD As Worksheet
    **Dim PTCache As PivotCache
    **Dim PT As PivotTable
    **Dim PRange As Range
    **Dim FinalRow As Long
    **Dim FinalCol As Long
    **
    **'===========================================
    **' New From Hans
    **Dim WSH As Excel.Worksheet

    strPTName = "PT4FutureUse"
    **On Error GoTo Err_fCreatePivotChart2

    **' Create a Microsoft Excel object. THIS OPENS AN INSTANCE OF EXCEL
    ****Set xlApp = CreateObject("Excel.Application")

    **' Open the spreadsheet to that has the exported the data.
    ****Set xlWrkbk = xlApp.Workbooks.Open(strFileName)
    ****
    **' Determine the size of the range and store it.
    ****Set xlSourceRange = xlWrkbk.Worksheets(strSheetName).Range("a1").Curre ntRegion

    ****' Create the Pivot table in this section
    ****Set WSD = xlWrkbk.Worksheets(strSheetName)

    ****' Clears any pivot tables in the spreadsheet's memory
    ****For Each PT In WSD.PivotTables
    ********PT.TableRange2.Clear
    ****Next PT

    ****FinalRow = WSD.Cells(WSD.Rows.Count, 1).End(xlUp).Row
    ****FinalCol = WSD.Cells(1, WSD.Columns.Count).End(xlToLeft).Column
    ****Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    ****Set PTCache = xlWrkbk.PivotCaches.Add(xlDatabase, PRange)
    ****
    ' ================================================== =====

    ****Set WSH = xlWrkbk.Worksheets.Add
    ****WSH.Name = strPivotsheetName
    ****Set PT = PTCache.CreatePivotTable(WSH.Range("A3"), strPTName, , xlPivotTableVersion10)


    Any ideas are greatly appreciated!

    Ken
    Attached Images Attached Images

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What are the values of FinalRow and FinalCol when you run the code?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Rory,

    Thanks for asking. *The values for Final Row and FinalCol are 85 and 28 respectively.




    Ken


  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Could you post a sample workbook? I'm confused by your code - does the source data sheet originally have pivot tables on it too?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Actually, this is an Access database that creates the spreadsheet, then via VBA creates the pivot table. *So initially, there is only the tab with the data, and the pivot table gets created to an added sheet/tab.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What's the point of this then?

    Code:
     ' Clears any pivot tables in the spreadsheet's memory
        For Each PT In WSD.PivotTables
    *       * *PT.TableRange2.Clear
    Next PT

    given that WSD seems to be your data sheet?

    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Just seen that you have help elsewhere on this so I will stop now.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Rory,

    Thanks for your help. *I don't know what I would do without help from the experts on the boards.

    I will post when I figure it out the final disposition. *




    Ken





Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •