VBA Pivot Table challenge

kwvh
3StarLounger
Posts: 308
Joined: 24 Feb 2010, 13:41

VBA Pivot Table challenge

Post by kwvh »

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)"


Below is the code prior to the above line.



Code: Select all

  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").CurrentRegion

    ' 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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Pivot Table challenge

Post by HansV »

The most probable explanation is that the exported data have no records. Inspect the value of FinalRow when the error occurs.
Best wishes,
Hans

kwvh
3StarLounger
Posts: 308
Joined: 24 Feb 2010, 13:41

Re: VBA Pivot Table challenge

Post by kwvh »

FinalRow = 5706 and FinalCol = 28
But now, today for some reason I am getting "13 Type Mismatch" on the following line:
Set PTCache = xlWrkbk.PivotCaches.Add(xlDatabase, PRange)
Only change is I am running in Windows Virtual PC XP mode.

BTW, congratulations on making it to the quarter finals. I am rooting for you.

Ken

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Pivot Table challenge

Post by HansV »

Which version of Office are you using?
Best wishes,
Hans

kwvh
3StarLounger
Posts: 308
Joined: 24 Feb 2010, 13:41

Re: VBA Pivot Table challenge

Post by kwvh »

Hans,
thanks for getting back so promptly.

Office 2003 SP3

Ken

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Pivot Table challenge

Post by HansV »

OK. I was asking because Excel 2007 uses PivotCaches.Create instead of PivotCaches.Add.

Could you post a (preferably small) workbook on which the code fails? Make sure to remove sensitive information.
Best wishes,
Hans

kwvh
3StarLounger
Posts: 308
Joined: 24 Feb 2010, 13:41

Re: VBA Pivot Table challenge

Post by kwvh »

Hans,
I can post the worksheet. But I need to mention that I am creating the spreadsheet from Access, then via VBA creating the spreadsheet. So is the spreadsheet what you need, or would I need to try to strip down the database?

Thanks,
Ken

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Pivot Table challenge

Post by HansV »

I'd like to see a sample of an exported workbook, since the problem occurs there. I could then try to run your code.
Best wishes,
Hans

kwvh
3StarLounger
Posts: 308
Joined: 24 Feb 2010, 13:41

Re: VBA Pivot Table challenge

Post by kwvh »

Hans,
I cleared out some of the columns, and changed data in others, but I don't think it will impact the pivot table.

thanks for your help.

Ken
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Pivot Table challenge

Post by HansV »

I copied the code that you posted into a procedure in a modile in Access, and added the bits needed to make it run, such as specifying the workbook and sheet names. It ran without error on the workbook that you attached - neither of the lines that you mentioned caused a problem.

So there must be something else - perhaps in code that you didn't post, or perhaps the workbook isn't representative.
Best wishes,
Hans

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: VBA Pivot Table challenge

Post by rory »

Also posted here, BTW.
Regards,
Rory