Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10全般 : Excel 2016)
ピボットグラフの作成方法のプロパティ内容の理解ができない
投稿日時: 20/11/20 10:43:49
投稿者: mndkyui

 
いつもお世話になっております。
 
データ表からピボットグラフを自動で作成したいのですが、実装時に動的にしたいところの判断と
プロパティの内容が理解できず詰まっております。
 
以下、現状のソースです。
「テーブル11」ピボットテーブルデータからピボットグラフを作成し、X軸に商品名、
Y軸に商品個数を設定した棒グラフを作ろうとしています。
 
 
    Workbooks("データ1.xlsm.xlsm").Connections.Add2 _
        "WorksheetConnection_データ1.xlsm!テーブル11", "", _
        "WORKSHEET;データ1.xlsm", _
        "データ1.xlsm!テーブル11", 7, True, False
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections( _
        "WorksheetConnection_データ1.xlsm!テーブル11"), Version:=6). _
        CreatePivotChart(ChartDestination:="Sheet8").Select
--------------------------------------------------------------------------------------------(*)
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.ChartStyle = 201
    With ActiveChart.PivotLayout.PivotTable.CubeFields("[テーブル11].[小分類]")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveChart.PivotLayout.PivotTable.CubeFields("[テーブル11].[商品名]")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveChart.PivotLayout.PivotTable.CubeFields.GetMeasure "[テーブル11].[数量]", xlSum _
        , "合計 / 数量"
    ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
        PivotTable.CubeFields("[Measures].[合計 / 数量]"), "合計 / 数量"
 
 
【やりたいこと】
・ピボットテーブルからピボットグラフを作成し、これをシートを変えて複数回繰り返したい
 
【わからないこと】
・(*)から上に手を入れたいが、Sheets.Add以外の指示内容の把握ができない。
 ActiveWorkbook.PivotCaches.Createプロパティ内の引数が理解できない
 
 
以上です。どうぞよろしくお願いいたします。

回答
投稿日時: 20/11/20 17:25:51
投稿者: Suzu

基本、判らない部分は VBE上の判らない プロパティーやメソッド に カーソルをもっていき
「F1」キー を押せば、ヘルプが出てきます。
 
Connections であれば
https://docs.microsoft.com/ja-jp/office/vba/api/excel.connections?f1url=%3FappId%3DDev11IDEF1%26l%3Dja-JP%26k%3Dk(vbaxl10.chm775072);k(TargetFrameworkMoniker-Office.Version%3Dv15)%26rd%3Dtrue
 
ここに、addメソッドがありますので、引数はそこで確認できます。
でも、なぜ Add2 なのかは。。判りません。
コンパイルエラーは出ていないのでメソッド自体はあると思うのですが。。
そんなときには、シングルステップで動作確認。
エラーになったら、WEBで調べます。
 
 
PivotCaches.Create も同様
 
https://docs.microsoft.com/ja-jp/office/vba/api/excel.workbook.pivotcaches?f1url=%3FappId%3DDev11IDEF1%26l%3Dja-JP%26k%3Dk(vbaxl10.chm199124);k(TargetFrameworkMoniker-Office.Version%3Dv15)%26rd%3Dtrue
 
理解は当方も出来ていない所は多いと思いますが、
少なくとも、
 
WorksheetConnection_データ1.xlsm!テーブル11 は、先に作成したConnection の名称 と推測できます。
 
また、
ActiveWorkbook.PivotCaches.Create _
   ( _
    SourceType:=xlExternal, _
    SourceData:= ActiveWorkbook.Connections("WorksheetConnection_データ1.xlsm!テーブル11"), _
    Version:=6 _
   ) _
  .CreatePivotChart(ChartDestination:="Sheet8").Select
こうして、() を基点に、インデント位置を整えると、
 
ピボットキャッシュを作成する 為に、データソースとしてConnctionを指定し、
その作成したピボットキャッシュを元に、Sheet8に対して ピボットグラフを作成し 選択している
と判りやすくなります。
 
 
後は、色々触ってみる事です。
ファイル自体バックアップを取り、
 テーブル名、テーブルの要素名、シート名 っぽい所を変えて、
合わせて、実オブジェクト名を変えてみれば良いのではないですかね。

回答
投稿日時: 20/11/21 19:14:04
投稿者: mattuwan44

引用:
Function Add2(Name As String, Description As String, ConnectionString, CommandText, [lCmdtype], [CreateModelConnection], [ImportRelationships]) As WorkbookConnection
    Excel.Connections のメンバー

 
オブジェクトブラウザで検索してみるのもありかと、、、、
ダブルクォーテーションで括られているものは文字列だから、
名前的なものを入れればいいことが想像できます。
オブジェクトブラウザからもF1でヘルプを参照できます。
ただし、ヘルプには
Addメソッドしかなく、
オブジェクトブラウザではAdd2メソッドしか表示されませんが(こちらの環境のみ?)引数が同じなので、
同じなのかなぁ。。。。
(Addメソッドは非表示のメンバーになっているので、どこかで仕様が変わったのかも知れませんね>Add2)
マクロの記録をされた結果なら、大体想像がつきそうなものですが。。。
 
 
ActiveWorkbook.Connections("WorksheetConnection_データ1.xlsm!テーブル11")
これはオブジェクトを指定してますので、
 
Sub test2()
    Dim myTable As ListObject
    Dim myConection As Collection
    Dim pvtCache As PivotCache
    Dim pvtChart As Chart
    
    Set myTable = Workbooks("データ1.xlsm.xlsm").Worksheets("データ1").ListObjects("テーブル11")
    Set myConections = Workbooks("データ1.xlsm.xlsm").Connections.Add2( _
                        "WorksheetConnection_データ1.xlsm!テーブル11", "", _
                        "WORKSHEET;データ1.xlsm", _
                        "データ1.xlsm!テーブル11", 7, True, False)
    Set pvtCache = thisworkbook.PivotCaches.Create( _
                   SourceType:=xlExternal, SourceData:=myConection, Version:=6)
    Set pvtChart = pvtCache.CreatePivotChart(ChartDestination:="Sheet8")
    
    Stop
End Sub

 
みたいに出来そうです。
 
オブジェクトとオブジェクト変数を理解されてますでしょうか?
まだでしたらその辺を一回調べて理解しておくと、
コードの整理がしやすいかと思います。
上記のコードで、とりあえずエラーは出ないと思うので、
Stopで止まったときに、
ローカルウィンドウで各変数の中身を確認してみてください。
(折りたたまれてい居るので「+」をクリックして展開してみてください。)
 
「データ1.xlsm!テーブル11」
 
↑のような文字列を何かのプロパティで参照できるなら、
それを使って指定の文字列を作ってやればよいかと思います。
名前じゃなくて、オブジェクト指定でもAddしてくれそうな気がしますが、、、、
 
こちらは、
ピボットグラフを使ったことないですし、
ピボットテーブル等を作れるようなサンプルデータもこちらではないので、
とりあえず提示された内容でこちらでわかる範囲で書いてみました。
サンプルデータが提示されれば、回答側で同じようにマクロの記録をして、
探ってくれるきとくな方が現れるかもです。
 
参考になれば。。。

回答
投稿日時: 20/11/23 20:50:10
投稿者: simple

皆さんからコメントがありますので、反応をもらうとよいと思います。
 
既に回答いただいていますので、蛇足ですが、少しコメントします。
 
ネット検索して得られた、
[EXCEL VBA でピボットテーブルの挿入を記録する]
https://www.muscle-hypertrophy.com/?p=7166
なども参考になりそうに思います。
 
プロパティの内容が理解できない、とのことですが、
何を以て理解したというのか、難しいところです。
 
例えば、Worksheetに関するものなら、具体的にイメージしやすいですが、
Connections.Add2 メソッドなどは、機能そのものですから、
それと同等の理解がしにくいのは当然で、
具体的なイメージが浮かばなくても、それが自然なことです。
 
要するに、どんな設定にしたときに、どんな結果が得られるか、
それだけに注目して、それらが実現できればよいと思います。
 
自動車を運転するのに、エンジンの内部構造など知らなくてよいのと同じです。
ブレーキやアクセルの機能さえ理解していて操作できればOKなわけです。

トピックに返信