100本目ウェブからVBA100本ノックのリストを取得

VBA100本ノック 100本目を動画で解説しています。Excelの神髄さんの模範解答はコチラ

問題
解答
ウェブの情報を取得する方法
マクロの記録でコードを記録する
Withステートメントの中を整理する
再実行を考慮する
あとがたり

問題

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

解答

Sub ノック100本目()
    
    On Error Resume Next
    ActiveWorkbook.Queries("Table 0").Delete 'もし既にあれば削除
    On Error GoTo 0
    Cells.Clear
    
    ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
        "let ソース = Web.Page(Web.Contents(""https://excel-ubara.com/vba100sample/vba100list.html""))," & _
        " Data0 = ソース{0}[Data]," & _
        " 変更された型 = Table.TransformColumnTypes(Data0,{{""本数"", type text}, {""出題日"", type date}, {""出題ツイート"", type text}, {""問題と解説記事"", type text}, {""テスト用データ"", type text}})" _
        & "in 変更された型"
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0]")
        .PreserveFormatting = True
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0 '更新間隔
        .ListObject.DisplayName = "Table_0"
        .Refresh BackgroundQuery:=False
    End With
End Sub

■考え方・流れ
0:00 冒頭・問題確認
1:52 ウェブの情報を取得する方法
3:48 マクロの記録でコードを記録する
6:27 Withステートメントの中を整理する
9:38 再実行を考慮する

新人君

遂に最後ですね！

ブチョ

諦めてもいいんだよ？

新人君

諦めてたまるか…

ウェブの情報を取得する方法

さて！じゃあそもそも…
WEB上の情報(テーブル形式)を取得する方法って何があるの？って話なんだけど…

ま、パワークエリでいいかな！(Excel2013以前はウェブクエリ機能？みたいな名前)
どちらにせよやることはほぼ変わらないっす！

「データ」→「ウェブから」を押して、対象のURLを入力

読み込み！でもOKだけど…
読み込み先を変えたい時は、横の三角ボタンから色々変更できるよ！

出力先を決めたらOK！

まぁ…なんて簡単…

マクロの記録でコードを記録する

さて！やり方がわかったら…
次は記録記録ぅ！！

記録したら中身を見てみよう！！
っておぇぇぇえぇぇ！！！なげえ！！！

後は、このコードの中でわからんところをググって修正！ググって修正！の繰り返し！！
よく分からんけどChr(13)とかのところが一切合切いらんと判断したので消していくぞ！

ま、この辺は訓練していくしかないね！！
何回もやってたら、コツがつかめてくるぞ！

Withステートメントの中を整理する

マクロの記録を使って良く記録されるのが、異常にWithの中身が多いコード！！
明らかにデフォルトのままでいいところのコードは消した方が見やすいぞ！

つまりこのファンクションでは、席替え後の範囲をループして、
そのセルが席替え可能かどうか一つ一つ確認していこう！

Withの一番最後のオブジェクトを確認すりゃーたぶんOK！！
主は最初これを見た時、ListObjects.Addメソッドの返り値を指定してるのかと思ったw

あとは、Withの中でわからないものをググって、修正していけばOK！

再実行を考慮する

よし！！！じゃあ最後に！！！
再実行を考慮して、終わりにするよ！！

もう一回このコードを実行すると…↓このようなエラーに…！

これを回避するために…
最後だし超シンプルに！！
Table 0ってクエリを削除するコードを入れてあげよう！！

ただ…削除系のコードは、削除対象がないとエラーになるから…(Table 0なんてありまへん！ってね。)
だからエラーハンドリングを入れてあげよう！

よし！このコードを実行すると…
あれ？？テーブルが消えない？？？？

消えてへんやんけ！と思いきや…
クエリの接続はちゃんと消えてた！！

つまり！セルをクリアにするコードを追加すればOK!!

実行結果↓

終わりです！！お疲れさまでした！

ブチョ

お疲れさマリル！

新人君

お疲れさまでした！

あとがたり

おはこんばんちは。uぷ主です。
えー、最後まで見てくれた方々、
応援してくれた皆様、
本当に、ありがとうございました。

そして、神髄さんをはじめ、ExcelやVBAの記事を書いてくれてる方々にも
感謝申し上げます。

ありがとうございました。

何だろう…涙が…(ホロリ)

約2年、ほぼ毎朝5時起き(早い時は4時)で…本業までノックやって…
本業が終わったらノックやって…
疲れたら茶番作って…
それも疲れたらポケモンして…
ポケモンも飽きたら茶番作って…
みたいな生活を続けてきました。

その結果、VBAのスキルが上がったことは言うまでもなく、
酒を飲まなくなり(飲む暇がなくなり)、超健康体になり、
論理的思考力？が身についたかどうか分かりませんが、
本業の収入も上がり、
youtubeからもお金をもらえたり、
疲労とストレスでハゲたりと…、
最高の結果が生まれました。

これがVBA100本ノック…！
これがExcelの神髄を追い求めた輩の結果なのか…！！

ホント、やって良かったです。
100本ノック、自分で回答するのももちろんなんですけど、
人に分かるように伝えるのが凄く大事だなと思いました。

職場でも後輩から、○○先輩ってすごく教えるの上手ですよね♡って言われたり。
オッサン嬉しい。

とま、冗談はさておき…
皆さんも、ぜひチャレンジしてください！！！
Excelの神髄の中の人、山岡さん。マジですげえっす。

絶対ためになります。色んな意味で。
もしわからない問題があったら、わちょんの動画を見てもらって、
ほんで、それでもわからなかったら…ワイに文句言ってくださいww

正直、解説下手やなーと思う問題も多々ありましたんで。
作り直します。
ツイッターで連絡ください！

ツイッター連動企画だからね！！

じゃ、そういうわけで…

皆さんご視聴…
ありがとうございました！！