Access引用Excel创建数据透视表错误

YANWEI82123300

由于新版Access已经不支持数据透视表
所以想在Access中直接操作Excel生成l数据透视表
(不是将表数据导出到Excel,然后在Excel创建)

过程运行到注释处错误,请大家帮忙指点一下.

• Private Sub CreatePivotTable_Click()
•     Dim XLApp As Excel.Application
•     Dim XLB As Workbook
•     Dim XLS As Worksheet
•     Dim PC As PivotCache
•     Dim PT As PivotTable
•     Dim sSQL As String
•     sSQL = "SELECT STYLE,PO,SIZE,COLOR,QUANTITY FROM ORD"
•     Set XLApp = CreateObject("Excel.Application")
•     Set XLB = XLApp.Workbooks().Add
•     XLB.SaveAs CurrentProject.Path & "\A.xlsx"
• Set PC = ActiveWorkbook.PivotCaches.Add(xlExternal)
•     Set PC.Recordset = CurrentDb.OpenRecordset(sSQL)            '到此处出现错误1004
•     Worksheets.Add
•     ActiveSheet.Name = "PivotSheet"
•     ActiveWindow.DisplayGridlines = False
•     Set PT = ActiveSheet.PivotTables.Add(PC, Range("A1"), "MyPivot")
•     With PT
•         .PivotFields("STYLE").Orientation = xlPageField
•         .PivotFields("PO").Orientation = xlRowField
•         .PivotFields("COLOR").Orientation = xlRowField
•         .PivotFields("SIZE").Orientation = xlColumnField
•         .PivotFields("QUANTITY").Orientation = xlDataField
•         .DataPivotField.Orientation = xlRowField
•     End With
• XLB.Save
•     XLApp.Visible = True
•     Set XLApp = Nothing
•     Set XLB = Nothing
•     Set XLS = Nothing
• End Sub
• 
  

rendiule01

ACCESS 不是有交叉查询，这就是和EXCEL数据透视表