クエリとテーブルの状態を調べる

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

これからのExcelユーザーが理解しておかなければならない知識・技術(2023年版)は、次の3つです。

  1. テーブル
  2. Power Query
  3. スピル

最後の「スピル」は、セル内に入力する数式の話です。もう少し正確に言えば、セル参照に関する画期的な仕組みです。現在のExcelには、この"スピルありき"で動作するメチャクチャ便利な関数が、続々と追加されていますので「そもそもスピルとはどういう仕組みなのか」を正しく理解していないと、そうした超絶便利な関数たちを使うことができません。さて、今回のテーマは「1.テーブル」と「2.Power Query」です。Power Queryは、Excelの使い方が激変するくらい強力な機能ですが、その実体を理解しているユーザーは少ないです。Power Queryは、Excelのために開発された"Excelの機能"では、ありません。Excelも使うことを許された、汎用的な技術です。どちらかと言えば、Power BIなどで積極的に使われています。「Power QueryはExcelの機能ではない」のですから、データベースの考え方が必要になります。しかし、Power Queryによって作られたデータをワークシート上に展開するとき、それは"テーブル"になります。テーブルは、Excelのために開発された"Excelの機能"です。Power Queryを使って外部のCSVなどをワークシート上に読み込むとき、これら「Excelの機能ではない」Power Queryと「Excelの機能である」テーブルを使うことになります。いずれにしても両者は、セルやワークシートなどのように、長年にわたりExcelユーザーが慣れ親しんだ仕組みではありません。こいつらをVBAで操作する情報は、ほとんどありませんので、ちょっとまとめてみましょう。

前提

ここでは「D:\Work\Sample.xlsm」というブック内にマクロを書くものとします。まぁ、これはそれほど重要ではありません。さて、その「D:\Work」フォルダは、次のようになっています。

これら3つのCSVデータを、Power QueryでSample.xlsmに取得しました。その結果が下図です。

3つのCSVともにPower Queryで取得したのですが、「売上2023-10.csv」と「売上2023-12.csv」はワークシート上にテーブルとして読み込み、「売上2023-11.csv」だけは読み込まずに"接続専用"にしてあります。さて、ワークシート上では、次のようになっています。

「売上2023-10.csv」は「Sheet1」に読み込みました。読み込んだテーブルの名前は「売上10月データ」です。さて、このSheet1には、もうひとつテーブルがあります。

テーブルの名前は「地域マスター」です。このテーブルは、Power Queryを使ったのではなく、手入力しました。つまり、どのクエリともリンクしていません。

最後の「売上2023-12.csv」は「Sheet2」に読み込みました。読み込んだテーブルの名前は「売上12月データ」です。では、このブックから、あれこれと情報を探ってみましょう。

ブック内のクエリを調べる

このブックには、次の3つのクエリが存在します。

ブック内の全クエリを調べるには、WorkbookオブジェクトのQueriesプロパティを使います。QueriesプロパティはQueriesコレクションを返します。QueriesコレクションのメンバーはWorkbookQueryオブジェクトです。Workbookと付いていますが、このWorkbookQueryオブジェクトが、いわゆるクエリです。

Sub Macro1()
    MsgBox ThisWorkbook.Queries.Count
End Sub

個々のクエリはWorkbookQueryオブジェクトですから、次のように情報を調べられます。

Sub Macro2()
    Dim i As Long, A As String
    For i = 1 To ThisWorkbook.Queries.Count
        A = A & ThisWorkbook.Queries(i).Name & vbCrLf
    Next i
    MsgBox A
End Sub

クエリの読み込み先

クエリがワークシート上に読み込まれているかどうかを調べるには、Connectionオブジェクトを使います。クエリとConnection(接続)の違いは、次のようにイメージしてください。

どの元データを取得して、そのデータに対してどんなデータベース的な処理をするか…などを表すのがクエリ(WorkbookQueryオブジェクト)です。クエリによって作成されたデータ(リスト)は、ワークシート上に読み込まれなければ利用できません。クエリによって作成されたデータを、どのワークシートの、どのセルに読み込むか…など、クエリとワークシートの接続に関することがConnectionオブジェクトです。

クエリによって作成されたデータが、どのワークシートの、どのセル(というかテーブル)に読み込まれているかを調べてみましょう。ここでは、1番目のクエリである「売上2023-10」を例に解説します。クエリ「売上2023-10」は次のように読み込まれています。シート名とテーブル名を確認してください。

ワークシート上に読み込まれているクエリでは、その読み込み先が、ConnectionオブジェクトのRangesプロパティに記録されています。

Sub Macro3()
    MsgBox ThisWorkbook.Connections("クエリ - 売上2023-10").Ranges(1).Address
End Sub

Rangeではなく、Rangesですので留意してください。Rangesとは何かについては、気にしないでください。ややこしい話ですし、今回の本筋ではありませんから、ここでは解説を割愛します。ちなみに「Connections("クエリ - 売上2023-10")」で指定した"クエリ - 売上2023-10"は、Connection(接続)の名前です。「売上2023-10」は1番目のクエリですから「Connections(1)」のようにインデックスで指定できそうなものですが、ちょっと注意が必要です。クエリが3つしかないからといって、Connectionも3つとは限りません。私の環境では、Connectionは4つ存在しました。

最初の「ThisWorkbookDataModel」ですが、これはもしかして、私のExcelにPower Pivotを入れているために存在しているのかもしれません。ちなみに、"クエリ - 売上2023-10"という名称は、Power Pivotの画面で確認できます。

また、Power Queryでクエリを保存するとき、[このデータをデータモデルに追加する]チェックボックスをオンにしておくと、さらに複数の、Excel上には表示されないConnection(接続)が追加されました。なので、今回のテーマである「このクエリは、どのセル(テーブル)に読み込まれているか」を調べるときは、「Connections("クエリ - 売上2023-10")」のようにクエリ名を指定した方が安全だと思います。クエリ名の先頭に"クエリ - "を付けるのを忘れないようにしてください。

さて、話を戻しましょう。「Connections("クエリ - 売上2023-10").Ranges(1)」で読み込まれているセルを取得できるのですから、シート名やテーブル名も簡単に分かります。

Sub Macro5()
    MsgBox ThisWorkbook.Connections("クエリ - 売上2023-10").Ranges(1).Parent.Name
End Sub


Sub Macro6()
    MsgBox ThisWorkbook.Connections("クエリ - 売上2023-10").Ranges(1).ListObject.Name
End Sub

問題は、"接続専用"になっているクエリ「売上2023-11」です。"接続専用"のクエリでは、Connectionの「Ranges.Count」が0になりますので、それで判定できます。

Sub Macro7()
    If ThisWorkbook.Connections("クエリ - 売上2023-11").Ranges.Count = 0 Then
        MsgBox "接続専用"
    Else
        MsgBox "読み込まれています"
    End If
End Sub

Ranges(1)ではなくRangesですから注意してください。Ranges(1)というのは、Ranges.Item(1)ですから、Ranges内で「1番目のメンバー」みたいな意味です。"接続専用"では、そもそもRanges内のメンバーが0なのですから、Ranges(1)はできません。

クエリで使用している元データ

これも、実務では知りたいところです。ブック内に複数のクエリが存在していて、それぞれが、どんな元データ(CSVなど)を使っているのか。簡単ではありませんが、これも調べる方法はあります。

クエリを表すWorkbookQueryオブジェクトには、Formulaというプロパティがあります。まずは、これを調べてみましょう。

Sub Macro8()
    MsgBox ThisWorkbook.Queries("売上2023-10").Formula
End Sub

ちょっと分かりにくいですが、これって、Power Queryエディタの「詳細エディター」で確認できる、クエリの内容です。

ここには、クエリで行った処理がすべて記載されているのですから、必要な情報を調べることも可能です。ちょっと、やってみましょうか。

Sub Macro9()
    Dim A As Variant
    A = Split(ThisWorkbook.Queries("売上2023-10").Formula, vbCrLf)
    MsgBox Split(A(1), """")(1)
End Sub

簡単に解説します。Formulaプロパティで次のようなテキストが取得できます。

let
    ソース = Csv.Document(File.Contents("D:\Work\売上2023-10.csv"),[Delimiter=",", Columns=4, Encoding=932, QuoteStyle=QuoteStyle.None]),
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"日付", type date}, {"名前", type text}, {"記号", type text}, {"数値", Int64.Type}})
in
    変更された型

それぞれの行末は、改行コードvbCrLfで改行されていますので、Split関数で分割します。結果は配列になり、A(0)が「let」で、A(1)が「 ソース = Csv.Document(File.Contents("D:\Work\売上2023-10.csv"),[Delimiter(略)」です。このA(1)を、2回目のSplit関数で分割します。区切り文字は「"(ダブルコーテーション)」です。その結果も配列になり、1番目の要素がファイル名です。このように、クエリでどんな元データを使っているかは、力わざで調べるしかないでしょう。もしかしたら「フォルダーから」でフォルダを指定しているかもしれませんし、別の元データを別クエリとして取得して、それらを「クエリの結合」しているかもしれません。調べたい情報はケースバイケースですから、何とかがんばってください。

テーブルからクエリを調べる

任意のクエリが、どのテーブルに読み込まれているかは、上記のように調べられます。では逆に、任意のテーブルが「どのクエリから読み込まれているか」を調べてみましょう。冒頭の「前提」で示したとおり、このブックには3つのテーブルが存在します。

テーブル名 シート クエリ
売上10月データ Sheet1 売上2023-10
地域マスター Sheet1 (なし)
売上12月データ Sheet2 売上2023-12

テーブルは、ListObjectで表されます。ブック全体のListObjectを調べたいのですが、残念ながらListObjectはブック(Workbookオブジェクト)ではなく、ワークシート(Worksheetオブジェクト)の配下です。したがって、ブック全体のテーブルを取得するには、ワークシートを1つずつ調べなければなりません。

Sub Macro10()
    Dim i As Long, j As Long, A As String
    For i = 1 To Sheets.Count
        For j = 1 To Sheets(i).ListObjects.Count
            A = A & Sheets(i).ListObjects(j).Name & vbCrLf
        Next j
    Next i
    MsgBox A
End Sub

クエリから読み込んだかどうか

まず、任意のテーブルが「クエリから読み込んだテーブル」なのか「手入力で作成したテーブル」かを判別してみましょう。両者の違いは、ListObjectのSourceTypeプロパティで分かります。SourceTypeプロパティの返り値が「xlSrcQuery」だったら、そのテーブルはクエリから読み込んだものであり、返り値が「xlSrcRange」なら手入力された(クエリとリンクされていない)テーブルです。

Sub Macro11()
    Dim i As Long, A As String
    For i = 1 To ActiveSheet.ListObjects.Count
        With ActiveSheet
            Select Case .ListObjects(i).SourceType
            Case xlSrcQuery
                A = A & .ListObjects(i).Name & "(クエリ)" & vbCrLf
            Case xlSrcRange
                A = A & .ListObjects(i).Name & "(手入力)" & vbCrLf
            End Select
        End With
    Next i
    MsgBox A
End Sub

SourceTypeプロパティは「xlSrcQuery」や「xlSrcRrange」だけでなく、次の値が格納可能です。

名前 値 説明
xlSrcExternal 0 外部データソース
xlSrcModel 4 PowerPivot モデル
xlSrcQuery 3 クエリ
xlSrcRange 1 範囲(手入力)
xlSrcXml 2 XML

「xlSrcQuery」かどうかの判定を、次のようにIf...Then...Elseステートメントで行うのは誤動作の原因ですから、上記のようにSelect Caseを使った方がいいでしょう。

Sub Macro11_2() ''悪い例
    Dim i As Long, A As String
    For i = 1 To ActiveSheet.ListObjects.Count
        With ActiveSheet
            If .ListObjects(i).SourceType = xlSrcQuery Then
                A = A & .ListObjects(i).Name & "(クエリ)" & vbCrLf
            Else
                A = A & .ListObjects(i).Name & "(手入力)" & vbCrLf
                ''ここには「範囲(手入力)だけでなく
                ''「外部データソース」や「Power Pivotモデル」
                ''なども含まれてしまう
            End If
        End With
    Next i
    MsgBox A
End Sub

読み込んだクエリの名前

テーブル「売上10月データ」は、クエリ「売上2023-10」から読み込みました。そのクエリ名を一発で調べるプロパティはありませんが、いくつかの方法で取得できます。

Sub Macro12()
    MsgBox ActiveSheet.ListObjects("売上10月データ").QueryTable.CommandText
End Sub

QueryTableは、テーブルがクエリから読み込まれたとき、そのクエリに関する情報が格納されます。そうした情報の中で、CommandTextプロパティにはSQL文が記録されます。あるいは、QueryTable内のSqlプロパティでも同じ情報を取得できます。どちらでもいいですが、ここからクエリ名だけを取り出せます。

Sub Macro13()
    Dim A As String
    A = ActiveSheet.ListObjects("売上10月データ").QueryTable.Sql
    MsgBox Replace(Split(A, "[")(1), "]", "")
End Sub

ほかにも方法があります。QueryTableのWorkbookConnectionプロパティが、読み込んでいるクエリを表しますので、その名前を調べられます。

Sub Macro14()
    MsgBox ActiveSheet.ListObjects("売上10月データ").QueryTable.WorkbookConnection.Name
End Sub

ただし、こちらはConnection(接続)で使用されている名前ですから、名前の先頭に「クエリ - 」が付きます。この文字列を除けば、クエリ名だけを抽出可能です。

Sub Macro15()
    Dim A As String
    A = ActiveSheet.ListObjects("売上10月データ").QueryTable.WorkbookConnection.Name
    MsgBox Mid(A, 7)
End Sub

Power Queryは、超強力な機能です。業務でCSVデータを使っているのなら、そのCSVデータをExcelで直接開くのではなく、Power Queryで読み込んだ方がいいです。そうしてPower Queryを多用していくと、Power Queryによって読み込んだテーブルが増えてきます。ケースによっては、複数のデータを読み込んだり、1つのワークシートに複数のテーブルが存在するかもしれません。そんなときは、どのクエリがどのテーブルとリンクしているのかを確認したいこともあるでしょう。VBAを使って、それらを確認するときには、上記の解説を参考にしてください。