技術(shù) 點(diǎn)
- 技術(shù)
- 點(diǎn)
- V幣
- 點(diǎn)
- 積分
- 16023
|
本帖最后由 Henry D. Sy 于 2016-9-12 08:43 編輯
由于新版Access已經(jīng)不支持?jǐn)?shù)據(jù)透視表
所以想在A(yíng)ccess中直接操作Excel生成l數(shù)據(jù)透視表
(不是將表數(shù)據(jù)導(dǎo)出到Excel,然后在Excel創(chuàng)建)
過(guò)程運(yùn)行到注釋處錯(cuò)誤,請(qǐng)大家?guī)兔χ更c(diǎn)一下.
- 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) '到此處出現(xiàn)錯(cuò)誤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
復(fù)制代碼
|
本帖子中包含更多資源
您需要 登錄 才可以下載或查看,沒(méi)有帳號(hào)?注冊(cè)
x
|