Excel2000・2003～「ピボット」や「Microsoft Query の結果表」にて、ソースの表の指定時にMicrosoft Queryを使った場合の「CommandText（SQL文）」のVBAでの自動変更について

● Excel2000・2003～「ピボット」や「Microsoft Query の結果表」にて、ソースの表の指定時にMicrosoft Queryを使った場合の「CommandText（SQL文）」のVBAでの自動変更について

目次
　★ はじめに
　★ VBAプログラムで「CommandText（SQL文）」を扱える（=自動書き換え）できる条件
　★ VBAプログラムで「CommandText（SQL文）」を自動変更すると何ができるの？
　★ 「CommandText（SQL文）」をVBAで自動書き換えする場合に使う変数について
　★VBAプログラムで 「CommandText（SQL文）」を自動書き換えする例
※Shift＋TABキー、もしくは、Homeキー、Homeキー＋TAB数回、を押すと、目次付近に戻れます。
※まだ書きかけです。すみません。
※間違ってたらすみません。
※メモ書きなので、自分でも意味不明な箇所も多いです。ごめんなさい。

★ はじめに

「ピボット」でも、「Microsoft Query の結果表」でも、ソースのデータを「Microsoft Query（外部データソース）」で作った場合に限り、VBAプログラムにて「CommandText」が扱えます。

※これでピボットソースを作った時 ↓ や、Microsoft Query でExcelに結果を返した時です。

「CommandText」の内容は「SELECT・・・」といったような「SQL文」です。

ピボットソースやMicrosoft Queryの結果表を作る際に、「ExcelファイルやAccessファイルと等々から、どのデータをどのように抜き出したか」、が書かれています。

（SQL文とは・・・データ管理の世界では、「世界標準」と言っても過言ではない・かつ・複式簿記と同じくらいの有名度の自動データ集計＆リストアップ命令群です。）

そして「CommandText（SQL文）」がVBAで自由に書き換えられると、Accessとのピボット連携が強化できたり、Excelのミニシステム化、自動集計化、などがさらにパワーアップします（と思います。多分・・・。）
ピボットのフィールドリストの内容もVBAで自由に自動的に変更できます。なので利用価値が高いと思います。

ちなみに、そのほかの場合・・・つまり、ピボットのソースを「外部ソース」で作ってない場合・・・、例えば「単純にシートを選択しただけの普通のピボットのソース」の場合は、「CommandText」は扱えません。

今回の記事では、Excelの2000と2003を使って、その「CommandText（SQL文）」をVBAで自動修正・自動変更するために、どんなデータ型を使ったらいいか、などを調べてみました。参考文献が見つからなかったので、僕が根本的に何か考え違いをしている部分もあるかもしれませんが、分かったところだけでもご紹介いたします。

なお、ピボットソースとMicrosoft Queryの結果表の「CommandText（SQL文）」を扱ううち、ピボットソースのほうだけ「配列」という仕組みを利用するのですが、テストでは「配列の各要素ごとに文字数の制限」がありました。そして、その上限文字数は状況によってまちまちです（テストでは何故かそうでした）。その理由は現時点ではわかりません。この場合も、参考文献が見つからなかったので、僕が根本的に何か考え違いをしているのかもしれません。その際は大変申し訳ございません。

★ VBAプログラムで「CommandText（SQL文）」を扱える（=自動書き換え）できる条件

・ピボットの場合
ピボットのソースが、Microsoft Query によってかたちづくられている場合に限ります。

例えば Excel2000・2003の場合、ピボットのソースを指定するとき、ウィザードの最初のほうで「外部データソース」を指定した場合です。
また、ピボットテーブルツールバーなどから、ピボットテーブルウィザードにて、「戻る」ボタンを押した場合、ダイアログに「データの取り出し」ボタンが表示される場合もそうです。

実は、「外部データソース」 ボタンも、「データの取り出し」ボタンも、押すと出てくるのは「Microsoft Query」の画面です。

つまり、「外部データソース＝データの取り出し＝Microsoft Query」・・・

「外部データソース」も、「データの取り出し」も「Microsoft Query そのもの」ということなのです。

ちなみに、「外部データソース（Microsoft Query）」を使った場合、AccessのmdbファイルやExcelのxls・xlsm・xlsxファイルだけでなく、その他のデータベースでも、ピボットソースの指定先にすることができますが、そのすべてがOKです。「CommandText（SQL文）」を扱うことができます。

ピボットの場合は、とにかくソースが「外部データソース」で作られていれば、
「CommandText（SQL文）」を扱うことができます。すなわち、VBAで自動書き換えができます。

「外部データソース」で作られたピボットソースではない場合や、ウィザードの「戻る」を押したときに何らかのシートが点滅する点線で囲まれるような場合、そのような場合は、「CommandText（SQL文）」を扱えません。

・Microsoft Query の結果表の場合
必ず、「CommandText（SQL文）」を扱えます。
つまり、「CommandText（SQL文）」の自動修正ができます。

★ VBAプログラムで「CommandText（SQL文）」を自動変更すると何ができるの？

・【ピボットの場合】
(01)「フィールドリスト」の内容を自動的に書き換えることができます。
例えば、表示させたいフィールド・一時消しておきたいフィールドなどを簡単に指定し分け、更新することができます。

・【Microsoft Query の結果表の場合】
次項の【両方とも】に準じます。

・【両方とも】
(01)リレーション構成などをSQL文にて自動切り替えできます。
（リレーション＝VLOOKUP関数での紐付けのようなこと）

(02)「CommandText（SQL文）」を次々と自動的に切り替えて集計やリストアップを行う

(03)同じ構成のソース表なら、複数のファイル間でも瞬時に自由に切替できます。
たとえば同じ列構成でファイル名やシート名だけが違うソースファイルが複数あったとして、その時に、集計専用のExcelファイルなどにおいて、「それらの複数のソースファイルの中から、目的に合ったソースファイルを自由に自動的に瞬時に切り替えして色々調べたい・・・」といった場合に、いちいち、戻るボタンで別のソースファイルを指定しなおさなくても大丈夫になります。ソースファイルがmdbでもxlsでも同じです。

(04)ソースをmdbからxlsに自動切り替えしたい、などができます。

(05)集計機能のシステム化（既存集計ファイルの流用・コピペ増殖）
ピボットもMicrosoft Queryも両方を含むまったく同じ集計手順・表構成のExcelファイルで「ファイル名だけ変えたい」という場合・かつ・その自ファイル自体にもソース表が含まれるような場合・・・に、「その自ファイルの複製を作りさえすれば、あとはソース指定に自ファイル名に自動書替えするだけで別の集計システムのできあがり。処理が1分～5分で終わる。」というようなことができます。

(06)重複を簡単にとりのぞく、再度表示させる、を何度も瞬時に切り替えする。

★ 「CommandText（SQL文）」をVBAで自動書き換えする場合に使う変数について

ピボットの場合は文字列型かVariant型の配列（要素ごとに文字数制限あり）、Microsoft Query の場合は配列じゃなくて普通の文字列型でいいようです。

僕はピボットの場合は、Variant型の配列が便利じゃないかなと思います。
何かエラーが出るようなことがあれば、文字列型の配列のほうがいい場合もあるかもしれません。ただ、まだそこまでテストできていません。

なお、参考文献が無いので よくわからないのですが、ピボットの配列の場合は下図のように要素毎に文字数制限があるようです。制限は文字数だけのようなので、たとえば １つの要素のなかでSELECT句の一部とFROM句の一部がつながっていても問題は無いようです。また、空文字が値となっている要素があっても問題ありません。
また、SQL文が、「SLELECT句、FORM句、WHERE句等々、全部」で、半角でその文字数 以内（要素１つ分以内）だと、その場合は、配列じゃなくても単一の文字列型の変数でも大丈夫みたいです。

Microsoft Query の場合は、このような配列のことを考えなくてもいいみたいです。ただ、テストが不十分で、実は、数千文字まで、とかの制限があるのかもしれません。その際は申し訳ございませんが、ご自分でもチェックしてみてください。

このテストは、2000と2003なので、もしかしたら2007以降は、ピボットでも配列じゃなくてもよくなってるかもしれません。その場合も、ご自分でもどちらなのかチェックしてみてください。

★VBAプログラムで 「CommandText（SQL文）」を自動書き換えする例

以下は、Excel2000や2003での「ピボットテーブル」の場合の、自動書き換え例です。

ピボットから mdbファイル（Accessのファイル）を覗きに行った時の例です。

好きなフィールド名の「SqlStr02 = SqlStr02 & "T顧客マスタ.ビル名, "」のような行をコメントアウトすれば、そのフィールドはピボットテーブル上のフィールドリストから消すことができます。コメントアウトを解いて再実行すれば、再度、フィールドリストに表示できます。

プログラムについては、まず、ピボットの書き換えの場合、SQL文を代入する変数には、Variant型を使っています。配列として使用します。（多分、文字列型の配列を使ってもOKではないかと思われます。）

なお、Excel2000や2003の場合、その配列の変数においては、１要素あたりで、半角で394文字前後（バージョンその他によって405文字とか307文字とか）を超えるとダメみたいです。「型がちがう」みたいなエラーになります。

また、SQL文が、「SLELECT句、FORM句、WHERE句等々、全部」で、半角でその文字数 以内だと、その場合は、配列じゃなくても単一の文字列型の変数でも大丈夫みたいです。

※Excelファイルを覗きにいった場合は、フィールド名（列名）に「'」が含まれるので少し変わります。句と句のあいだには、最低１つの半角スペースが必要です。１つ以上あれば、２つでもいくでも構いません。ただし、全角スペースはエラーとなります。