いつまでもカンニングしたやつがトップページなのもなんなので
久しぶりに回答したトピックから...
『ピボットテーブルの参照元のワークシート名を取得するには?』
http://park7.wakwak.com/~efc21/cgi-bin/exqalounge.cgi?print+201211/12110039.txt
#ぃや大した話ではないのだが覚え書き的に。
#ちなみにポイントはそこではないよ。Range(名前定義)なだけ。
SourceTypeがxlDatabase、つまりExcelリストまたはデータベースから作ったピボットテーブルの場合。
そのピボットテーブルの参照元を取得するにはどうしたらよいか。
ピボットテーブルの参照元はSourceDataプロパティから取れる。
このSourceDataプロパティはセル参照の文字列である。
順当に考えればこれを利用するだけで良いはず。
普通にセル範囲を指定してピボットテーブルを作成したら
SourceDataはR1C1形式のセル参照文字列になる。
Application.ConvertFormulaメソッドを使ってRangeに受けたら後は煮るなり焼くなり...
#余談
#Rangeプロパティの引数はA1形式なのでConvertFormulaを使う。
#例えばApplication.ReferenceStyleがxlR1C1だったらR1C1形式で良いかというとそんな事はない。
#ぃや、そこは別に実験するとこじゃないだろ>end-u
さて。
ピボットテーブル作成時にListObjectを指定したり、定義した名前で指定したりすると、
SourceDataはセルアドレスではなく名前文字列になる。
#以下記事はVer2007以降が対象。
結果はこんな感じ。
pivot2 Sheet1!R1C1:R2C1 $A$1:$A$2
pivot1 table1 $A$2
Indexが逆転(?)なのは置いといて。
SourceDataが名前文字列になっているのが確認できる。
厄介なのは Range("table1").Address が $A$2 となっているように、「見出し」を含んでくれていない事。
この"table1"はListObjectそのものではなく名前として定義されたもの。
冒頭リンクスレッドのようにWorksheet.Nameを取得するだけなら簡単だが
参照元範囲をRangeに取得したいとなると、
元データがListObjectの場合はもう少し工夫が必要になりそうだ。
Range(p.SourceData).ListObject.Range
で全体が取得できるのだが、ListObject範囲外ではエラーになる。
通常の名前定義のケースとの識別も必要だから、分岐処理が妥当なセンだろうか。
久しぶりに回答したトピックから...
『ピボットテーブルの参照元のワークシート名を取得するには?』
http://park7.wakwak.com/~efc21/cgi-bin/exqalounge.cgi?print+201211/12110039.txt
#ぃや大した話ではないのだが覚え書き的に。
#ちなみにポイントはそこではないよ。Range(名前定義)なだけ。
SourceTypeがxlDatabase、つまりExcelリストまたはデータベースから作ったピボットテーブルの場合。
そのピボットテーブルの参照元を取得するにはどうしたらよいか。
ピボットテーブルの参照元はSourceDataプロパティから取れる。
このSourceDataプロパティはセル参照の文字列である。
順当に考えればこれを利用するだけで良いはず。
Sub try()
Dim p As PivotTable
Dim s As String
Dim r As Range
For Each p In ActiveSheet.PivotTables
s = p.SourceData
s = Application.ConvertFormula(s, xlR1C1, xlA1)
Set r = Range(s)
Debug.Print r.Address
Next
End Sub
Dim p As PivotTable
Dim s As String
Dim r As Range
For Each p In ActiveSheet.PivotTables
s = p.SourceData
s = Application.ConvertFormula(s, xlR1C1, xlA1)
Set r = Range(s)
Debug.Print r.Address
Next
End Sub
普通にセル範囲を指定してピボットテーブルを作成したら
SourceDataはR1C1形式のセル参照文字列になる。
Application.ConvertFormulaメソッドを使ってRangeに受けたら後は煮るなり焼くなり...
#余談
#Rangeプロパティの引数はA1形式なのでConvertFormulaを使う。
#例えばApplication.ReferenceStyleがxlR1C1だったらR1C1形式で良いかというとそんな事はない。
#ぃや、そこは別に実験するとこじゃないだろ>end-u
さて。
ピボットテーブル作成時にListObjectを指定したり、定義した名前で指定したりすると、
SourceDataはセルアドレスではなく名前文字列になる。
#以下記事はVer2007以降が対象。
Sub test()
'新規Book追加、A1:A2にデータセット、ListObjectセット、PivotTable2個追加してテスト。
Dim ws As Worksheet
Dim r As Range
Dim p As PivotTable
With Workbooks.Add(xlWBATWorksheet)
Set ws = .Sheets(1)
Set r = ws.Range("A1:A2")
r.Value = [{"a1";"a2"}]
ws.ListObjects.Add(xlSrcRange, r, , xlYes).Name = "table1"
.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="table1" _
).CreatePivotTable ws.Range("D1"), "pivot1"
.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=r _
).CreatePivotTable ws.Range("H1"), "pivot2"
For Each p In ws.PivotTables
Debug.Print p.Name, p.SourceData, _
Range(Application.ConvertFormula(p.SourceData, xlR1C1, xlA1)).Address
Next
End With
End Sub
'新規Book追加、A1:A2にデータセット、ListObjectセット、PivotTable2個追加してテスト。
Dim ws As Worksheet
Dim r As Range
Dim p As PivotTable
With Workbooks.Add(xlWBATWorksheet)
Set ws = .Sheets(1)
Set r = ws.Range("A1:A2")
r.Value = [{"a1";"a2"}]
ws.ListObjects.Add(xlSrcRange, r, , xlYes).Name = "table1"
.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="table1" _
).CreatePivotTable ws.Range("D1"), "pivot1"
.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=r _
).CreatePivotTable ws.Range("H1"), "pivot2"
For Each p In ws.PivotTables
Debug.Print p.Name, p.SourceData, _
Range(Application.ConvertFormula(p.SourceData, xlR1C1, xlA1)).Address
Next
End With
End Sub
結果はこんな感じ。
pivot2 Sheet1!R1C1:R2C1 $A$1:$A$2
pivot1 table1 $A$2
Indexが逆転(?)なのは置いといて。
SourceDataが名前文字列になっているのが確認できる。
厄介なのは Range("table1").Address が $A$2 となっているように、「見出し」を含んでくれていない事。
この"table1"はListObjectそのものではなく名前として定義されたもの。
冒頭リンクスレッドのようにWorksheet.Nameを取得するだけなら簡単だが
参照元範囲をRangeに取得したいとなると、
元データがListObjectの場合はもう少し工夫が必要になりそうだ。
Range(p.SourceData).ListObject.Range
で全体が取得できるのだが、ListObject範囲外ではエラーになる。
通常の名前定義のケースとの識別も必要だから、分岐処理が妥当なセンだろうか。