100本目：WEBから100本ノックのリストを取得

Excel&VBA解説サイト「エクセルの神髄」様出題の問題集、
「VBA100本ノック」に対する私の回答と解説のページです。

100本ノックの出題リストはこちらから
excel-ubara.com

出題：WEBから100本ノックのリストを取得

以下のページにはVBA100本ノックのリストが掲載してあります。
https://excel-ubara.com/vba100sample/vba100list.html
この一覧を表形式でシートに出力してください。
方法不問。VBAで自動取得すれば良い。

◇ 出題ページはこちら
excel-ubara.com

ソースコード

' VBA100本ノック_100_WEBから100本ノックのリストを取得
Sub ★指定URLの第1テーブルを新規シートに出力する()

    Call QueryTableでWEB上の表を読み込む(ThisWorkbook.Worksheets.Add.Range("A1") _
        , WS実行シート.Range("C2"), 1, is書式リンクを含む:=False, is日付に変換する:=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

※ 実行シートはこんな風にしました。

回答を記載したExcelファイルはこちら
【和風スパ】VBA100本ノック_100(WEBから100本ノックのリストを取得).xlsm

解説

100本ノック回答のマイルールとして、
「なるべく汎用関数を作って流用しやすいマクロにする」
というルールを掲げて取り組んでいます。

そのルールにのっとり、QueryTableオブジェクトを使って
「指定URLの第1テーブルをシートに出力する」マクロで回答しました。

QueryTableによるWEB読込の解説は下記ページをご覧ください。
www.limecode.jp

ちなみに回答時、真っ先に思い浮かんだのはPowerQueryだったのですが、
PowerQueryをマクロ記録したときに出てくる、

wb.Queries.Add Name:=cnsTBL, Formula:= _
    "let ソース = Web.Page(Web.Contents(""" & cnsURL & """))," & _
    "Data0 = ソース{0}[Data]," & _
    "変更された型 = Table.TransformColumnTypes(Data0,{" & _
        "{""本数"", type text}," & _
        "{""出題日"", type date}," & _
        "{""出題ツイート"", type text}," & _
        "{""問題と解説記事"", type text}," & _
        "{""テスト用データ"", type text}}) in 変更された型"

この列ごとの設定部分を消してどんなURLでも動くようにする方法がわからず、
QueryTableで実装しなおして回答しました。

この解決策はえくせるちゅんちゅんのちゅんさんから教えてもらい、

wb.Queries.Add Name:=cnsTBL, Formula:= _
    "let ソース = Web.Page(Web.Contents(""" & cnsURL & """))," & _
    "Data0 = ソース{0}[Data] in Data0"

このように、変更された型の指定部分を削除したあと、
最後の「in 変更された型」を「in Data0」に書き換えることで、
テーブル固有の型変更を停止させることができました。

こちらの記述であればPowerQueryでもURLを渡す汎用化ができるようです。

ちゅんさん情報提供ありがとうございました(´∀｀)
www.excel-chunchun.com