★★★★★★★★★★★★★★★★Access2000VBA・Excel2000VBA独学～★超重要！！「■ Microsoft Query ＋ SQL」、「■ ODBCデータソース or ADO/DAO ＋ SQL ＋ VBA ＋QueryTableオブジェクト」、「■ PowerQueryやPowerPivotやリレーションシップ機能＋テーブル機能等々」の違い。本当に便利なのはどれなのか？～ | 無駄なお金と時間がない方・零細企業様・独立したい方・個人事業主の方・営業さん・経理さん・学生さん・就活生さん・小学生のお子様、中高生の子たちのための、　　　　「小さな会社の兼任なんちゃって社内SE・パソコン管理術・の基礎知識」

★★★★★★★★★★★★★★★★Access2000VBA・Excel2000VBA独学～★超重要！！「■ Microsoft Query ＋ SQL」、「■ ODBCデータソース or ADO/DAO ＋ SQL ＋ VBA ＋QueryTableオブジェクト」、「■ PowerQueryやPowerPivotやリレーションシップ機能＋テーブル機能等々」の違い。本当に便利なのはどれなのか？～
　　
※まだ書きかけです。すみません。
※間違ってたらすみません。
※メモ書きなので、自分でも意味不明な箇所も多いです。ごめんなさい。
　　
目次
< ※Shift＋TABキー、もしくは、Homeキー、Homeキー＋TAB数回、を押すと、目次付近に戻れます。　　
　　

★ はじめに

Excelでは、ワークシート関数以外に、「表と表をリレーション」させたり、「リレーションさせながら・同時に絞り込みや計算・並べ替え等々も同時に行う」のには、３つくらいの方法があると思います。

◎ SQLを直接使う方法として、以下の２つ。

(01)「Microsoft Query ＋ SQL」
（クリック・ドラッグ中心だけど内部的にSQLを自動生成。手動記述も可能。VBAなし。）

と、

(02)「ODBCデータソース or ADO/DAO ＋ SQL ＋ VBA ＋QueryTableオブジェクト」

の２つ。

※ADO/DAOでは「ODBC」接続ではなく「OLEDB」接続を使うことが多いと思います。
　また、ADO/DAOでは基本、「QueryTableオブジェクト」は使いません。
　「CopyFromRecordset」を使うことが多いと思います。

　　
　　
◎ SQLを直接は使わない方法として、以下の１つ。

(03)「PowerQueryやPowerPivotやリレーションシップ機能＋テーブル機能等々」
　　
　　
そのほかにもあるかもしれませんが、ぱっと思いつくだけでも上記の３つがあります。

この中で便利のいいのは結局のところどれなのでしょうか？
　　
　　
★ 結論（僕個人の独断と偏見）

僕の場合、クラウドを使ったり特別なことはしないので、以下の優先順位となります。

(02)「ODBCデータソース or ADO/DAO ＋ SQL ＋ VBA ＋QueryTableオブジェクト」
(01)「Microsoft Query ＋ SQL」
(03)「PowerQueryやPowerPivotやリレーションシップ機能＋テーブル機能等々」

(02)と(01)は多分多くのケースでこの順位だと思いますが、(03)については、「(03)でしかできないことがどうしても必要な人」ならば、(03)が一番上にくるかと思います。

僕の場合は、そんなことがいまだ無いので、(03)は面倒くさすぎて、使う気になれません。（SQLも使いにくいっぽいですし。）

パワーピボットのようなことも、QueryTableオブジェクト（MicrosoftQueryも含まれます）の結果の表に、Offset関数を使って名前定義すれば、バージョン2000（20年前のバージョン）でも同じことができますから。

ただ、ダッシュボード機能？みたいなのが無いので「絵や図での見える化」のときには劣るかもしれませんが。
でも、集計やリストアップだけならまったく同等・・・というか(01)(02)のほうが遥かに(03)よりも「格上」です。
また、「見える化」は「「絵や図での見える化」よりも、文字だけの見える化のほうが多いですから。

また、操作性のシンプルさやVBA連携・SQLでの効率化やコスパアップが可能かどうかで言えば、それも(03)はすごく劣ります。推測しますに、機能自体の構造が複雑すぎて、特にVBAでの「複数の集計やリストアップ」については効率が悪くて仕方がないと思います。
たとえば「(01)や(02)にて・SQLでやれは２分～１０分で済むことを、なんで(03)では、こんなにウィザード作って複雑化しているのでしょうか？」という感じです。さらには、複数の(03)の処理を、VBAで連続自動処理することについてはまるでできないですし・・・。（できるのかもしれませんが多分アホみたいに難しく、(02)で実現するほうが10倍効率的でコスパもいいです。(03)でなければならない、というのはかなり特殊な事情ではないかと思います。）

その他の理由はこれ以降に示します。
　　
　　
★ その前に「重要な注意事項！！」

(01)、(02)、(03)、は、Excelで標準的に良く使われる機能ではないため「Web上にも文献が少ない」です。

そのため、『 https://www.shegolab.jp/entry/excel-tips-data-combination 』のようなWeb記事が多く存在します。

この記事は正しいです。
そして、記事を書いている方は、私の数十倍、SQLにもVBAにも詳しい人です。

でも、このような記事を信用「し過ぎ」ないでください。

たとえばこのWeb記事は、(01)や(02)が「将来は無くなるかもしれない」と役に立たないかのような記述をしていらしゃいますが、それは半分は確かにそのとおりです。「将来は無くなるかもしれない」です。でも、「役に立たないものではありません」。

例えば、(01)と(02)は、さらに例えば、「Windows10」が「最後のWindows」として今後も存在するうちは、VirtualBOXやVMWareなどの仮想マシン管理ソフトを使って、古いExcelを使い続ける方法もあります。

例えば「Excel2000（数千円）」なら「VMWare＋XPorWin2000」などで今も使えますし、「Excel2016」などなら、もちろんそのままWin10でネイティブに使い続けられます。

といいますか、(01)(02)は、「VMWare＋XPorWin2000＋Excelの古いバージョン」なんていう面倒くさい方法をとってでも、その利用価値が、「非常に」高いです。

そもそも、「VMWare＋XPorWin2000」自体も、仮想マシンを一個作っておけば、他のマシンに「それをコピペするだけ」で使えます。そして仮想マシンはLANにも親PCとともに参加させることができます。そのなかにExcelが入っていれば、もちろんExcel込みでコピペでき・仮想マシンのデータをLAN経由でも利用できる・・・ということになります。それを面倒と思うかラクと思うかは個人差があるかもしれませんが・・・。
前のマシンから仮想マシンを消してから作業すれば、ライセンス的にも問題なく「仮想マシンを新マシンにコピペ」するだけで、いつまででも「Excel2000」を使い続けられます。
そしてSQL Server、MySQL、オラクルのSQLの学習がわりにも使えます。
（VMWereやVirtualBOXのバージョンがWin10の大型バージョンアップに追い付かなくなったらそれもそのとき考えればOKです。）

もっと言うと、(01)(02)は、Win10や他のOSをネットから切り離して、大型アップデートを止めて「スタンドアロン環境・あるいは閉じたLAN」ででも、「使い続ける価値のある機能」です。

だって、OSのライセンスさえあれば、仮想マシン同士でもLANが組めますし、仮想マシンコピペするだけで、複数のパソコンで使えるし、手間がいらないんですもの。

事実、今の時点での最新バージョンの2019でも使えますし、SQL Server や MySQLにも接続できる・しているユーザーが居ることも考えますと、「まだしばらくは消えない」、と思います。
もちろん、いつかは消える可能性はありますが、前述したように「VirtualBOXやVMWareなどの仮想マシン管理ソフト」を使う方法も利用すれば、「あと20年はいけそう」な感じです。

そのあいだに、今のPoweQueryなどがどう進化するか、参考文献が増えるか、ですね。

逆に言いますと、(03)はそこまでする「魅力が無い」と、そういう機能でもある、と言えます。（その理由も詳しくは後述します。）

いずれにしても、(01)や(02)のような、
「あと20年は使えそうな機能」であり・かつ・「仮想マシンに入れてでも使い続ける価値のある機能」については、前述のURLのような記事は、信用することはしてもいいですが、「し過ぎ」てはいけないと思います。

ちなみにですが、この記事の２ページ目の、『複数リストから全ての組み合わせデータを作りたい（2/2）』の記事の終わりのほうに書いてあるコードと同じ結果を得ることができる、VBAプログラムを２つ、ご紹介しておきます。
両方ともバージョン2010で作りました。

ただしこちらの『複数リストから全ての組み合わせデータを作りたい（2/2）』のコードは1枚のシート上に複数の表があっても選択して処理できるように高度になっています。
また、パワークエリを直接いじってクロス結合しているのではなく、ExcelVBAのRangeオブジェクトなどでループなどでゴリゴリにクロス結合している・・・・っぽいです。
なので長いのですが・・・。
また、「パワークエリを直接VBAで動かす」様子もわかりません。

ただ、一般的にはそこまでしなくても普通にMicrosoftQueryを使う方法をマクロ記録しただけでも同様のクロス結合が、自動化は可能です。どちらを使うかは利用シーン次第ですが・・・。（ただ、このサンプルのように範囲を選択すると大きな表の場合は「遅くなる」とは思います。）
そして、「このページの ” CrossJoin ” プロシージャの長さと内容のわかりやすさ・ループ処理の数」と、後述のコードのそれを見比べてみてください。基本、ループ処理の個数が増えればるほど、セルを参照すればするほど、プログラムの動作速度は遅くなります。

いずれにしましても、以降の２つのサンプルコードのいずれかのコードで、アクティブになっているシートに、前述の『複数リストから全ての組み合わせデータを作りたい（2/2）』の記事と同じ、「クロス結合」の結果が表示されます。
このサンプルでは「D:\1\5.xlsx」というExcelファイルに記録された、Sheet1～Sheet3（つまり、具、カレー、辛さの3つの表）を「クロス結合」するサンプルです。

ところで、このコードは、実は、「マクロの記録」機能で作りました。
特に１つ目のサンプルコードはそれを、SQL文（SELECTのところの1行）だけを短く書き換えただけのものです。
「SELECT 具, カレー, 辛さ FROM ・・・・・・・・・・・・」と1行書くだけです。MicrosoftQueryやExcelのSQLの場合、複数の表を「あえて結合しない」と「クロス結合」になるようです。
なお、内部結合（VLOOKUP関数などと同じ）、外部結合、集計関数による集計、縦結合（マージ）なども、同じようなパターンでやれます（これらの場合は「結合」というものをして）。
以下のサンプルは、「テーブル機能」を併用するものと（１つめ）、しないもの（２つめ）が作ってあって、いずれも、アクティブシートに、「具、カレー、辛さ」の３つの表がクロス結合された結果が表示されます。
『マクロの自動記録でも簡単にSQLを使ったコードが自動生成される』、『しかも短くて作り変えがラク』というところに注目してください。

なお、「Microsoft Query」は、実はExcelからは独立した機能です（Excelが開いてなくても単独使用が可能です）。が、SQL実行の結果の表は、「QeryTableオブジェクト」に返ってくるように、Excel自身が指示を出しているっぽいです。
なので、「Microsoft Query」と「QueryTableオブジェクト操作」は、操作は異なりますが、結果は「QueryTableオブジェクト」に出力される、「同等なもの」と理解して良さそうです。（バージョン2007以降は、ExcelのMicrosoft Queryは、テーブル機能の中にQueryTableオブジェクトを埋め込みます。＝１つめのサンプル）

では、以下、２つのサンプルコードです。

サンプル中のSQL文の、
「"SELECT 具, カレー, 辛さ FROM `D:\1\5.xlsx`.`Sheet1$`, `Sheet2$`, `Sheet3$`" 」
は、
「"SELECT * FROM `Sheet1$`, `Sheet2$`, `Sheet3$`"」
と、
短く書いてもOKです。より、簡単になると思います。

'
'

'###############################################################################
'「テーブル機能」の中に、QueryTableオブジェクトを埋め込んでクロス結合を表示するサンプル。
'「MicrosoftQuery」での結合をマクロの記録機能で記録して、<a href="https://euc-access-excel-db.com/tips/ct90_yougo/yougo-sql01" target="_blank" rel="noopener noreferrer">SQL</a>を少し短くしただけの
'コードです。
'###############################################################################

Sub MSQryCrossJoinWithTable01()

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=Excel Files;DBQ=D:\1\5.xlsx;DefaultDir=D:\1;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" _
        , Destination:=ActiveSheet.Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT 具, カレー, 辛さ FROM `D:\1\5.xlsx`.`Sheet1$`, `Sheet2$`, `Sheet3$`" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "テーブル_Excel_Files_からのクエリ4"
        .Refresh BackgroundQuery:=False
    End With
End Sub


'###############################################################################
'「テーブル機能」は使わずに、つまり、QueryTableオブジェクトを埋め込まないパターン。
'そして、クロス結合するサンプル。
'前述の１つ目と同様に、「Microsoft Query」での結合をマクロの記録機能で記録して、<a href="https://euc-access-excel-db.com/tips/ct90_yougo/yougo-sql01" target="_blank" rel="noopener noreferrer">SQL</a>を少し
'短くしただけのコードを、「テーブル機能」に埋め込まないように2行ほど書き換えたもの。
'これは「QueryTableオブジェクト」のみで結果を表示しています。
'なお、「Microsoft Query」は、実はExcelからは独立した機能ですが、<a href="https://euc-access-excel-db.com/tips/ct90_yougo/yougo-sql01" target="_blank" rel="noopener noreferrer">SQL</a>実行の結果の表は、
'「QeryTableオブジェクト」に返ってくるように、Excel自身が指示を出しているっぽいです。
'なので、「Microsoft Query」と「QueryTableオブジェクト操作」は、機能は操作は異なりますが、
'結果は「QueryTableオブジェクト」に出力される、「同等なもの」と理解して良さそうです。
'###############################################################################

Sub MSQryOnlyCrossJoinNonTable01()

    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Excel Files;DBQ=D:\1\5.xlsx;DefaultDir=D:\1;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" _
        , Destination:=Range("$A$1"))
        .CommandText = Array( _
        "SELECT 具, カレー, 辛さ FROM `D:\1\5.xlsx`.`Sheet1$`, `Sheet2$`, `Sheet3$`" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
'        .ListObject.DisplayName = "テーブル_Excel_Files_からのクエリ4"
             '↑この2つ目のサンプルコードは新しいExcelでも もちろん動きますが、
             ' もともとは古いバージョンのExcelのためのものです。
             ' しかしこの行だけは新しいExcelのためのものです。
             ' 古いExcelは、最初から「テーブル機能」が使えないので
             ' 古いExcelでこの2つ目のサンプルを動かすとこの行はエラーになります。
             ' なので、コメントアウトして、代わりに以下の行で名前を設定しています。
        .Name = "テーブル_Excel_Files_からのクエリ6"
        .Refresh BackgroundQuery:=False
    End With
End Sub

'
'

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

'「テーブル機能」の中に、QueryTableオブジェクトを埋め込んでクロス結合を表示するサンプル。

'「MicrosoftQuery」での結合をマクロの記録機能で記録して、<a href="https://euc-access-excel-db.com/tips/ct90_yougo/yougo-sql01" target="_blank" rel="noopener noreferrer">SQL</a>を少し短くしただけの

'コードです。

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

Sub MSQryCrossJoinWithTable01()

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _

"ODBC;DSN=Excel Files;DBQ=D:\1\5.xlsx;DefaultDir=D:\1;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" _

, Destination:=ActiveSheet.Range("$A$1")).QueryTable

.CommandText = Array( _

"SELECT 具, カレー, 辛さ FROM `D:\1\5.xlsx`.`Sheet1$`, `Sheet2$`, `Sheet3$`" _

)

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

.ListObject.DisplayName = "テーブル_Excel_Files_からのクエリ4"

.Refresh BackgroundQuery:=False

End With

End Sub

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

'「テーブル機能」は使わずに、つまり、QueryTableオブジェクトを埋め込まないパターン。

'そして、クロス結合するサンプル。

'前述の１つ目と同様に、「Microsoft Query」での結合をマクロの記録機能で記録して、<a href="https://euc-access-excel-db.com/tips/ct90_yougo/yougo-sql01" target="_blank" rel="noopener noreferrer">SQL</a>を少し

'短くしただけのコードを、「テーブル機能」に埋め込まないように2行ほど書き換えたもの。

'これは「QueryTableオブジェクト」のみで結果を表示しています。

'なお、「Microsoft Query」は、実はExcelからは独立した機能ですが、<a href="https://euc-access-excel-db.com/tips/ct90_yougo/yougo-sql01" target="_blank" rel="noopener noreferrer">SQL</a>実行の結果の表は、

'「QeryTableオブジェクト」に返ってくるように、Excel自身が指示を出しているっぽいです。

'なので、「Microsoft Query」と「QueryTableオブジェクト操作」は、機能は操作は異なりますが、

'結果は「QueryTableオブジェクト」に出力される、「同等なもの」と理解して良さそうです。

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

Sub MSQryOnlyCrossJoinNonTable01()

With ActiveSheet.QueryTables.Add(Connection:= _

"ODBC;DSN=Excel Files;DBQ=D:\1\5.xlsx;DefaultDir=D:\1;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" _

, Destination:=Range("$A$1"))

.CommandText = Array( _

"SELECT 具, カレー, 辛さ FROM `D:\1\5.xlsx`.`Sheet1$`, `Sheet2$`, `Sheet3$`" _

)

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

' .ListObject.DisplayName = "テーブル_Excel_Files_からのクエリ4"

'↑この2つ目のサンプルコードは新しいExcelでももちろん動きますが、

' もともとは古いバージョンのExcelのためのものです。

' しかしこの行だけは新しいExcelのためのものです。

' 古いExcelは、最初から「テーブル機能」が使えないので

' 古いExcelでこの2つ目のサンプルを動かすとこの行はエラーになります。

' なので、コメントアウトして、代わりに以下の行で名前を設定しています。

.Name = "テーブル_Excel_Files_からのクエリ6"

.Refresh BackgroundQuery:=False

End With

End Sub

※2つ目のサンプルの（自動書き込みされる）細かい設定とダイアログボックスの対応状況の図　　（『Excelで使うMySQL データ分析編』のP272の図を引用しました。）

　　
　　
★ 2000での吸い込みプログラムを関数化したもの（xls拡張子）

基本、「SQL内容・SQL文」の部分のコードを書き換えるだけでも色々できます。

※2010でも動きました。自ファイルも吸い込めます。何段階でも。そして動作が速いです。
ただ、自ファイルを読み込んだ場合は、そのQueryTableオブジェクトは、MicrosoftQueryでは「認識しない」という感じのエラーになってSQLの書き換え等ができません。2000も2010も同じようです。
2000では、MicrosoftQueryでなら多段階にネストすると、エラーが消えることは消えます。
でも、結果の表示の速度が超遅いです。
なので、多段階にするときは（VBA以外は不可に近いため）、MicrosoftQueryやSQLに慣れるまでは1ファイル単位で多段階にしたほうがいいのかもしれません。

'
'
Option Explicit

'###############################################################################
'QueryTableオブジェクトを自動作成するプログラムの一例
'他のExcelファイルや自ファイルからの、SQLを使ってのデータの吸い込み
'###############################################################################

Sub test01()
 
    Dim s_SqlStr01jj        As String
    Dim s_ChkQTobjNm01jj    As String

    '★設定部
    
    s_ChkQTobjNm01jj = "QTSht5_To_5xlsht1"
       '「重複を調べたい＝付けたい」
       '「QueryTableオブジェクトの名前」の設定
    
    
    'SQL文の設定
    s_SqlStr01jj = "SELECT * FROM `sheet1$`"
 
        '※テーブル名は上記のように「`」で囲むのもOKですし、
        '  「角カッコ」で囲むのもOKです。
        '   角カッコで囲むと『 [Sheet1$] 』となります。
        '   フィールド名に別名を付けたときは、別名に角カッコを使うほうが
        '   結果の表のフィールド名に「’」が付かないので便利かもしれません。
    

    
    
    '★ チェック部
    
    '指定したブック内に、指定し名前を含むQueryTableオブジェクトが
    '既に存在していないかどうかをチェック。
    '在ったら中断。
    If QTSonzaiChk01(ThisWorkbook, s_ChkQTobjNm01jj) = 1 Then
        Exit Sub
    Else
    
    End If
    
 
 
 
    '★ 実動部
    
    'SQLの実行＝SQLでの吸込み
    Call MSQryOnlMakeByODBCFunc001("D:\1\5.xls", _
                                   "D:\1", _
                                   s_SqlStr01jj, _
                                   s_ChkQTobjNm01jj, _
                                   ActiveSheet, _
                                   "$A$1")
 
' 呼び出し方法
'    Call MSQryOnlMakeByODBCFunc001("読みに行きたいファイルのフルパス",
'                                   "そのファイルの在るフォルダのパス",
'                                   SQL文,
'                                   結果表（QueryTableオブジェクト）につけたい名前,
'                                   結果を出力したい「オブジェクトとしての」シート.
'                                   結果表を出力するセルのアドレス（表の一番左上隅のセルの。)
'


End Sub



'###############################################################################
'「テーブル機能」は使わずに、つまり、QueryTableオブジェクトを埋め込まないパターン。
'そして、他の「XLS、XLSM、XLSX」のシートなどをｓ吸い込む関数のサンプル。
'
'前述の１つ目と同様に、「Microsoft Query」での結合をマクロの記録機能で記録して、<a href="https://euc-access-excel-db.com/tips/ct90_yougo/yougo-sql01" target="_blank" rel="noopener noreferrer">SQL</a>を少し
'短くしただけのコードを、「テーブル機能」に埋め込まないように2行ほど書き換えたもの、を、
'関数化したもの。
'
'これは「QueryTableオブジェクト」のみで結果を表示しています。
'なお、「Microsoft Query」は、実はExcelからは独立した機能ですが、<a href="https://euc-access-excel-db.com/tips/ct90_yougo/yougo-sql01" target="_blank" rel="noopener noreferrer">SQL</a>実行の結果の表は、
'「QeryTableオブジェクト」に返ってくるように、Excel自身が指示を出しているっぽいです。
'なので、「Microsoft Query」と「QueryTableオブジェクト操作」は、機能は操作は異なりますが、
'結果は「QueryTableオブジェクト」に出力される、「同等なもの」と理解して良さそうです。
'###############################################################################
 
Sub MSQryOnlMakeByODBCFunc001(s_TrgFNm01jj As String, _
                              s_TrgFPath01jj As String, _
                              s_Sql01jj As String, _
                              s_QTNm01jj As String, _
                              o_TrgWSht01jj As Worksheet)
 
   
'    Dim s_TrgFNm01jj            As String
'    Dim s_TrgFPath01jj          As String
'    Dim s_Sql01jj               As String
'
'
    Dim o_QT01jj                As QueryTable
'    Dim s_QTNm01jj              As String
    
    
'    s_TrgFNm01jj = "D:\1\クエリのネストのテスト.xls"
'    s_TrgFPath01jj = "D:\1"
'    s_Sql01jj = "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `Sheet1$`"
'    s_QTNm01jj = "QTSheet5"
    
    
    Set o_QT01jj = o_TrgWSht01jj.QueryTables _
                      .Add(Connection:= _
                           "ODBC;DSN=Excel Files" & _
                           ";DBQ=" & s_TrgFNm01jj & _
                           ";DefaultDir=" & s_TrgFPath01jj & _
                           ";DriverId=1046" & _
                           ";MaxBufferSize=2048" & _
                           ";PageTimeout=5" & _
                           ";" _
                           , Destination:=Range("$A$1"))
                           
        
    With o_QT01jj
        
        .CommandText = Array(s_Sql01jj)
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
'        .ListObject.DisplayName = "テーブル_Excel_Files_からのクエリ4"
             '↑この2つ目のサンプルコードは新しいExcelでも もちろん動きますが、
             ' もともとは古いバージョンのExcelのためのものです。
             ' しかしこの行だけは新しいExcelのためのものです。
             ' 古いExcelは、最初から「テーブル機能」が使えないので
             ' 古いExcelでこの2つ目のサンプルを動かすとこの行はエラーになります。
             ' なので、コメントアウトして、代わりに以下の行で名前を設定しています。
        .Name = s_QTNm01jj
        .Refresh BackgroundQuery:=False
        
    End With
    
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

Option Explicit

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

'QueryTableオブジェクトを自動作成するプログラムの一例

'他のExcelファイルや自ファイルからの、SQLを使ってのデータの吸い込み

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

Sub test01()

Dim s_SqlStr01jj As String

Dim s_ChkQTobjNm01jj As String

'★設定部

s_ChkQTobjNm01jj = "QTSht5_To_5xlsht1"

'「重複を調べたい＝付けたい」

'「QueryTableオブジェクトの名前」の設定

'SQL文の設定

s_SqlStr01jj = "SELECT * FROM `sheet1$`"

'※テーブル名は上記のように「`」で囲むのもOKですし、

' 「角カッコ」で囲むのもOKです。

' 角カッコで囲むと『 [Sheet1$] 』となります。

' フィールド名に別名を付けたときは、別名に角カッコを使うほうが

' 結果の表のフィールド名に「’」が付かないので便利かもしれません。

'★ チェック部

'指定したブック内に、指定し名前を含むQueryTableオブジェクトが

'既に存在していないかどうかをチェック。

'在ったら中断。

If QTSonzaiChk01(ThisWorkbook, s_ChkQTobjNm01jj) = 1 Then

Exit Sub

Else

End If

'★ 実動部

'SQLの実行＝SQLでの吸込み

Call MSQryOnlMakeByODBCFunc001("D:\1\5.xls", _

"D:\1", _

s_SqlStr01jj, _

s_ChkQTobjNm01jj, _

ActiveSheet, _

"$A$1")

' 呼び出し方法

' Call MSQryOnlMakeByODBCFunc001("読みに行きたいファイルのフルパス",

' "そのファイルの在るフォルダのパス",

' SQL文,

' 結果表（QueryTableオブジェクト）につけたい名前,

' 結果を出力したい「オブジェクトとしての」シート.

' 結果表を出力するセルのアドレス（表の一番左上隅のセルの。)

End Sub

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

'「テーブル機能」は使わずに、つまり、QueryTableオブジェクトを埋め込まないパターン。

'そして、他の「XLS、XLSM、XLSX」のシートなどをｓ吸い込む関数のサンプル。

'短くしただけのコードを、「テーブル機能」に埋め込まないように2行ほど書き換えたもの、を、

'関数化したもの。

'これは「QueryTableオブジェクト」のみで結果を表示しています。

'「QeryTableオブジェクト」に返ってくるように、Excel自身が指示を出しているっぽいです。

'なので、「Microsoft Query」と「QueryTableオブジェクト操作」は、機能は操作は異なりますが、

'結果は「QueryTableオブジェクト」に出力される、「同等なもの」と理解して良さそうです。

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

Sub MSQryOnlMakeByODBCFunc001(s_TrgFNm01jj As String, _

s_TrgFPath01jj As String, _

s_Sql01jj As String, _

s_QTNm01jj As String, _

o_TrgWSht01jj As Worksheet)

' Dim s_TrgFNm01jj As String

' Dim s_TrgFPath01jj As String

' Dim s_Sql01jj As String

Dim o_QT01jj As QueryTable

' Dim s_QTNm01jj As String

' s_TrgFNm01jj = "D:\1\クエリのネストのテスト.xls"

' s_TrgFPath01jj = "D:\1"

' s_Sql01jj = "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `Sheet1$`"

' s_QTNm01jj = "QTSheet5"

Set o_QT01jj = o_TrgWSht01jj.QueryTables _

.Add(Connection:= _

"ODBC;DSN=Excel Files" & _

";DBQ=" & s_TrgFNm01jj & _

";DefaultDir=" & s_TrgFPath01jj & _

";DriverId=1046" & _

";MaxBufferSize=2048" & _

";PageTimeout=5" & _

";" _

, Destination:=Range("$A$1"))

With o_QT01jj

.CommandText = Array(s_Sql01jj)

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

' .ListObject.DisplayName = "テーブル_Excel_Files_からのクエリ4"

'↑この2つ目のサンプルコードは新しいExcelでももちろん動きますが、

' もともとは古いバージョンのExcelのためのものです。

' しかしこの行だけは新しいExcelのためのものです。

' 古いExcelは、最初から「テーブル機能」が使えないので

' 古いExcelでこの2つ目のサンプルを動かすとこの行はエラーになります。

' なので、コメントアウトして、代わりに以下の行で名前を設定しています。

.Name = s_QTNm01jj

.Refresh BackgroundQuery:=False

End With

End Sub

　　
　　
　　
★ ↓ 上↑の20000での吸い込みプログラムを関数化したもの・より詳しく版。
（こちらのほうが実用向きかも？です。セルの値を使ったパラメータクエリの自動作成についても書いてあります。）

既存のQueryTableオブジェクトと同じ名前（近い名前）のものがないかチェックしてから、無ければQueryTableオブジェクトを自動作成する版です。

『 ★★★★★★Access2000VBA・Excel2000VBA独学～★★超重要！！！ODBCでQueryTableオブジェクトを作成するサンプルコード）。多分、2000以降の全バージョンで使えると思います。～』

読みこみたいファイルの指定、吸い込みたいファイルの指定等々、SQL文、などをプログラムの上の方でいじれます。
なので、すぐに他のファイルの内容を、「SQLによってリレーションや集計関数などを駆使した複雑な条件での集計や絞込み・リストアップ」をしながら、吸い込めます。

少し作り変えて、アドインファイル（xlam）として登録すれば、どのファイルにでも、すぐに他のふファイルの内容が吸い込めるようになると思います。 ←勘違いでした。すみませんでした。アドインにしても使えませんでした。ガックリ。Accessとは違うようです。Excelは外部のxls*のプロシージャを簡単には「Call」ができないので使いづらいですね。

（※別手法ですが、ODBCデータソースを作れば、テキストファイルの内容SQLを使いながら読み込めます。リンク先の記事に、さらにその件のリンクがありますのでそちらをご参照ください。）

　　
　　
　　
★ (03) の「PowerQueryやPowerPivotやリレーションシップ機能＋テーブル機能等々」が「僕にとっては」ダメな理由

もちろん、「PowerQueryやPowerPivotやリレーションシップ機能＋テーブル機能等々」が「まったくダメ」、ということはありません。

生き生きと使える場面も必ずあるはずです。
もしかしたらクラウド対応とか、より、簡単に縦結合・横結合、集計、その他、ができる、ということもあるかもしれません。
（例えば、パワークエリの「データの取得と変換（列のピボット解除）」機能は便利です！クロス集計表をリスト表に直すのに。）

そもそも「ワークシート関数やSQLを学ばなくても・VLOOKUP関数の持つ欠点のような事を気にせずとも・表の結合ができる」ということは、もうすでにそれだけでも、また、「それが2つの表のあいだだけ」だとしても、私たちド素人・末端のユーザーにとっては価値が高いです。

でも、「SQLの学習」は「Excelを集計ソフトとして使う以上は」、VBAの学習よりもある意味重要です。
そのような意味を考えますと、どうしても「PowerQueryやPowerPivotやリレーションシップ機能＋テーブル機能等々」「だけ」だと弱いのです。なので、あえて、それらの機能は「ダメ」と書かせて頂いてしまいました。
私の思い上がり、どうかお許しください。

では、以降に、その「ダメ」な理由を書きます。
（はじめてパワーピボットやパワークエリを触ったときの感想メモ書きがベースになっているので、言葉遣いが悪いところがあります。どうかお許しください。ただ、当時、そう感じてしまったのも事実です。そのことも少し、イメージしてみてください。肯定しなくてもいいので、イメージだけでもしてみてください。）

(a)
「SQL」でできることをGUIでやろうとしている感じなのに、それを隠そうとしており、使い勝手が悪すぎる気がします。同じように「SQLでできることなら(01)か(02)のほうがやりやすいですし、おまけに、「Access」や「SQLそのもの」自体の学習もできます。つまり（多少の方言はあるものの）、SQL Server、オラクル、MySQLなどの学習を「意識せずとも自然・かつ・自動的におこなっている」ことにもなります。SQLは「Googleスプレッドシート」でも使えますし。
なので、(01)や(02)のほうが(03)よりも断然・利用価値が高い気がします。
(03)は「他のソフト（特にデータベースソフト）にはまったく使えず、 ” Excelでしか ” 利用できない」ので、「仕事として使う・今後のステップアップも考えて使う」には、基本的には、すごく効率が悪いです。

(b)
そもそもですが、(01)と(02)は多分(03)と同様にSQL Server やMySQLなどにも接続できたと思います。（古いバージョンではできていました。多分今のバージョンでもできると思います。）
今さら(03)は別に要らないかな・・・という面もあります。
特に僕はExcelを高度に使ってはいないので・・・。

(d)
例えば「Power Query」という機能ですと3枚以上の表を一度にリレーションできません。
できるのかもしれませんが、デフォルトではできないもようです。
（単に僕がバカでやりかたを見つけられなかっただけの可能性が高いですが）
いずれにしましても、(01)や(02)なら何枚の表でも「一発で」リレーションできます。

(e)
(01)(02)はAccessのクエリの画面とほぼ同じで使いやすいです。SQL Serverなどのビューの画面にも似ていると思います。
(03)は似てないので「なんとなく使う」ということができません。当然、SQL Serverなどの練習にもなりません。

(f)
複数の表を扱いたい時、個々の表がいちいち「シートとして」読み込まれてしまって動きが「遅い」し面倒くさいです。（結果の表1枚・1発だけで終われないのです。）

(g)
Excelのバージョンごとに使える使えないがあるので、機能を統一しづらいです。
でも、(01)(02)なら、2000～2019までの全バージョンで使えます。特に(02)がバージョン間の差異もほぼ無くて便利です。
(01)は2007以降はMicrosoftQueryの結果がテーブル機能の中に埋め込まれてしまいますが（そしてそのせいなのか、何重かにネスト的に使えなかったり自ファイルがエラーで覗けませんが）、しかし、(02)はそれがありません。（バージョンも関係なく。）

(02)は全バージョンで、どれだけでもネスト的に使えますし、自ファイルを覗きに行ってもエラーになりません。
そして高速に動きます。

でも、かといって(01)が使えないかというとそうでもなく、便利に使える場面も少なくないです。
(02)をやるときのSQL文の生成に便利ですし。
ちょっとしたことならいちいち(02)を使うまでもなく、ドラッグとクリック中心に(01)で完結します。
リレーションを切り替えての操作をすばやく、数種類、切り替えたいときも(01)のほうが便利です。

(03)はそのどれもが、「できません。」

(h)
「SQL」でできることをGUIでやろうとしているのに、SQLが、基本、使えないです。
使える方法が残されているようですけど、それを使ってしまうと元々の機能が制限されるらしいです。
詳しくはわからないですけど、そんなに面倒くさいなら、最初から(01)か(02)を使った方がはるかにラクです。

(i)
そして何といっても「遅い」。（気がします。）

(j)
また、VBAでの自動化ができない（というかできるとは思うんですけど文献がないです。また、QueryTableオブジェクトのほうが遥かに簡単でしかも動作が速いです。結果でできた表のネスト的なことやすべての表の自動更新もQueryTableオブジェクトのほうがラクです。）

(k)
覗きに行っているファイルの「データの書き換え」が「できない」です。
閉じたままはもちろん、開いたままでも。
逆に、(01)と(02)は、つまりはMicrosoft Queryの画面からも、VBAコードからも、「SQL」にて、相手ファイルが開いたままはもちろん、閉じたままでもデータの書き換えができます。

MENU

最近の投稿

カテゴリー

MENU

最近の投稿

カテゴリー

タグ