![見出し画像](https://assets.st-note.com/production/uploads/images/144921370/rectangle_large_type_2_6c6ef3ca6e84ff2f6630e7ec809e60eb.png?width=1200)
第58回 複数ページのWebをさくっとまとめてスクレイピング!
今後のスケジュールはコチラ
投げ銭ページはコチラ
登壇者:りゅうりゅう@VBAer × ココナラPRO認定
日時 :2024年6月22日 21時
テーマ:「Webから」ボタン操作をマクロの記録をして、そのマクロを改良して同じサイトの複数ページにまたがった表データを連続でスクレイピングさせるってのをライブコーディングでやってみるよ!
![](https://assets.st-note.com/img/1719057511492-mz5TZ2whiF.png)
![](https://assets.st-note.com/img/1719057608718-q6WP20o9s5.png)
![](https://assets.st-note.com/img/1719058104832-oShpnf2DDK.png?width=1200)
![](https://assets.st-note.com/img/1719058167582-aAS6KVB4IY.png?width=1200)
![](https://assets.st-note.com/img/1719058214939-jzv7ukexLv.png)
自己紹介
![](https://assets.st-note.com/img/1719058226706-O2yjBZFAxT.png?width=1200)
![](https://assets.st-note.com/img/1719058236133-ewErR6tkXc.png?width=1200)
![](https://assets.st-note.com/img/1719058261027-7TUmZ3tq9S.png?width=1200)
![](https://assets.st-note.com/img/1719058290953-l21TJa0aHq.png?width=1200)
![](https://assets.st-note.com/img/1719058355411-gk3QpU1b53.png?width=1200)
![](https://assets.st-note.com/img/1719058391552-Owf5groaTT.png?width=1200)
![](https://assets.st-note.com/img/1719058496503-i08R3oKB4H.png)
![](https://assets.st-note.com/img/1719058574495-1MUO0eAJm3.png?width=1200)
![](https://assets.st-note.com/img/1719058587766-dRaViKZkoq.png?width=1200)
![](https://assets.st-note.com/img/1719058632042-O1WBRiI9cH.png?width=1200)
![](https://assets.st-note.com/img/1719058643676-tShx5ivkjg.png?width=1200)
![](https://assets.st-note.com/img/1719058668153-6VKIzAa0Kk.png?width=1200)
![](https://assets.st-note.com/img/1719058703566-4zZARQ47ya.png)
![](https://assets.st-note.com/img/1719058788893-tVjc43XMDo.png)
![](https://assets.st-note.com/img/1719058723422-4KEDVO2kTL.png?width=1200)
![](https://assets.st-note.com/img/1719058740130-O1ypANnbNZ.png?width=1200)
![](https://assets.st-note.com/img/1719058799546-tMUb54MBhk.png?width=1200)
![](https://assets.st-note.com/img/1719058823056-BFeBv19z6u.png?width=1200)
![](https://assets.st-note.com/img/1719058859998-u28ectWRYe.png?width=1200)
![](https://assets.st-note.com/img/1719058889492-Ek9ee03i5J.png?width=1200)
URLをメモ帳に
![](https://assets.st-note.com/img/1719058916189-PITGX3dov9.png?width=1200)
![](https://assets.st-note.com/img/1719058973411-X6f1oio97Q.png?width=1200)
ライブコーディング
![](https://assets.st-note.com/img/1719059008662-jpEqyJI5Ka.png)
![](https://assets.st-note.com/img/1719059029806-nmcHgek2Gb.png)
![](https://assets.st-note.com/img/1719059042235-wKxUzHnLrF.png?width=1200)
![](https://assets.st-note.com/img/1719059083364-WpMoEk8GZq.png)
![](https://assets.st-note.com/img/1719059123368-RhlGGVR4rG.png?width=1200)
![](https://assets.st-note.com/img/1719059220098-a4fJJ99aQa.png?width=1200)
パワク処理は2段階。パワクで取り込む上段とテーブルに変換する下段
取り込む部分の改造
![](https://assets.st-note.com/img/1719059340272-DSY8c7YEKv.png?width=1200)
![](https://assets.st-note.com/img/1719059328455-9odaH0KU6N.png?width=1200)
![](https://assets.st-note.com/img/1719059387998-hL3dXnJ2Lz.png)
![](https://assets.st-note.com/img/1719059419230-jMDZDkutbD.png?width=1200)
![](https://assets.st-note.com/img/1719059558702-mvm5X2mO94.png?width=1200)
マクロ名の変更、各種変数宣言と代入
![](https://assets.st-note.com/img/1719059866606-thnpeLtDUO.png)
![](https://assets.st-note.com/img/1719059945837-Mr0KoL0M5S.png)
![](https://assets.st-note.com/img/1719060229370-DpdQuT9sZm.png?width=1200)
![](https://assets.st-note.com/img/1719060147964-P6RWfpKpiy.png?width=1200)
![](https://assets.st-note.com/img/1719060164582-ikwZjsDflz.png?width=1200)
テーブル化する部分の改造
![](https://assets.st-note.com/img/1719060479245-YqBnvrRx86.png?width=1200)
![](https://assets.st-note.com/img/1719060490271-JaYoaMfJiu.png)
ついでにシート名も変更。
Option Explicit
Sub abWeb取り込み()
Dim zl年 As String
Dim zl月 As String
Dim zl掲載号 As String
zl年 = 1982
zl月 = Format(8, "00")
zl掲載号 = "掲載号" & zl年 & "年" & zl月 & "月"
ActiveWorkbook.Queries.Add Name:= _
zl掲載号, Formula:= _
"let" & Chr(13) & "" & Chr(10) & " ソース = Web.BrowserContents(""https://www.aabmg.com/yearbook/list.php?sY=" & zl年 & "&sM=" & zl月 & """)," & Chr(13) & "" & Chr(10) & " #""HTML から抽出されたテーブル"" = Html.Table(ソース, {{""Column1"", ""TABLE.list > * > TR > :nth-child(1)""}, {""Column2"", ""TABLE.list > * > TR > :nth-child(2)""}, {""Column3"", ""TABLE.list > * > TR > :nth-child(3)""}, {""Column4"", ""TABLE.list > * > TR > :nth-child(4)""}}, [R" & _
"owSelector=""TABLE.list > * > TR""])," & Chr(13) & "" & Chr(10) & " 変更された型 = Table.TransformColumnTypes(#""HTML から抽出されたテーブル"",{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " 変更された型" & _
""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & zl掲載号 & """;Extended Pr" _
, "operties="""""), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT * FROM [" & zl掲載号 & "]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = zl掲載号
.Refresh BackgroundQuery:=False
End With
ActiveSheet.Name = zl掲載号
End Sub
月を8月にすれば、シートがどんどん増えるぜぇ。
![](https://assets.st-note.com/img/1719060818918-8hUXKqO19V.png)
![](https://assets.st-note.com/img/1719060860453-oqabnIYttH.png)
年と月を引数にしてサブルーチン化
![](https://assets.st-note.com/img/1719061148201-7NEsXYOjbb.png)
![](https://assets.st-note.com/img/1719061091868-VOWKQCy6ot.png)
![](https://assets.st-note.com/img/1719061184668-nw6ScNWjqD.png)
ループ処理・確認用プロシージャー
![](https://assets.st-note.com/img/1719061594600-EX3GrFat0a.png)
![](https://assets.st-note.com/img/1719061586177-099uGoCdeD.png)
![](https://assets.st-note.com/img/1719061773296-ovIxn4tzaf.png)
ループ処理本番
![](https://assets.st-note.com/img/1719061725063-y1z2jic0iT.png)
![](https://assets.st-note.com/img/1719061760255-sOswoah3WO.png)
![](https://assets.st-note.com/img/1719061808256-NY63QtGkcY.png?width=1200)
テーブルをがっちゃんこ
![](https://assets.st-note.com/img/1719061936272-uqLbljQeas.png)
![](https://assets.st-note.com/img/1719062011606-a11TjrlWJH.png)
![](https://assets.st-note.com/img/1719061980929-g4Xr993VJ8.png?width=1200)
![](https://assets.st-note.com/img/1719062085979-61QcQDTmNY.png?width=1200)
![](https://assets.st-note.com/img/1719062155625-08O8Qoqnos.png?width=1200)
![](https://assets.st-note.com/img/1719062200785-mvE0TY5ygH.png)
風柳判For~Nextバージョン
ループ処理確認用プロシージャ(別解)貼り付けておきます
Forループでもできるということで…
Sub sb確認用1()
Dim zl年月 As Date
Dim zl停止年月 As Date
zl年月 = DateSerial(1982, 7, 1)
zl停止年月 = DateSerial(1983, 1, 1)
Do While zl年月 <= zl停止年月
Debug.Print zl年月
zl年月 = DateAdd("m", 1, zl年月)
Loop
End Sub
Sub sb確認用2()
Dim zl年月 As Date
Dim i As Long
zl年月 = DateSerial(1982, 7, 1)
For i = 0 To DateDiff("m", zl年月, DateSerial(1983, 1, 1))
Debug.Print zl年月
zl年月 = DateSerial(Year(zl年月), Month(zl年月) + 1, 1)
Next
End Sub
Webクエリボタンとの違い ※2016以前
![](https://assets.st-note.com/img/1719066449438-CF4NJzC1x9.png)
ファイル
ちゅんちゅん日誌
ゆえまる日誌
投げ銭ページ
次回はちゅんさんの登壇!
![](https://assets.st-note.com/img/1719062470631-QfG4AQ69A4.png?width=1200)
![](https://assets.st-note.com/img/1719062492910-84jT77NUWD.png?width=1200)
![](https://assets.st-note.com/img/1719062526656-7hkVqpOws1.png?width=1200)
![](https://assets.st-note.com/img/1719062552852-p8RrjwBb8F.png?width=1200)
この記事が気に入ったらサポートをしてみませんか?