|
本帖最后由 Henry D. Sy 于 2016-9-12 08:43 编辑
由于新版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
复制代码
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?注册
x
|