如何将ADO查询的数据清单使用数据透视表的问题

qy0027

如何将ADO方法从SQL数据库中查询出的数据清单用数据透视表进行汇总。如查询的清单为recordest,不将recordest输入到sheet表中，而直接将recordest作为数据区域，用数据表进行汇总！

xyhecho

回报一下，最近正在学习这方面的东西，贴段代码你看看吧。

Sub gzquery()
Dim objPivotCache As PivotCache
Dim cmd As New ADODB.Command
Dim rsT As New ADODB.Recordset

Application.ScreenUpdating = False
Sheets("date").Select
Cells.Delete xlUp
If UserForm3.TextBox1.Text = "" Then
MsgBox "请输入查询参数!", vbOKOnly + vbExclamation, "提示!"
UserForm3.TextBox1.SetFocus
Exit Sub

End If

With cmd
.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source =" & pthstr
.CommandText = "select * from YJGZ  where date like '" & UserForm3.TextBox1.Text & "%'  "
End With
Set rsT = cmd.Execute

' Create a PivotTable cache and report
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)
Set objPivotCache.Recordset = rsT

Sheets("date").Select
With objPivotCache
.CreatePivotTable TableDestination:=Range("B6"), _
TableName:="yjgzb"
End With
  With ActiveSheet.PivotTables("yjgzb")
        .ColumnGrand = False
        .RowGrand = False
  End With
ActiveWorkbook.ShowPivotTableFieldList = False
' Add fields to the PivotTable
With ActiveSheet.PivotTables("yjgzb")
.SmallGrid = False
With .PivotFields("areaname")
.Orientation = xlRowField
.Position = 1
.Name = "区域"
End With
With .PivotFields("xt")
.Orientation = xlRowField
.Position = 2
.Name = "状态"
End With
With .PivotFields("yjzb")
.Orientation = xlRowField
.Position = 3
.Name = "分类"
End With
With .PivotFields("SKU")
.Orientation = xlColumnField
.Position = 1
.Name = "SKU"
End With
With .PivotFields("SYJSL")
.Orientation = xlDataField
.Position = 1
.NumberFormat = "#,##0.00"
End With

End With

ActiveSheet.UsedRange.Columns.AutoFit

Cells.Select
    With Selection.Font
        .Name = "宋体"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Cells.EntireColumn.AutoFit
    Cells(6, 3).Select
Set cmd = Nothing
Set rsT = Nothing
 

Unload UserForm3
Application.ScreenUpdating = True
End Sub

arson006

我对这个也非常感兴趣  能否传上附件 学习研究呢