Creating an Excel Pivot Table From Access

[hturk]

I am trying to create a pivot table in excel from within access using an access table as the source. When I use the following code I get a blank screen can anyone help me.


Function CreatePivotTable()
Dim xlApp As Excel.Application, xlwkb As Excel.Workbook, xlwks As Excel.Worksheet
Dim PTCache As PivotCache, PT As PivotTable, strxlwkb As String, strxlwks As String
Dim dbs As DAO.Database, rst As DAO.Recordset, vExportPath As String
Dim strTemplate As String, vsql As String

vExportDataToExcel = "D:\Projects\HTM"
vExportPath = vExportDataToExcel & "\HTM_BE.mdb"
strTemplate = vExportDataToExcel & "\PivotTableTemplate.xls"
strxlwkb = vExportDataToExcel & "\PivotTable" + Format(Date, "mmddyy") & ".xls"
FileCopy strTemplate, strxlwkb

Set xlApp = CreateObject("Excel.Application")
Set xlwkb = xlApp.Workbooks.Open(strxlwkb)
On Error Resume Next
xlApp.DisplayAlerts = False
Sheets("PivotSheet").Delete

On Error GoTo 0
Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlExternal)
Set dbs = CurrentDb
vsql = "SELECT * FROM tblHTMData"
With PTCache
.Connection = "ODBC;Database=D:\Projects\HTM\HTM_BE.mdb"
.CommandText = vsql
End With
ActiveSheet.Name = "PivotSheet"
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet").Range("A1"), _
TableName:="PivotTable")

With PT
.PivotFields("CostCenterDesc").Orientation = xlRowField
.PivotFields("PeriodMonth").Orientation = xlColumnField
.PivotFields("Cluster").Orientation = xlPageField
.PivotFields("FTE").Orientation = xlDataField
End With
ActiveWorkbook.Close savechanges:=True
Set xlwkb = Nothing
xlApp.Quit
Set xlApp = Nothing

End Function

Harry

 

[PHV]

Always use FULL qualified syntax:
xlApp.Sheets("PivotSheet").Delete
xlApp.ActiveWorkbook.PivotCaches
....

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244