Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Access 2016)
EXCELのピボットテーブルの集計行に色付けする
投稿日時: 17/11/21 17:44:50
投稿者: Masaka

AccessからEXCELをオートメーションで開いています。
ピボットテーブルを作成し、集計行にハイライトをしたいのですが、
下記のコードでは式が不正です。と1004のエラーが発生してしまいます。
 
マクロの記録では確かに下記のコードで、実際、
直接EXCELのマクロに貼り付け(シート名は修正)て実行すると、
動きます。何か見落としがありますでしょうか?
 
        xlBook.Worksheets("予算").PivotTables("予算").PivotSelect "部門名[All;Total]", _
         xlDataAndLabel, True
       With selection.Interior
           .Pattern = xlSolid
           .PatternColorIndex = xlAutomatic
           .ThemeColor = xlThemeColorAccent3
           .TintAndShade = 0.599993896298105
           .PatternTintAndShade = 0
        End With
 
よろしくお願いいたします。

回答
投稿日時: 17/11/21 18:12:25
投稿者: sk

引用:
With selection.Interior

Selection プロパティの親である Excel.Application オブジェクトの
指定を省略できるのは Excel 上でそのプロジェクトのコードを
実行している時だけです。
 
仮に Excel アプリケーションへの参照が渡されている
変数の名前が xlApp であるならば
 
--------------------------------------------------------------
 
With xlApp.Selection.Interior
 
--------------------------------------------------------------
 
のようにするか、もしくは Selection プロパティに頼らず
塗りつぶしの対象となる Excel.Range オブジェクトを取得して
その Interior プロパティ(オブジェクト)を参照するように
して下さい。

投稿日時: 17/11/21 18:40:13
投稿者: Masaka

skさん、どうもありがとうございます。
 
エラーが出ているのはこちらのほうです。
      xlBook.Worksheets("予算").PivotTables("予算").PivotSelect "部門名[All;Total]", _
         xlDataAndLabel, True
 
ちななみに、xlAppから記述してもコンパイルエラーは回避できませんでした。

回答
投稿日時: 17/11/21 19:02:39
投稿者: sk

引用:
コンパイルエラー

引用:
xlBook.Worksheets("予算").PivotTables("予算").PivotSelect "部門名[All;Total]", _
         xlDataAndLabel, True

VBA プロジェクトのライブラリ参照設定に
Microsoft Excel *.* Object Library を
追加されていないのであれば、上記の定数は
プロジェクト内で使用出来る定数としては
みなされません。
(同じ名前の変数/定数がどこにも宣言されていなければ、
 未定義の変数として扱われる)
 
明示的に定数 xlDataAndLabel を宣言するか、
定数 xlDataAndLabel の代わりに
値 0 を渡すようにして下さい。
 
引用:
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3

これらの定数についても同様。
 
Excel 固有の定数の値は、Excel VBA のヘルプなり
オブジェクトブラウザーなりを用いて調べて下さい。

投稿日時: 17/11/21 19:23:10
投稿者: Masaka

どうもありがとうございます。
 
参照設定は当然してあります。
定数のところではなくて、
[]の数が合ってないとか言われています。
 
skさんが赤くハイライトしてくださぅた
定数以下を削除しても同じエラーなのです。

回答
投稿日時: 17/11/22 10:11:36
投稿者: sk

引用:
ピボットテーブルを作成し、集計行にハイライトをしたい

引用:
定数のところではなくて、
[]の数が合ってないとか言われています。

作成されたピボットテーブルの行ラベルとして
[部門名]というフィールドを追加して、
その小計を各グループの先頭に表示するようにしていて、
その小計行を任意の色で塗りつぶしたい、ということでしょうか。
 
引用:
xlBook.Worksheets("予算").PivotTables("予算").PivotSelect "部門名[All;Total]", _
 xlDataAndLabel, True

xlBook.Worksheets("予算").PivotTables("予算").PivotSelect "部門名[All]", _
 xlDataAndLabel + xlFirstRow, True
 
-------------------------------------------------------------------------
 
いずれにしても、実際のピボットテーブルの構造次第ではないかと。
(行ラベルとして追加されているフィールドが[部門名]だけなのか、
 他にも追加されているフィールドがあるのかも現在不明)

投稿日時: 17/11/22 11:45:52
投稿者: Masaka

どうもありがとうございます。
 
実行時エラーは回避できて、ファイルへの出漁できました。
ただし、集計業行ではなく、先頭行に色付けさらているので、
真逆の結果になっている状況です。
 
定数で先頭を指定しているので想像通りの結果ではあります。
定数一覧を見ましたが、最終行という定数はなさそうでした。
 
ちなみに、集計を取っているカラムはファイルにおうじて、
2つもすくは3つありますが、合計、集計という名称は使い分けしてます。
マクロの自動記録のコードを基本的に流用しています。

回答
投稿日時: 17/11/22 12:11:17
投稿者: sk

引用:
実行時エラーは回避できて、ファイルへの出漁できました。
ただし、集計業行ではなく、先頭行に色付けさらているので、
真逆の結果になっている状況です。
  
定数で先頭を指定しているので想像通りの結果ではあります。
定数一覧を見ましたが、最終行という定数はなさそうでした。

オートメーションを実行しているコードに
ピボットテーブルを一から作る部分も含まれているとして、
追加されたフィールド[部門名]の Subtotals プロパティや
LayoutSubtotalLocation プロパティを
明示的に設定するコードを書かれてはいないのでしょうか。

投稿日時: 17/11/22 15:37:57
投稿者: Masaka

行プロパティで集計の設定をしています。
 
集計行なに色付けするところをコメントアウトして、
エクセルを生成したものに対して、本ファイルに直さあ、
集計行を色付けするマクロだけを移植して実行してみると、
何も問題なく期待通りの色付けをしてくれます。
※シート名はactivesheetに変更
 
実行時エラー:1004
数式が不正です。対応する角かっこ(])があることをご確認下さい。
 
この原因がわかりません。
 
ループを回して背景色を付ければ機能的には解決する話なのですが、
どうしても上記のぎもんが解決できません。

回答
投稿日時: 17/11/22 16:13:08
投稿者: sk

引用:
行プロパティで集計の設定をしています。

Subtotals プロパティや LayoutSubtotalLocation プロパティを
設定するコードを実行せず、ピボットテーブルが自動生成された後で、
行ラベルとして追加されたフィールド[部門名]の設定を
Excel から直接行なわれている、ということでしょうか。
 
引用:
実行時エラー:1004
数式が不正です。対応する角かっこ(])があることをご確認下さい。
  
この原因がわかりません。

[部門名]の小計行の設定を行なう(コードを実行する)前に
PivotSelect メソッドを実行なさろうとしているのであれば、
あり得ない現象ではないと思います。
 
引用:
xlBook.Worksheets("予算").PivotTables("予算").PivotSelect "部門名[All;Total]", _
 xlDataAndLabel, True

仮に「[部門名]の小計行の集計方法を『自動』とし、
かつ小計行をグループ内の末尾に表示する」ように
設定されていない場合、そのまま上記のコードを実行すれば
選択すべき範囲が見つからないため、実行時エラーが発生するはず。
 
引用:
マクロの自動記録のコードを基本的に流用しています。

プロシージャ内の全てのコードが明記されていないので
断言は致しかねますが、それぞれのステートメントを
実行する順番や流れに誤りがないかどうか、改めて
ご確認されてみてはいかがでしょうか。

投稿日時: 17/11/22 20:15:33
投稿者: Masaka

skさん、ご回答ありがとうございます。
 
>[部門名]の小計行の設定を行なう(コードを実行する)前に
>PivotSelect メソッドを実行なさろうとしているのであれば、
>あり得ない現象ではないと思います。
 
このヒントをもらってよく見直してみました。
まさに、小計を設定をするまえに、
PivotSelect メソッドを実行してました。
 
この不具合を修正し、期待通りの結果になりました。
どうもありがとうございました。