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.
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
