はじめに
下記サイトの方で「ログイン後にWebクエリをしたい」旨の質問を頂きました。
質問の回答するにあたり、先ずは「Webクエリ」って何だろう？というところから始まりました。
Excelを普段さわっているといっても、仕様書(Excel方眼紙)を書くとかエビデンス用にテスト結果を貼るみたいな感じですからね。非ITエンジニアの方々のほうがよっぽどExcelを使いこなしているでしょう。
Webクエリとは
Webサイトにある表データをExcelに取り込むことができるようになっています。
今回、ログイン後に表示される表データをExcelに取り込みたいということで、TinySeleniumVBAを検討しているという話でした。確かに認証しないと表示されない表データでは、WebクエリでURLを指定しても認証エラーになってしまい使用できないですね。
対応方法
VBAでWebクエリを操作できることが分かりました。あと検索しててローカルのHTMLファイルを指定しているサイトも見掛けました。
そうだとしたら、一度テーブルタグをローカルにHTMLを保存した上でVBAでWebクエリを操作すれば出来そうだと考えました。
テーブルタグのHTMLを取得する方法は下記サイトを参考にしましたが、TinySeleniumVBAで実現する方法には考えさせられました。TinySeleniumVBAには必要最小限の機能しかないため、element.GetAttribute("innerHTML") としても駄目でした。
しかし、TinySeleniumVBAには、JavaScriptを実行する機能ExecuteScriptが備わっているので最悪これで何とか出来そうだと分かりました。
ソースコード
TinySeleniumVBAは最新版v0.1.3のEdge上で確認しています。
サンプルとして「2020年基準 消費者物価指数 全国 2022年（令和4年）2月分 （2022年3月18日公表）」の表をWebクエリで取得することにしました。
' Start WebDriver (Edge)
Dim Driver As New WebDriver
Driver.Edge "C:\Tools\edgedriver_win64\msedgedriver.exe"
Driver.OpenBrowser
Driver.Navigate "https://www.stat.go.jp/data/cpi/sokuhou/tsuki/index-z.html"
Dim contents As String
contents = Driver.ExecuteScript("return document.getElementsByClassName('datatable')[0].outerHTML;")
Dim Target As String
Target = ThisWorkbook.path & "\table.html"
With CreateObject("ADODB.Stream")
.Charset = "UTF-8"
.Open
.WriteText "<html><body>", 1
.WriteText contents, 1
.WriteText "</body></html>", 1
.SaveToFile Target, 2
.Close
End With
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Target, _
Destination:=Range("$A$1"))
.Name = "Test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.Delete
End With
Driver.Shutdown
QueryTablesのパラメータをたくさん記載していますが、WebFormatting = xlWebFormattingNone くらいを指定する程度でいいと思ってます。参考にしたサイトをコピーしたので長くなっただけです。
最終的に、QueryTablesはDeleteしています。
テーブルデザインのスタイル採用
このままだと、現行のWebクエリと同じ状態にはなっていないのです。
現行のWebクエリはテーブルデザインのスタイルが採用されて交互に背景色が変わっていますし、フィルターもかかっています。
それにはQueryTablesは削除(Delete)した上で、ListObjectsに変更する必要がありました。
下記の行をDriver.Shutdownの直前に追加します。
ActiveSheet.ListObjects.Add 1, Range("A1").CurrentRegion
これにより、テーブルデザインのスタイルが採用されて交互に背景色が変わりフィルターもかかっています。
最後に
Qiitaに記事を書くとしても変わりばえしない開発ばかりだと、なかなかネタは生まれないわけです。
同僚とか誰かが困っていると、これはネタになるぜと思ってメモしています。
TinySeleniumVBAは、非ITエンジニア側の人の方が触れる機会が多そうなので、いい架け橋になりそうですね。