[分享]动态更新透视表中的内容

henrry

最近有个朋友问到动态更新透视表中的内容的问题，觉得较有用，拿出来与大家分享，从数据库中根据条件搜索出一定数据然后在EXCEL中生成透视表。 Private Sub aa()
Dim strConn$, strSql$
Dim Conn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim objPivotCache As PivotCache


strConn = "Provider=SQLOLEDB.1;Password=11;Persist Security Info=True;User ID=oper11;Initial Catalog=DBentmgr;Data Source=192.168.1.1"
Conn.Open strConn '搜索出2002年以来入职的人员做分析
strSql = "select deptid,cname from Employee where Employdate>'2002-1-1' "
Rs.Open strSql, Conn, adOpenKeyset

Sheets("sheet1").Select

Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)
Set objPivotCache.Recordset = Rs

ActiveSheet.PivotTables.Add _
PivotCache:=objPivotCache, _
TableDestination:=Range("f3"), _
TableName:="Performance"

With ActiveSheet.PivotTables("Performance")
.SmallGrid = False
With .PivotFields("deptid")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("cname")
.Orientation = xlDataField
.Position = 1
End With

End With


Rs.Close
Conn.Close
Set Rs = Nothing
Set Conn = Nothing
End Sub

huhh_ren

用ACCESS 数据库如何改？ 请教！！！