■PivotTables().SourceDataプロパティと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

普通にセル範囲を指定してピボットテーブルを作成したら
SourceDataはR1C1形式のセル参照文字列になる。
Application.ConvertFormulaメソッドを使ってRangeに受けたら後は煮るなり焼くなり...

#余談
#Rangeプロパティの引数はA1形式なのでConvertFormulaを使う。
#例えばApplication.ReferenceStyleがxlR1C1だったらR1C1形式で良いかというとそんな事はない。
#ぃや、そこは別に実験するとこじゃないだろ＞end-u



さて。
ピボットテーブル作成時にListObjectを指定したり、定義した名前で指定したりすると、
SourceDataはセルアドレスではなく名前文字列になる。

#以下記事はVer2007以降が対象。

Sub test()
　　'新規Book追加、A1:A2にデータセット、ListObjectセット、PivotTable２個追加してテスト。
　　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範囲外ではエラーになる。
通常の名前定義のケースとの識別も必要だから、分岐処理が妥当なセンだろうか。