|
请教如果要把货号、性别、帮面ID三个字段再添到EXCEL里,下面这段代码要怎么修改?Private Sub Command19_Click()
Dim Db As Database
Dim Qdf As QueryDefSet Db = CurrentDb
'On Error Resume NextWith Db
Set Qdf = .CreateQueryDef("qtemp", "SELECT * from 领取分配汇总查询 where " & strFilter)
Kill "c:\xx.xls"
DoCmd.TransferSpreadsheet acExport, 8, "qtemp", "c:\xx.xls", False, ""
.QueryDefs.Delete Qdf.Name
End With
'--------------------------------------------
Dim xls As Excel.Application
Dim wrb As Excel.Workbook
Dim sht As Excel.Worksheet
Dim sht1 As Excel.Worksheet
Set xls = CreateObject("excel.application")
xls.Visible = False
Set wrb = xls.Workbooks.Open("c:\xx.xls")
Set sht = wrb.Worksheets(1)
Set sht1 = wrb.Worksheets.Add
'---------------------------------------------------------
wrb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
sht.Columns("A:I")).CreatePivotTable TableDestination:=sht1.Range("A3"), TableName:="数据透视表2", _
DefaultVersion:=xlReport6
With sht1.PivotTables("数据透视表2").PivotFields("工人姓名")
.Orientation = xlRowField
.Position = 1
End With
sht1.PivotTables("数据透视表2").AddDataField sht1.PivotTables("数据透视表2" _
).PivotFields("数量之Sum"), "计数项:数量之Sum", xlCount
sht1.PivotTables("数据透视表2").AddDataField sht1.PivotTables("数据透视表2" _
).PivotFields("小计之Sum"), "计数项:小计之Sum", xlCount
sht1.PivotTables("数据透视表2").PivotSelect "", xlDataAndLabel, True
sht1.PivotTables("数据透视表2").PivotFields("计数项:数量之Sum").Function = xlSum
sht1.PivotTables("数据透视表2").PivotFields("计数项:小计之Sum").Function = xlSum
'---------------------------------------------------------
xls.DisplayAlerts = False
wrb.Save
xls.DisplayAlerts = True
xls.Quit
Set xls = Nothing
Call Shell("excel.exe c:\xx.xls", vbNormalFocus)
'----------------------------------------------
End Sub |
|