紧急求助：如何用VBA得到一个已经创建好的数据透视表的SourceData？

FENGJUN

紧急求助：如何用VBA得到一个已经创建好的数据透视表的SourceData，即数据源？如何更改一个已经创建好的数据透视表的SourceData的？

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ datasc).CreatePivotTable TableDestination:="", TableName:= _ "数据透视表1", DefaultVersion:=xlPivotTableVersion10

素还真

以下是引用FENGJUN在2004-5-10 17:41:00的发言：

紧急求助：如何用VBA得到一个已经创建好的数据透视表的SourceData，即数据源？如何更改一个已经创建好的数据透视表的SourceData的？

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ datasc).CreatePivotTable TableDestination:="", TableName:= _ "数据透视表1", DefaultVersion:=xlPivotTableVersion10

Set pTable = ThisWorkbook.ActiveSheet.PivotTables(1) pTable.SourceData = "Sheet1!R1C1:R9C2"

FENGJUN

多谢朋友！

再请教以下这段代码为何不能通过？

Sub rfsh() Dim x As string x = ThisWorkbook.Sheets(1).Cells(1, 1).CurrentRegion.Address Sheets(1).PivotTables(1).SourceData = "Sheets(1)!" & x Sheets(1).PivotTables(1).PivotCache.Refresh End Sub

素还真

以下是引用FENGJUN在2004-5-11 13:21:00的发言：

多谢朋友！

再请教以下这段代码为何不能通过？

Sub rfsh() Dim x As string x = ThisWorkbook.Sheets(1).Cells(1, 1).CurrentRegion.Address Sheets(1).PivotTables(1).SourceData = "Sheets(1)!" & x Sheets(1).PivotTables(1).PivotCache.Refresh End Sub

Try It!

Dim x As Range Set x = ThisWorkbook.Sheets(1).Cells(1, 1).CurrentRegion Sheets(1).PivotTables(1).SourceData = x.Address Sheets(1).PivotTables(1).PivotCache.Refresh

FENGJUN

朋友厉害!我绕了弯子终于实现了手动刷新数据透视表:

Sub rfsh() Dim x As String, y As String, z As String

'ThisWorkbook.Sheets(1).Range("A1").Activate

x = ThisWorkbook.Sheets(1).Cells(1, 1).CurrentRegion.Address y = Application.ConvertFormula( _ Formula:=x, _ fromReferenceStyle:=xlA1, _ toReferenceStyle:=xlR1C1)

z = "Sheets(1)!" & y

Set PTB = Sheets(2).PivotTables("数据透视表1") PTB.SourceData = z PTB.PivotCache.Refresh End sub

FENGJUN

列出工作簿中所有的透视表:

Sub lstptb()

On Error Resume Next For j = 1 To ThisWorkbook.Sheets.Count On Error Resume Next For i = 1 To Sheets(j).PivotTables.Count shtnm = Sheets(j).Name ptbnm = Sheets(j).PivotTables(i).Name Sheets("sheet3").Cells(1, 1).Offset(k, 1) = shtnm Sheets("sheet3").Cells(1, 1).Offset(k, 2) = ptbnm k = k + 1 Next Next

End Sub