Excel2000でのピボットテーブルのPivotCache.CommandText（ピボットのソースのSQL文内容)のVBAでの自動書き換え

● Excel2000でのピボットテーブルのPivotCache.CommandText（ピボットのソースのSQL文内容)のVBAでの自動書き換え

※間違いが判明しました。プログラム紹介のあとの別テスト結果についてのまとめを必ず参考にしてください。

今日はうれしいことがありました。（何年か前のお話です）

悩んでいたピボットテーブルのPivotCache.CommandTextのVBAでの自動書き換えができるようになりました。＼(^^)／

何回やっても「1004：実行時エラー」になってしまい、ヘルプやWebにも情報がなく、２日間くらい悩みましたが、でもがんばって推測して、やっと答えを見つけました。＼(^^)／

久々に格闘した～！！＼(^^)／

そして勝った！！＼(^^)／＼(^^)／＼(^^)／

うーん、Excel2007以降だとこのような苦労は無いようでしたが（そのままテキストデータをテキスト型として設定できたので）、Excel2000～2003までは、PivotCache.CommandTextのデータは配列データで設定してあげないといけないという、メッチャ面倒なことになっていました。

しかも、SQLの句ごとに配列の要素に指定しないといけないという・・・(^^)

そんなんヘルプのどこにも書いてないやん！！(^^)

Webにもないし・・・
英語のサイト見てもわからんかった・・・
英語自体もわからへんし。。。。 (TT)

イミディエイトウィンドウで

? PivotCache.CommandText = "aaa" ってやっても
? PivotCache.CommandText = "SELECT フィールド名 FRROM ファイル名テーブル名（正しいSQL内容）" ってやっても
どっちでやっても「False」が返ってきて、「なんか変だなあ・・・前者はもちろんFalseでいいけど後者はTrueじゃないの？？？」と思ってたら、
? PivotCache.CommandTextでSQL表示したときに、FROM句の前に改行入ってるから気が付いたです・・・。

ん？改行入ってる？

おかしいやん！なんで？

PivotCache.CommandTextがもし単一のテキストデータだとするなら、そんなかに改行コードなんて自動的に含まれるのか？
SQL文の途中で改行コードが含まれるなんてありえん気が・・・

あ！！！

もしかして PivotCache.CommandText は単純なテキストデータではなく、
テキストデータの配列なのでは？？？

・・・と、悩みに悩んでもしやと思って、配列でSQLを代入する試しのコードを書いてみたら・・・

ビンゴでした！！！！

八方ふさがりだったけど、ついに見つかった～！！

わーい！＼(^^)／

しかし・・・

なんで配列なんだよおおお？？？

くそう、ヘルプにちゃんとそう書いておいておくれよ～！！！

Variant型としか書いてないからすぐには分かんなかった・・・

でも結局「Variant」もヒントになって「配列」かもと思いつきましたが・・・
サンプルコードは配列ではなく完全に単一テキストを指定（代入）するように書いてあったので、それにも騙されてしまいました・・・悲し・・・

プログラミングのプロならこんなの数分で気づくんだろうな・・・
僕はプログラミングのプロじゃないから2日もかかってしまった・・・

しかし、最初から「データ型はVariant型で設定にはSQLの句ごとに要素格納された配列を使用します・・・」ってヘルプに書いてくれればこんなに悩まなかったのに・・・(TT)

おまけにAccessほうのピボットではPivotCache.CommandTextのデータ型は配列じゃなくてただのテキストデータだったから、それにも目をくらまされて迷ってしまいました・・・。泣

でも何はともあれ、解決できてよかたです。
これでピボットテーブルの扱いが格段に楽になって、しかもAccessとの連携も深まるです・・・。

ほんと、よかった・・・。＼(^^)／

作業が楽になる・・・。(^^)

各句の配列の要素の文字数の制限は、あるかもしれませんが、相当長くてもいいみたい・・・。
以下、標準モジュールに貼り付けてテストする用のコードです。

'##############################################################
'このコードは、ウィザードを使わずに、VBAにて
'ピボットテーブルのフィールドリストの内容を自動修正します。
'（ソース側にある列名しかもちろん使えませんが。）
'また、このコードが使えるのは、ピボットのソースがMicrosoftAccessの
'テーブルかクエリを対象としている場合のみです。
'また、ピボットテーブル内のセルにフォーカスが無いといけません。
'（後日調査：ソースがMSQueryでもPivotCache.CommandTextで
'SQLが返ってくるのでもしかしたらOKかも・・・試してないですが）

'PivotCache.CommandText は、
' ? ActiveSheet.PivotTables(1).PivotCache.CommandText
'というかたちでイミディエイトすると
'句ごとに改行されて出力されます。
'ということは、PivotCache.CommandText のデータ型は
'テキスト型ではない可能性があります。
'よって、PivotCache.CommandText は配列データであると推測・仮定して、
'代入にはVariant型の変数を使ってテスト。
'SELECT区、FROM句、に分けて配列を作ってテストします。
'なので、もしイミディエイトで
' ? ActiveSheet.PivotTables(1).PivotCache.CommandText
'としたときにWHERE区があったら、その分の変数も作成して、FROM句の前に
'配列としてセットする必要がありそうです。

'基本、以下のコードでPivotCache.CommandTextの内容が変更できます。
'なお、リフレッシュしようがしまいが、「データ」エリアのフィールドが
'SQL内から消えると、ピボットは真っ白になります。
' （存在する列名、行名だけが残ります）
'
' ●注意事項
'このコードでPivotCache.CommandTextを変更できるピボットテーブルは、
'現在アクティブになっているピボットテーブルだけです。

'フィールド（列）の数にあわせて必要に応じて
'「StrSELECTword = StrSELECTword & " Table名.フィールド名01,"」
'の行を増減します。

'最後にカンマを含まない、
'「StrSELECTword = StrSELECTword & " Table名.フィールド名01"
 'の行は一番最後のフィールド（列）の名前を指定します。

'カンマを含む行では、先頭列から最終列直前列までを指定します。

'各行、スペースが含まれていますが消さないでください。

'FROM句ではAccessファイルとテーブル（もしくはクエリ)を指定します。
'「`D:\フォルダ名`」では、サブフォルダあればそれも含めて指定します。

'WHERE句が必要な場合はFROM句のように、SQLを書くように追加します。
'WHERE句用の変数も宣言して。
'WHERE句やその他の句が必要かどうかは、事前に、
'イミディエイトウィンドウで
' ? ActiveSheet.PivotTables(1).PivotCache.CommandText 
 'とやって、返ってきたSQL内容をチェックしておきます。

'##############################################################

Sub PivotCacheCmdTextEdit()

Dim SQLArrey As Variant
Dim StrSELECTword As String
Dim StrFROMword As String

StrSELECTword = "SELECT"
StrSELECTword = StrSELECTword & " テーブル名.フィールド名01,"
StrSELECTword = StrSELECTword & " テーブル名.フィールド名02,"
StrSELECTword = StrSELECTword & " テーブル名.フィールド名03,"
StrSELECTword = StrSELECTword & " テーブル名.フィールド名04,"
StrSELECTword = StrSELECTword & " テーブル名.フィールド名05"

StrFROMword = " FROM `D:\フォルダ名`.Accessのファイル名（mdb拡張子指定なしでOK）"
StrFROMword = StrFROMword & " Accessのファイル名（mdb拡張子指定なしでOK）"

SQLArrey = Array(StrSELECTword, StrFROMword)

'PivotCache.CommandText の内容の変換
'この時点で、フィールドリストの表示内容が切り替わります。
ActiveSheet.PivotTables(1).PivotCache.CommandText = SQLArrey

'PivotCacheをリフレッシュ
'してもしなくても大丈夫みたいだけど、一応、する。
ActiveSheet.PivotTables(1).PivotCache.Refresh

End Sub

'##############################################################

'このコードは、ウィザードを使わずに、VBAにて

'ピボットテーブルのフィールドリストの内容を自動修正します。

'（ソース側にある列名しかもちろん使えませんが。）

'また、このコードが使えるのは、ピボットのソースがMicrosoftAccessの

'テーブルかクエリを対象としている場合のみです。

'また、ピボットテーブル内のセルにフォーカスが無いといけません。

'（後日調査：ソースがMSQueryでもPivotCache.CommandTextで

'SQLが返ってくるのでもしかしたらOKかも・・・試してないですが）

'PivotCache.CommandText は、

' ? ActiveSheet.PivotTables(1).PivotCache.CommandText

'というかたちでイミディエイトすると

'句ごとに改行されて出力されます。

'ということは、PivotCache.CommandText のデータ型は

'テキスト型ではない可能性があります。

'よって、PivotCache.CommandText は配列データであると推測・仮定して、

'代入にはVariant型の変数を使ってテスト。

'SELECT区、FROM句、に分けて配列を作ってテストします。

'なので、もしイミディエイトで

' ? ActiveSheet.PivotTables(1).PivotCache.CommandText

'としたときにWHERE区があったら、その分の変数も作成して、FROM句の前に

'配列としてセットする必要がありそうです。

'基本、以下のコードでPivotCache.CommandTextの内容が変更できます。

'なお、リフレッシュしようがしまいが、「データ」エリアのフィールドが

'SQL内から消えると、ピボットは真っ白になります。

' （存在する列名、行名だけが残ります）

' ●注意事項

'このコードでPivotCache.CommandTextを変更できるピボットテーブルは、

'現在アクティブになっているピボットテーブルだけです。

'フィールド（列）の数にあわせて必要に応じて

'「StrSELECTword = StrSELECTword & " Table名.フィールド名01,"」

'の行を増減します。

'最後にカンマを含まない、

'「StrSELECTword = StrSELECTword & " Table名.フィールド名01"

'の行は一番最後のフィールド（列）の名前を指定します。

'カンマを含む行では、先頭列から最終列直前列までを指定します。

'各行、スペースが含まれていますが消さないでください。

'FROM句ではAccessファイルとテーブル（もしくはクエリ)を指定します。

'「`D:\フォルダ名`」では、サブフォルダあればそれも含めて指定します。

'WHERE句が必要な場合はFROM句のように、SQLを書くように追加します。

'WHERE句用の変数も宣言して。

'WHERE句やその他の句が必要かどうかは、事前に、

'イミディエイトウィンドウで

' ? ActiveSheet.PivotTables(1).PivotCache.CommandText

'とやって、返ってきたSQL内容をチェックしておきます。

'##############################################################

Sub PivotCacheCmdTextEdit()

Dim SQLArrey As Variant

Dim StrSELECTword As String

Dim StrFROMword As String

StrSELECTword = "SELECT"

StrSELECTword = StrSELECTword & " テーブル名.フィールド名01,"

StrSELECTword = StrSELECTword & " テーブル名.フィールド名02,"

StrSELECTword = StrSELECTword & " テーブル名.フィールド名03,"

StrSELECTword = StrSELECTword & " テーブル名.フィールド名04,"

StrSELECTword = StrSELECTword & " テーブル名.フィールド名05"

StrFROMword = " FROM `D:\フォルダ名`.Accessのファイル名（mdb拡張子指定なしでOK）"

StrFROMword = StrFROMword & " Accessのファイル名（mdb拡張子指定なしでOK）"

SQLArrey = Array(StrSELECTword, StrFROMword)

'PivotCache.CommandText の内容の変換

'この時点で、フィールドリストの表示内容が切り替わります。

ActiveSheet.PivotTables(1).PivotCache.CommandText = SQLArrey

'PivotCacheをリフレッシュ

'してもしなくても大丈夫みたいだけど、一応、する。

ActiveSheet.PivotTables(1).PivotCache.Refresh

End Sub

＝＝＝＝＝＝＝＝＝＝＝

※追記
2017/03/08 Excel2010で、ピボットのソースがMSQyeryの場合も、変数をVariantの配列じゃなく通常のテキスト型にして、SQL文のすべての句を半角スペース区切りで１つにつなげると、この場合も変更できました。xlsmを閉じる前じゃないと有効じゃないのかもしれないですが・・・。
リボン→ピボットテーブル→オプション→データソースの変更→接続のプロパティ→定義→クエリの編集ボタン、にて、そのボタンを押したときにMSQueryの編集画面が立ち上がる間は大丈夫みたいです。
「接続のプロパティ」メニューがグレーアウトしている場合はできません。

※重要！！追記2（2000と2003でのテスト結果）

ちょっと配列のことについて記述が間違っていたので訂正いたします。
句ごとの区切りではなく、どうやら、文字数ごとの要素の区切りだったみたいです。
SQL文の内容を代入する文字列型の配列の変数にて、１要素あたりで、半角で394文字くらいを超えるとダメみたいです。「型がちがう」みたいなエラーになります。
SQL文が、全部で半角で394文字以内だと、大丈夫みたいです。
その場合は、配列じゃなくても単一の文字列型の変数でも大丈夫みたいです。

'
'
'
Sub test03()

    Dim SqlStr01 As String
    Dim SqlStr02 As String
    Dim SqlStr03 As String
    Dim SqlStr04 As String
    Dim SqlStr05 As String
'    Dim SqlStrALL As String '要らなかった。半角394文字以内のSQL文用。
    Dim SQLArrey As Variant
    
'    Debug.Print Worksheets("ソース2").QueryTables(1).Sql
'    Debug.Print Worksheets("ソース2").QueryTables(1).CommandText
    
    SqlStr01 = "SELECT "
    SqlStr01 = SqlStr01 & "T顧客マスタ.顧客ID, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.名, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.姓, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.氏名, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.名フリガナ, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.姓フリガナ, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.国籍, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.性別, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.年齢, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.郵便番号, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.住所01, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.住所02, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.TEL, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.市, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.区, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.郡, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.町, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.字等, "
    SqlStr01 = SqlStr01 & "T顧客マスタ.番地  , "

    SqlStr02 = SqlStr02 & "T顧客マスタ.ビル名, "
    SqlStr02 = SqlStr02 & "T顧客マスタ.棟など, "
    SqlStr02 = SqlStr02 & "T顧客マスタ.部屋名, "
    SqlStr02 = SqlStr02 & "T顧客マスタ.階, "
    SqlStr02 = SqlStr02 & "T顧客マスタ.お仕事, "
    SqlStr02 = SqlStr02 & "T顧客マスタ.趣味, "
    SqlStr02 = SqlStr02 & "T顧客マスタ.愛読書カテゴリ, "
    SqlStr02 = SqlStr02 & "T顧客マスタ.ネット通販, "
    SqlStr02 = SqlStr02 & "T顧客マスタ.内ネットオークション, "
    SqlStr02 = SqlStr02 & "T顧客マスタ.免許更新日, "
    SqlStr02 = SqlStr02 & "T顧客マスタ.ダイエット, "
    SqlStr02 = SqlStr02 & "T顧客マスタ.エステ, "
    SqlStr02 = SqlStr02 & "T顧客マスタ.オススメ料理店, "
    SqlStr02 = SqlStr02 & "T顧客マスタ.年収_ご夫婦込み, "
    SqlStr02 = SqlStr02 & "T商品マスタ.商品ID, "
    
    SqlStr03 = SqlStr03 & "T商品マスタ.メーカー名, "
    SqlStr03 = SqlStr03 & "T商品マスタ.アイテム, "
    SqlStr03 = SqlStr03 & "T商品マスタ.上代, "
    SqlStr03 = SqlStr03 & "T商品マスタ.下代, "
    SqlStr03 = SqlStr03 & "T商品マスタ.入荷数, "
    SqlStr03 = SqlStr03 & "T商品マスタ.色系統, "
    SqlStr03 = SqlStr03 & "T商品マスタ.ターゲット年齢層, "
    SqlStr03 = SqlStr03 & "T商品マスタ.全商品ID表示フラグ, "
    SqlStr03 = SqlStr03 & "T売上明細.売上ID, "
    SqlStr03 = SqlStr03 & "T売上明細.売上日, "
    SqlStr03 = SqlStr03 & "T売上明細.顧客ID, "
    SqlStr03 = SqlStr03 & "T売上明細.お名前, "
    SqlStr03 = SqlStr03 & "T売上明細.電話番号, "
    SqlStr03 = SqlStr03 & "T売上明細.商品ID, "
    SqlStr03 = SqlStr03 & "T売上明細.アイテム, "
    SqlStr03 = SqlStr03 & "T売上明細.実売単価, "
    SqlStr03 = SqlStr03 & "T売上明細.仕入単価, "
    SqlStr03 = SqlStr03 & "T売上明細.売上点数, "
    
'    SqlStr02 = SqlStr02 & "T売上明細.売上金額（1行当り）, "
'    SqlStr02 = SqlStr02 & "T売上明細.仕入金額（1行当り）, "
'    SqlStr02 = SqlStr02 & "T売上明細.レシートNo"

    '句ごとじゃなくて、文字数ごとの配列のようなので、次のように、SELECT句とFROM句が混ざってもいいし,
    'フィールドとフィールドのあいだや、各句のあいだに、半角スペースならいくつ入ってもOKです。
    SqlStr04 = " T売上明細.仕入単価,                    T売上明細.売上点数, T売上明細.売上金額（1行当り）, T売上明細.仕入金額（1行当り）, T売上明細.レシートNo       FROM    `D:\pos\pos_ac`.T顧客マスタ T顧客マスタ, `D:\pos\pos_ac`.T商品マスタ T商品マスタ, `D:\pos\pos_ac`.T売上明細 T売上明細"
    
    '配列の要素の１つが、値が空文字でもOKのようです。
    SqlStr05 = ""
    SqlStr06 = "   WHERE T売上明細.顧客ID = T顧客マスタ.顧客ID AND T売上明細.商品ID = T商品マスタ.商品ID"  'ac
    
    'ピボットの「CommandText」に代入する値の生成
    SQLArrey = Array(SqlStr01, SqlStr02, SqlStr03, SqlStr04, SqlStr05, SqlStr06)

'    Debug.Print SqlStr01
'    Debug.Print SqlStr02
'    Debug.Print SqlStr03
    
    Worksheets("Sheet1").PivotTables(1).PivotCache.CommandText = SQLArrey
    Worksheets("Sheet1").PivotTables(1).PivotCache.Refresh
    
    
    '※ ピボットの「CommandText」の内容を変更することで、ピボットのフィールドリストの中の項目を、自動的に増やしたり減らしたり
    '   内容変更することができます。
        
    '結局、Microsoft Query の結果表の「CommandText」とピボットの「CommandText」は文字数制限？に違いがあるようです。
    'とりあえずピボットの「CommandText」の場合は、句ごとに区切られているわけではなく、
    '「一定の文字数」で区切られた文字列型の配列みたいです。
    '
    'その文字数は、1つの要素あたり、半角で394文字（？）くらいまでみたいです。
    '（本当の文字数の制限の仕様はちゃんと調べてないので良く分かっていません。すみません。
    '  エラーが出る境目を調べたらだいたいそのくらいでした｡
    '  エラーのときは、「型が一致しません」、みたいなエラーになります。
    '  なので、SELECT句は300文字ずつくらいで区切ればいいようです。）
    
    '※ Microsoft Query の結果表の「CommandText」は、かなりの単一の文字列型変数で、半角で394文字以上、
    '   テストでは1000文字近くでも大丈夫だったので、そのへんまでは多分大丈夫です。
    '   また、Microsoft Query の結果表の「CommandText」は、
    
    
    'また、句と句のあいだや、フィールドとフィールドのあいだには、半角スペースがいくつあってもエラーになりませんでした。
    '全角スペースは１個でもあるとエラーになるのでタイプミスしないように注意が必要です。
    '半角で394文字以内に収まるSQL文なら、変数は、単一の文字列型でも文字列型の配列（Variant型）でもどちらでもいいみたいです。
    'SQL文が半角で394文字を超えると、文字列型の配列（Variant型）でないとエラーになるようです。
    'なお、配列の要素の数の制限（上限）はテストしてないのでわかりません。
    'ただ、どこかの要素の値が「""（空文字）」であっても大丈夫です。
    'また、SELECT句に、いくつの要素を使ってもかまわないし、
    'FROM句やWHERE句などが、何番目の要素にこないといけない、という制限は無いようです。
    
    'あと、ピボットのCommandTextの場合は、文字数で区切られた配列なので、
    '要素の値が、SELECT句の一部とFROM句が混ざったものになっても大丈夫みたい。
    'もちろん、句ごとで割ったほうがプログラムが見やすくなるので、基本的にはそうしたほうがいいです。
    'また、WHERE句の前か、FROM区のあとにスペースが無いと、「FROM句が正しくない」といったようなエラーになります。
    
    'VBE6.DLLのバージョンがOffice2000の中での古いバージョン(6.0.87.14)でも動きました。
    
    'Officeのバージョン、あるいは、VBE6や7のバージョンでもしかしたら動きや文字数制限などが異なるかもしれません。
    '事前のテストが必要です。
    
    '当方でやったテストは今のところ、
    'バージョン2000での Microsoft Query の結果表の「CommandText」とピボットの「CommandText」の違い
    '同じくバージョン2000での、Excelシートをソースにした場合と、mdbファイル（Accessのファイル）をソースにした場合の
    'SQLの書き方が異なる、という点、です。
        'Excelシートをのぞきに行って、ピボットのソースをMicrosoftQueryde作るケースの「CommandText」の書き換えは
        'まだテストしていません。
    'あと、バージョン2003での Exelシートをのぞきに行っての、
    'Microsoft Query の結果表のExcelシートだけの「CommandText」の書き換えの方法、のみです。
    'mdbはやってません。
    
    '各バージョンで、
    'ピボットのソースにMicrosoftQueryを使う場合で、ソースのモトがExcelファイルの場合とmdbの場合、
    'Microsoft Query での同じこと、をテストする必要があるかもしれません。
    'とくにバージョンの切れ目、2003から2007とか。VBE6.DLLからVBE7.DLLとか。
    
    'ピボットに関しては、ソースにMicrosoftQueryを使う場合（mdb、xls等ともに）しか、「CommandText」の書き換えはできません。
    '通常のExcelシートを直接見に行っている場合はSQLを使ってないみたいなので・・・
    '※「名前定義」した表はどうなるのかテストしていません。
    
    
    
    '全てのバージョンで試してはいませんが、ここでのテストがその他のバージョンの場合の参考にしていただければ幸いです。
    
End Sub
'
'
'

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

Sub test03()

Dim SqlStr01 As String

Dim SqlStr02 As String

Dim SqlStr03 As String

Dim SqlStr04 As String

Dim SqlStr05 As String

' Dim SqlStrALL As String '要らなかった。半角394文字以内のSQL文用。

Dim SQLArrey As Variant

' Debug.Print Worksheets("ソース2").QueryTables(1).Sql

' Debug.Print Worksheets("ソース2").QueryTables(1).CommandText

SqlStr01 = "SELECT "

SqlStr01 = SqlStr01 & "T顧客マスタ.顧客ID, "

SqlStr01 = SqlStr01 & "T顧客マスタ.名, "

SqlStr01 = SqlStr01 & "T顧客マスタ.姓, "

SqlStr01 = SqlStr01 & "T顧客マスタ.氏名, "

SqlStr01 = SqlStr01 & "T顧客マスタ.名フリガナ, "

SqlStr01 = SqlStr01 & "T顧客マスタ.姓フリガナ, "

SqlStr01 = SqlStr01 & "T顧客マスタ.国籍, "

SqlStr01 = SqlStr01 & "T顧客マスタ.性別, "

SqlStr01 = SqlStr01 & "T顧客マスタ.年齢, "

SqlStr01 = SqlStr01 & "T顧客マスタ.郵便番号, "

SqlStr01 = SqlStr01 & "T顧客マスタ.住所01, "

SqlStr01 = SqlStr01 & "T顧客マスタ.住所02, "

SqlStr01 = SqlStr01 & "T顧客マスタ.TEL, "

SqlStr01 = SqlStr01 & "T顧客マスタ.市, "

SqlStr01 = SqlStr01 & "T顧客マスタ.区, "

SqlStr01 = SqlStr01 & "T顧客マスタ.郡, "

SqlStr01 = SqlStr01 & "T顧客マスタ.町, "

SqlStr01 = SqlStr01 & "T顧客マスタ.字等, "

SqlStr01 = SqlStr01 & "T顧客マスタ.番地 , "

SqlStr02 = SqlStr02 & "T顧客マスタ.ビル名, "

SqlStr02 = SqlStr02 & "T顧客マスタ.棟など, "

SqlStr02 = SqlStr02 & "T顧客マスタ.部屋名, "

SqlStr02 = SqlStr02 & "T顧客マスタ.階, "

SqlStr02 = SqlStr02 & "T顧客マスタ.お仕事, "

SqlStr02 = SqlStr02 & "T顧客マスタ.趣味, "

SqlStr02 = SqlStr02 & "T顧客マスタ.愛読書カテゴリ, "

SqlStr02 = SqlStr02 & "T顧客マスタ.ネット通販, "

SqlStr02 = SqlStr02 & "T顧客マスタ.内ネットオークション, "

SqlStr02 = SqlStr02 & "T顧客マスタ.免許更新日, "

SqlStr02 = SqlStr02 & "T顧客マスタ.ダイエット, "

SqlStr02 = SqlStr02 & "T顧客マスタ.エステ, "

SqlStr02 = SqlStr02 & "T顧客マスタ.オススメ料理店, "

SqlStr02 = SqlStr02 & "T顧客マスタ.年収_ご夫婦込み, "

SqlStr02 = SqlStr02 & "T商品マスタ.商品ID, "

SqlStr03 = SqlStr03 & "T商品マスタ.メーカー名, "

SqlStr03 = SqlStr03 & "T商品マスタ.アイテム, "

SqlStr03 = SqlStr03 & "T商品マスタ.上代, "

SqlStr03 = SqlStr03 & "T商品マスタ.下代, "

SqlStr03 = SqlStr03 & "T商品マスタ.入荷数, "

SqlStr03 = SqlStr03 & "T商品マスタ.色系統, "

SqlStr03 = SqlStr03 & "T商品マスタ.ターゲット年齢層, "

SqlStr03 = SqlStr03 & "T商品マスタ.全商品ID表示フラグ, "

SqlStr03 = SqlStr03 & "T売上明細.売上ID, "

SqlStr03 = SqlStr03 & "T売上明細.売上日, "

SqlStr03 = SqlStr03 & "T売上明細.顧客ID, "

SqlStr03 = SqlStr03 & "T売上明細.お名前, "

SqlStr03 = SqlStr03 & "T売上明細.電話番号, "

SqlStr03 = SqlStr03 & "T売上明細.商品ID, "

SqlStr03 = SqlStr03 & "T売上明細.アイテム, "

SqlStr03 = SqlStr03 & "T売上明細.実売単価, "

SqlStr03 = SqlStr03 & "T売上明細.仕入単価, "

SqlStr03 = SqlStr03 & "T売上明細.売上点数, "

' SqlStr02 = SqlStr02 & "T売上明細.売上金額（1行当り）, "

' SqlStr02 = SqlStr02 & "T売上明細.仕入金額（1行当り）, "

' SqlStr02 = SqlStr02 & "T売上明細.レシートNo"

'句ごとじゃなくて、文字数ごとの配列のようなので、次のように、SELECT句とFROM句が混ざってもいいし,

'フィールドとフィールドのあいだや、各句のあいだに、半角スペースならいくつ入ってもOKです。

SqlStr04 = " T売上明細.仕入単価, T売上明細.売上点数, T売上明細.売上金額（1行当り）, T売上明細.仕入金額（1行当り）, T売上明細.レシートNo FROM `D:\pos\pos_ac`.T顧客マスタ T顧客マスタ, `D:\pos\pos_ac`.T商品マスタ T商品マスタ, `D:\pos\pos_ac`.T売上明細 T売上明細"

'配列の要素の１つが、値が空文字でもOKのようです。

SqlStr05 = ""

SqlStr06 = " WHERE T売上明細.顧客ID = T顧客マスタ.顧客ID AND T売上明細.商品ID = T商品マスタ.商品ID" 'ac

'ピボットの「CommandText」に代入する値の生成

SQLArrey = Array(SqlStr01, SqlStr02, SqlStr03, SqlStr04, SqlStr05, SqlStr06)

' Debug.Print SqlStr01

' Debug.Print SqlStr02

' Debug.Print SqlStr03

Worksheets("Sheet1").PivotTables(1).PivotCache.CommandText = SQLArrey

Worksheets("Sheet1").PivotTables(1).PivotCache.Refresh

'※ ピボットの「CommandText」の内容を変更することで、ピボットのフィールドリストの中の項目を、自動的に増やしたり減らしたり

' 内容変更することができます。

'結局、Microsoft Query の結果表の「CommandText」とピボットの「CommandText」は文字数制限？に違いがあるようです。

'とりあえずピボットの「CommandText」の場合は、句ごとに区切られているわけではなく、

'「一定の文字数」で区切られた文字列型の配列みたいです。

'その文字数は、1つの要素あたり、半角で394文字（？）くらいまでみたいです。

'（本当の文字数の制限の仕様はちゃんと調べてないので良く分かっていません。すみません。

' エラーが出る境目を調べたらだいたいそのくらいでした｡

' エラーのときは、「型が一致しません」、みたいなエラーになります。

' なので、SELECT句は300文字ずつくらいで区切ればいいようです。）

'※ Microsoft Query の結果表の「CommandText」は、かなりの単一の文字列型変数で、半角で394文字以上、

' テストでは1000文字近くでも大丈夫だったので、そのへんまでは多分大丈夫です。

' また、Microsoft Query の結果表の「CommandText」は、

'また、句と句のあいだや、フィールドとフィールドのあいだには、半角スペースがいくつあってもエラーになりませんでした。

'全角スペースは１個でもあるとエラーになるのでタイプミスしないように注意が必要です。

'半角で394文字以内に収まるSQL文なら、変数は、単一の文字列型でも文字列型の配列（Variant型）でもどちらでもいいみたいです。

'SQL文が半角で394文字を超えると、文字列型の配列（Variant型）でないとエラーになるようです。

'なお、配列の要素の数の制限（上限）はテストしてないのでわかりません。

'ただ、どこかの要素の値が「""（空文字）」であっても大丈夫です。

'また、SELECT句に、いくつの要素を使ってもかまわないし、

'FROM句やWHERE句などが、何番目の要素にこないといけない、という制限は無いようです。

'あと、ピボットのCommandTextの場合は、文字数で区切られた配列なので、

'要素の値が、SELECT句の一部とFROM句が混ざったものになっても大丈夫みたい。

'もちろん、句ごとで割ったほうがプログラムが見やすくなるので、基本的にはそうしたほうがいいです。

'また、WHERE句の前か、FROM区のあとにスペースが無いと、「FROM句が正しくない」といったようなエラーになります。

'VBE6.DLLのバージョンがOffice2000の中での古いバージョン(6.0.87.14)でも動きました。

'Officeのバージョン、あるいは、VBE6や7のバージョンでもしかしたら動きや文字数制限などが異なるかもしれません。

'事前のテストが必要です。

'当方でやったテストは今のところ、

'バージョン2000での Microsoft Query の結果表の「CommandText」とピボットの「CommandText」の違い

'同じくバージョン2000での、Excelシートをソースにした場合と、mdbファイル（Accessのファイル）をソースにした場合の

'SQLの書き方が異なる、という点、です。

'Excelシートをのぞきに行って、ピボットのソースをMicrosoftQueryde作るケースの「CommandText」の書き換えは

'まだテストしていません。

'あと、バージョン2003での Exelシートをのぞきに行っての、

'Microsoft Query の結果表のExcelシートだけの「CommandText」の書き換えの方法、のみです。

'mdbはやってません。

'各バージョンで、

'ピボットのソースにMicrosoftQueryを使う場合で、ソースのモトがExcelファイルの場合とmdbの場合、

'Microsoft Query での同じこと、をテストする必要があるかもしれません。

'とくにバージョンの切れ目、2003から2007とか。VBE6.DLLからVBE7.DLLとか。

'ピボットに関しては、ソースにMicrosoftQueryを使う場合（mdb、xls等ともに）しか、「CommandText」の書き換えはできません。

'通常のExcelシートを直接見に行っている場合はSQLを使ってないみたいなので・・・

'※「名前定義」した表はどうなるのかテストしていません。

'全てのバージョンで試してはいませんが、ここでのテストがその他のバージョンの場合の参考にしていただければ幸いです。

End Sub

投稿タグ: Accessの独学, ExcelVBA, Excelの独学, Excel操作の基礎, ビジネスパソコンの基礎, ビジネス一般常識, ピボットテーブル関連

MENU

関連記事

最近の投稿

カテゴリー

MENU

Excel2000でのピボットテーブルのPivotCache.CommandText（ピボットのソースのSQL文内容)のVBAでの自動書き換え

関連記事

最近の投稿

カテゴリー

タグ