WEB上の表をExcelに読み込む - QueryTable

WEBページ内にある表(テーブル)を、ワークシートに書き出すコードを紹介します。

↓こちらの表を取り込んでみましょう。

品番	品物	種別	価格
1-1	みかん	くだもの	100
1-2	りんご	くだもの	200
1-3	いちご	くだもの	300

WEB読込にはいろいろな方法がありますが、ここでは手軽に実行できる、
「QueryTableオブジェクト」を使用する方法を紹介します。

QueryTablesプロパティでWEBページを読み込む

ソースコード

Sub WEBページの表をシートに出力する()
    
    Dim 対象URL As String
    対象URL = "https://www.limecode.jp/entry/utility/querytables-import-web-table"
    
    Dim 出力先シート As Worksheet
    Set 出力先シート = Worksheets.Add ' 今回は新規シートに読み込み
 
    With 出力先シート.QueryTables.Add(Connection:="URL;" & 対象URL _
                                                    , Destination:=出力先シート.Range("A1"))
        .WebSelectionType = xlAllTables ' 読み込む対象
                    ' xlAllTables：全テーブル　　xlSpecifiedTables：特定のテーブル
        '.WebTables = "1" ' ↑でxlSpecifiedTablesを選んだ場合に何番目のテーブルを読むか
        .WebFormatting = xlWebFormattingNone ' 値のみ読込。Allだと書式リンクを含む
        .WebDisableDateRecognition = True ' 日付への変換を止める場合はTrue
        
        .Refresh BackgroundQuery:=False ' ←読み込みを実行
        .Delete ' ←接続を切る（「クエリと接続」一覧から削除）
    End With
    
End Sub

解説

CSVを読み込むなどにも使用するQueryTableですが、
AddメソッドのConnectionにURLを渡すことでWEBの読み込みも実行できます。

WEBの読み込みを行う場合は、

.Refresh BackgroundQuery:=False ' ←読み込みを実行
.Delete ' ←接続を切る（「クエリと接続」一覧から削除）

この部分を忘れないようにしてください。

.Deleteを怠ると、「クエリと接続」にサイトへの接続が残ってしまい、
Excelにもサイトにも負担をかけます。

BackgroundQuery:=Falseを忘れると、Deleteの際に、

実行時エラー '1004':データを更新しています。後でもう一度お試しください。

が発生します。
規定値がTrueで省略することはできませんので忘れずに指定してください。

また、上記のコードを実行して本ページを実際に読み込んでみると、
目的の表だけでなく、VBAのコード部分も読みこんでしまうことがわかります。
（本サイトの黒網掛け部分はテーブルと認識されてしまうようです）

その場合は、

.WebSelectionType = xlAllTables

' ↓対象を「全テーブル」から「1番目のテーブルだけ」に書き換え

.WebSelectionType = xlSpecifiedTables
.WebTables = 1

このように書き換えることで、目的のテーブルだけを読み込むことができます。

テーブルが複数ある場合や、余計なものがテーブルとして検知される場合は、
こちらのプロパティを設定して対応してください。

上記の通りWebから読み込む際は「.Web～～」のプロパティが利用できますが、
代わりに「.TextFile～～」のプロパティは使用できません。

特に、CSV読み込みで「1-1⇒1月1日への変換を止めたい」ときに使用する、

.TextFileColumnDataTypes = Array(2, 1, 1, 1)

この指定はWEB読み込み時には使用できません。
※ アプリケーションまたはオブジェクト定義のエラーになります。

データ全体に対して日付への変換を停止させる

.WebDisableDateRecognition = True

こちらの設定で代用してください。

※ 「Disable」なので、Trueだと変換「しない」になる点に注意

列ごとに日付かどうか判定することは、残念ながらQueryTableではできません。
加えて「0001」を「1」に変換しない方法もQueryTableにはありません。

複雑な設定を要する場合は、PowerQueryなど別の方法を検討して下さい。

汎用関数化

この処理をよく使う方は、汎用関数にして持っておくと便利です。

' 汎用関数を使用するとマクロ本体はこう書ける↓
Sub WEBページの表をシートに出力する()

    Call QueryTableでWEB上の表を読み込む(Worksheets.Add.Range("A1") _
        , "https://www.limecode.jp/entry/utility/querytables-import-web-table" _
        , 1, True, True)
    
End Sub

' QueryTableによるWEB読み込みの汎用関数
Sub QueryTableでWEB上の表を読み込む(出力起点セル As Range _
    , 対象URL As String, Optional テーブルNo As Long = -1 _
    , Optional is書式リンク含む As Boolean = False, Optional is日付に変換する As Boolean = False)

    With 出力起点セル.Worksheet.QueryTables.Add("URL;" & 対象URL, 出力起点セル)
    
        If テーブルNo = -1 Then ' テーブルNo省略時は全テーブルを対象
            .WebSelectionType = xlAllTables
        Else
            .WebTables = xlSpecifiedTables
            .WebTables = テーブルNo
        End If
        .WebFormatting = IIf(is書式リンク含む, xlWebFormattingAll, xlWebFormattingNone)
        .WebDisableDateRecognition = Not (is日付に変換する)
        
        .Refresh BackgroundQuery:=False
        .Delete
    End With

End Sub

マクロ本体はもはや1行になっていますね。

このように、よくやる処理は汎用関数にしておくと便利です。

興味があればこちらの記事もご覧ください。

www.limecode.jp

以上でQueryTableによるWEB読み込みの解説を終わります。

QueryTableは構文もシンプルで簡単に実行できますので、
「とりあえず実行してみて上手くいかなかったら他の方法を試す」
ような使い方に向いています。

手軽な方法ですので覚えておきましょう。