★Access2000VBA・Excel2000VBA独学~Access2019のクエリをソースに、COMオートメーションでExcel2019のファイルを自動生成し、ピボットも自動生成する方法~
  
  

★ はじめに

まさかAccess2019でピボットテーブルが作れなくなっているとは知りませんでした。

ピボットテーブルは集計がしやすいのでAccessと切っても切れない関係です。

Accessには「クロス集計クエリ」というものがあるのですが、1段階の列名や行名ならウィザードやあるいはクエリのデザイングリッドで簡単に作れるのですが、2段階以上の列名や行名になると、SQLを知らないと作れません。(知っててもできるかどうかわかりません。多分できるとは思うんですけど・・・)

その点、ピボットテーブルはSQLを知らなくても、誰でも簡単に、何段階もの列名や行名を持ったクロス集計表が作成できます。

もともと、2000のときも、ピボットテーブルは使い勝手が良くなかったのでピボットはExcel側で作っていましたので、今回もそのような形でピボットを作ってみたいと思います。

すでに存在する「Accessのクエリをソースにしたピボット」のソースのSQL内容を変える・・・ということはやったことがあったのですが、新規に作る、ということはしたことがなかったので、今回、実験してみました。

実験なので、間違っている部分もあるかもしれませんが、何かのご参考になれば。

  

  

★ マクロの記録のコードを流用した、もっとも簡単な方法の実験

僕も良く分からないのですみませんが、以下のような流れてサンプルを作ることができました。

(01)Excel2019からAccessファイル(accdb形式)のクエリをソースにしたマクロの記録を作成

(02)そのコードを流用して、COMオートメーションにて、AccessからExcelファイルを自動生成し、そこにピボットが生成されるようにする
  

ピボットがらみのコードは、ヘルプにも、「結構複雑になるのでマクロの記録を利用して作ってくだあし」的なことが書いてあるので、基本、このようなやりかたでOKです。
(あとこちらの神サイトもご参考に→雨のち晴れ

コードは以降の「test002()」のようになりました。
Excelファイルが生成されてすぐあとに、途中で、添付画像のようなダイアログが出ますが、そのままOKしたらExcelファイルの中にピボットが自動生成されました。

長いので、「 '生成されたピボットテーブルの各種設定?」のところ(「'Excelファイルの最初の保存」の直前まで)の各種設定は、全部消すか、コメントアウトしても動くと思います。

※※ 事前にMicrosoft Excel 16.0 Object Library に参照設定をしてから実行します。
 なお、複数のバージョンのExcelを共依存させていると、エラーで動かないかもです。
 また、ご自分の環境に合わせて「★ 設定部」のところのクエリ名を変えてください。
 クエリとテーブルが使えます(どちらの名前でもOKです。)。

  
プログラムの途中で出てくるダイアログは以下のようなものでした。
これをそのままOKします。
多分接続文字列で何らかの記述を変更・あるいは追加すれば、出なくなるのではないかと推測されます。


  

なお、このプログラムは、他のバージョンのExcelが入っていると動かないかもしれません。※※ (当方では2010と2019が混在するマシンでは動きませんでした。)

もちろん、2019オンリーのマシンでは動きました。
(旧Office365 Solo = 現Microsoft365 Psesonalの2019です。)

また、1004 のアプリケーション定義エラーが出るかもしれません。
その際は、ご自身の状況に合わせて、後述のようなExcelでの「ピボットを作るマクロ」を1つ作って、それをExcel上で実行してから上記コードを実行するとエラーが出なくなるかもしれません。

どうしても1004エラーが消えない場合は、さらに後述する、「ちょっとめんどくさいですけどエラーの出ない方法」でやる、という選択肢もあります。

ダイアログが出てもいいなら、上記のようにマクロコードをそのまま「ほぼほぼ流用」してしまうのが一番ラクちんです。

なお、ピボット作成のマクロの記録のコードは、バージョンによって内容が変わるので、そこも注意が必要です。

上記のコードは2019で作ったマクロの記録のコードを流用しているので2019でしか動かないかもです。
マクロ記録でコードを一度ご自分で生成して、それを上記の例を参考に作り変えるなどしてみてください。

なお、「ピボットテーブル1」というのは生成するピボットの名前ですが、必要に応じてこれも変数化して、
「臨機応変に異なる名前を付けられるようにする」・・・
といいかもしれません。
たとえば、1シート、1ブック、の中に複数のピボットを作りたい場合とかは・・・。

**********

'ExcelファイルからAccessファイルへ接続?
xlBook01.Connections.Add "db2", "", _

の部分の、

「 "db2" 」はどんな名前でも良いっぽいです。

なので、これも必要に応じて好きな名前に変えられるようにしても良いかもしれません。
  

  

===================

あと、以下は、マクロの記録で生成されたコードです。
今回はこれをベースにして、このコードを少し修正したものをAccess上にて動かしています。
Accessから動かす場合は、Excelファイルにはプログラムは1行も書きません。
ご参考まで。

※Accessで1004エラーが出る場合は、以下のようなコードを、ご自身で「マクロの記録」でつくることができますので、作ったのち、それをいったん実行すると、Accessで実行する時に1004エラーが出なくなるかもしれません。
  

  
  
  

★ Excel2000のマクロの記録のコードを流用する実験

実験して見ましたらExcel2000のピボット作成のマクロのコードがなぜか2019でも、つまり、「accdb」に対しても使えてしまいました。

Excel2000のピボット作成のマクロのコードは、ソースにSQLを使うので、色々といじれて便利なうえに、2019のマクロコードよりもシンプルなのでおススメかもしれません。

ただ、出てくるダイアログをOKしても進まないので、これは完全に・・・、さらに後述する、「ちょっとめんどくさいですけどエラーの出ない方法」でやる、選択肢しか無いかもしれません。