レッスン-投稿一覧パソコン仕事５倍塾ポータル

投稿/コメントを表示します。

エクセルマクロVBA「Web連携」

たかちゃんさんの投稿

(投稿ID: 4966)

セブンイレブンのおにぎり紹介ページより、おにぎりの名前を取得することに成功しました。

企業のWebページの作りは複雑すぎて、ブラウザからHTMLを確認した時は、もう読めないと思いました。(T_T)
しかし、一度Bodyの部分を、このコースで使っているマクロを使ってExcelに取り込んで、更にメモ帳にはりつけて
全体を眺めたら、商品名を発見し、このページに関してはなんとかその部分だけは仕組みが分かったかなという感じです。
例題のプログラムを再利用して、ちょこっと書き直しただけですが、、動いた時は本当に嬉しかったです。(^^)

HTMLの中身はこんな感じ。
<div class=""list-inner-～～～"">
<figure> 写真
<div class=""detail"">
<div class=""item_ttl"">　商品のおにぎりの名前
<div class=""item_price"">　価格
<div class=""item_region"">　販売地域

'以下に紹介するライブラリの参照設定をしてください
'Microsoft WinHTTP Services, version 5.1 -> HTTPリクエストをするため
'Microsoft HTML Object Library -> コンテンツのDOMを解析するため
Sub GetRequestSimple1()
    Dim url As String
    url = "https://www.sej.co.jp/products/a/onigiri/"

    Dim xh As New WinHttp.WinHttpRequest
    xh.Open "GET", url, False
    xh.send
    
    Dim sCode As String
    sCode = xh.Status
    If sCode <> 200 Then
        MsgBox "リクエストに失敗しました" & vbNewLine & sCode
    End If
    
    'htmlをDOMとして取得する。そのための変数を宣言。
    Dim oHTml As New MSHTML.HTMLDocument
    oHTml.body.innerHTML = xh.ResponseText 'htmlボディーをDOMとして取得
    
    'oH2--> 1つのおにぎり紹介のdiv(この中に、商品名や値段の情報が記載)
    'oH3-->商品名のdiv
    Dim oH2 As MSHTML.HTMLHeadElement
    Dim oH3 As MSHTML.HTMLHeadElement
    For Each oH2 In oHTml.getElementsByClassName("list_inner")
        For Each oH3 In oH2.getElementsByClassName("item_ttl")
            Debug.Print oH3.innerText '商品名（おにぎり名）を書き出し
        Next
    Next
End Sub

2020/12/30 14:21

田中宏明さんのコメント

(コメントID: 7011)

たかちゃんさん:

Web連携にチャレンジされているのですね。
コピペで動きました。すごいですね。

それで、値段も取得できるよう追記してみました。いろいろな方法がありそうですが、ご参考まで。

Sub GetRequestSimple1_Tanaka()
    Dim url As String
    url = "https://www.sej.co.jp/products/a/onigiri/"

    Dim xh As New WinHttp.WinHttpRequest
    xh.Open "GET", url, False
    xh.send
    
    Dim sCode As String
    sCode = xh.Status
    If sCode <> 200 Then
        MsgBox "リクエストに失敗しました" & vbNewLine & sCode
    End If
    
    'htmlをDOMとして取得する。そのための変数を宣言。
    Dim oHTml As New MSHTML.HTMLDocument
    oHTml.body.innerHTML = xh.ResponseText 'htmlボディーをDOMとして取得
    
    'oH2--> 1つのおにぎり紹介のdiv(この中に、商品名や値段の情報が記載)
    'oH3-->商品名のdiv
    Dim oH2 As MSHTML.HTMLHeadElement
    Dim oH3 As MSHTML.HTMLHeadElement
    For Each oH2 In oHTml.getElementsByClassName("list_inner")
        For Each oH3 In oH2.getElementsByClassName("item_ttl")
            '商品名（おにぎり名）を書き出し
            Debug.Print oH3.innerText & vbTab;
            '値段を書き出し by Tanaka
            Debug.Print oH3.parentElement.Children(1).innerText
        Next
    Next
End Sub

> セブンイレブンのおにぎり紹介ページより、おにぎりの名前を取得することに成功しました。
>…
>動いた時は本当に嬉しかったです。(^^)

2020/12/30 21:03

たかちゃんさんのコメント

(コメントID: 7012)

田中宏明さん:

ありがとうございます！これは凄い。。
更に、以下を付け足したところ、販売地域も取得してきました。
Debug.Print oH3.parentElement.Children(2).innerText

参考サイトで意味も確認してきました。
【参考ページ】
https://www.vba-ie.net/element/parent-children.php#a12

まだ、各プロパティの調べ方が良く分らず。。
今回はとても勉強になりました。(^^)

> たかちゃんさん:
>
> それで、値段も取得できるよう追記してみました。いろいろな方法がありそうですが、ご参考まで。
>

2020/12/31 07:57

小川慶一さんのコメント

(コメントID: 7015)

たかちゃんさん:

お二人ともすごいです (^^

演習としてチャレンジするなら、地域ごとに販売されている商品のリストを作るとかもおもしろいかもしれません。
やり方はいろいろありますが...例えば、以下の流れで。

[1] 各商品の販売地域を取得したところで、販売地域をキーとした辞書に、商品の配列を追加していく。
[2] 全商品について調べきったところで、辞書のすべてのキーと値を吐き出す。

2020/12/31 15:22

たかちゃんさんのコメント

(コメントID: 7017)

小川慶一さん:
販売地域をDictionaryのkeyにして、おにぎりの販売地域一覧表を作成する
コードを書いてみました。記念に投稿します。(^^)

'以下に紹介するライブラリの参照設定をしてください
'Microsoft WinHTTP Services, version 5.1 -> HTTPリクエストをするため
'Microsoft HTML Object Library -> コンテンツのDOMを解析するため
'Microsoft Scrintping Runtime -> Dictionaryを使用するため
Sub GetRequestSimple1()
    Dim dic As Scripting.Dictionary
    Set dic = CreateObject("Scripting.Dictionary")

    Dim url As String
    url = "https://www.sej.co.jp/products/a/onigiri/"

    Dim xh As New WinHttp.WinHttpRequest
    xh.Open "GET", url, False
    xh.send
    
    Dim sCode As String
    sCode = xh.Status
    If sCode <> 200 Then
        MsgBox "リクエストに失敗しました" & vbNewLine & sCode
    End If
    
    'htmlをDOMとして取得する。そのための変数を宣言。
    Dim oHTml As New MSHTML.HTMLDocument
    oHTml.body.innerHTML = xh.ResponseText 'htmlボディーをDOMとして取得
    
    'oH2--> 1つのおにぎり紹介のdiv(この中に、商品名や値段の情報が記載)
    'oH3-->商品名のdiv
    Dim oH2 As MSHTML.HTMLHeadElement
    Dim oH3 As MSHTML.HTMLHeadElement
    Dim tiku As String
    Dim spTiku As Variant
    Dim c As Long
    Dim idx As Long
    idx = 0
  'ーーーーーーーーーーーーーーここから上は前回と同じ。
    
    For Each oH2 In oHTml.getElementsByClassName("list_inner")
        For Each oH3 In oH2.getElementsByClassName("item_ttl")
            '商品名の書き出し
            Range("A2").Offset(idx).Value = oH3.innerText
            idx = idx + 1
            
            '販売地域の余計な文字をMidとInzStrで取り除く
            tiku = Mid(oH3.parentElement.Children(2).innerText, InStr(oH3.parentElement.Children(2).innerText, "：") + 1)
            spTiku = Split(tiku, "、")
            '販売地域をKeyにして登録。北海道　ツナ｜おかか｜梅干し　の様にDictionaryへ格納
            For c = LBound(spTiku) To UBound(spTiku)
                If Not dic.Exists(spTiku(c)) Then
                    dic.Add spTiku(c), oH3.innerText
                Else
                    dic.Item(spTiku(c)) = dic.Item(spTiku(c)) & "|" & oH3.innerText
                End If
            Next
        Next
    Next
    
    'ディクショナリの内容を一気に書き出し。
    Dim spItem As Variant
    Dim cnt As Long
    
    For c = LBound(dic.Keys) To UBound(dic.Keys)
        '地域の項目を書き出し
        Range("B1").Offset(, c).Value = dic.Keys(c)
        
        spItem = Split(dic.Items(c), "|")
        For cnt = LBound(spItem) To UBound(spItem)
            '販売されているおにぎりの行に〇を入力
            For idx = 0 To Range("A" & Rows.Count).End(xlUp).Row - 2
                If Range("A2").Offset(idx).Value = spItem(cnt) Then
                    Range("B2").Offset(idx, c).Value = "〇"
                    Exit For
                End If
            Next
            
        Next
    Next
    
    '罫線を引く
    Range("A1").CurrentRegion.Borders.LineStyle = True
End Sub

> 演習としてチャレンジするなら、地域ごとに販売されている商品のリストを作るとかもおもしろいかもしれません。

2021/01/01 09:48

田中宏明さんのコメント

(コメントID: 7018)

たかちゃんさん:

短期間でここまでできるなるとは驚きで言葉が見つかりません。
地域のページURLを取得してみましたので、ご参考まで。

'以下に紹介するライブラリの参照設定をしてください
'Microsoft WinHTTP Services, version 5.1 -> HTTPリクエストをするため
'Microsoft HTML Object Library -> コンテンツのDOMを解析するため
 Sub GetRequestSimple_Tanaka()
 
    Dim url As String
    url = "https://www.sej.co.jp/products/a/onigiri/"
 
    Dim xh As New WinHttp.WinHttpRequest
    xh.Open "GET", url, False
    xh.send
     
    Dim sCode As String
    sCode = xh.Status
    If sCode <> 200 Then
        MsgBox "リクエストに失敗しました" & vbNewLine & sCode
    End If
     
    'htmlをDOMとして取得する。そのための変数を宣言。
    Dim oHTml As New MSHTML.HTMLDocument
    oHTml.body.innerHTML = xh.ResponseText 'htmlボディーをDOMとして取得

    Dim HElem As MSHTML.HTMLHeadElement
    Dim AElem As MSHTML.HTMLAnchorElement
    
    'URL取得
    For Each HElem In oHTml.getElementsByClassName("ac_detail")
        For Each AElem In HElem.getElementsByTagName("a")
            If AElem.hasAttribute("href") Then
                '余計な文字をMidとInStrで取り除く
                Debug.Print "https://www.sej.co.jp" & _
                Mid(AElem.getAttribute("href"), InStr(1, AElem.getAttribute("href"), "about:") + 6)
            End If
        Next AElem
    Next HElem
End Sub

2021/01/02 02:13

小川慶一さんのコメント

(コメントID: 7019)

田中宏明さん:

> 短期間でここまでできるなるとは驚きで言葉が見つかりません。

すごいですよね。

辞書のItemに配列を格納することもできます。

以下のサンプルを作りました。参考にしてください。
「区切り文字を自分で考えて用意し、取り出し時にSplit関数を使う」というのも良いですが、このほうがスマートかと。

動作確認時には、B2:B11に重複した都市名、C2:C11に任意の文字列を入れて実行してみてください。

Array関数は、Variant型の配列を作る関数です。講座本編では紹介していないと思いますが、手っ取り早いのでここではあえてこれを使いました。

Sub dic_value_array_sample()
    Dim dic As New Scripting.Dictionary
    Dim i As Long, pref As String, okashi As String
    Dim ar As Variant
    For i = 2 To 11
        pref = Range("B" & i).Value
        okashi = Range("C" & i).Value
        If Not dic.Exists(pref) Then
            dic.Add pref, Array(okashi)
        Else
            ar = dic.Item(pref)
            ReDim Preserve ar(UBound(ar) + 1)
            ar(UBound(ar)) = okashi
            dic.Item(pref) = ar
        End If
    Next
    
    Dim j As Long
    Range("E1").Value = "pref"
    Range("F1").Value = "values"
    With Range("E2")
        For i = LBound(dic.Keys) To UBound(dic.Keys)
            pref = dic.Keys(i)
            ar = dic.Item(pref)
            .Offset(i, 0).Value = pref
            For j = LBound(ar) To UBound(ar)
                .Offset(i, j + 1).Value = ar(j)
            Next
        Next
    End With
End Sub

2021/01/02 08:14

たかちゃんさんのコメント

(コメントID: 7020)

小川先生、田中さん

いつもありがとうございます! ちょっとした事でも何もかもが
本当に勉強になります。
将来、別の生徒さんが見た時に簡単に読み解けるよう
調べた事を備忘録としてコメントします。
まずは、以下について、もう一つのコードはこれから読み解きます。(^^)

【地域ごとのリンクを取ってくるコードについて】

●該当の部分、HTMLはこんな風になっている。
<div class="ac_detail">
<ul>
<li><a href="/products/a/onigiri/hokkaido/">北海道</a></li>
<li><a href="/products/a/onigiri/tohoku/">東北</a></li>
</ul>
</div>
因みに、href="/～"は、ドメインを省略するhrefの書き方。
【参考サイト】
https://allabout.co.jp/gm/gc/475758/

コードを読んでいて「何故、about:を取り除いているのだろう？」と思い
Debug.Printで調査しました。

Debug.Print HElem.outerHTMLをした所、以下のようにabout:がついていました。
ドメインを省略した書き方をしたりすると、多少見え方が変わったりするんだと納得。

<DIV class=ac_detail><UL>
<LI><A href="about:/products/a/onigiri/hokkaido/">北海道</A></LI>
</UL>
</DIV>

Debug.Print AElem.outerHTML
<A href="about:/products/a/onigiri/hokkaido/">北海道</A>

Debug.Print AElem.innerText
北海道

aタグアンカータグの中身を取りたい時は、こう書けば良いんですですね。
Debug.Print AElem.getAttribute("href")
about:/products/a/onigiri/hokkaido/

その他参考リンク
https://excel-ubara.com/excelvba8/EXCELVBA819.html
https://www.relief.jp/docs/002097.html
https://docs.microsoft.com/ja-jp/dotnet/api/system.xml.xmlelement.hasattribute?view=net-5.0

> 地域のページURLを取得してみましたので、ご参考まで。

2021/01/02 12:48

田中宏明さんのコメント

(コメントID: 7023)

たかちゃんさん:

リンクを取得するコードを整理していただき、ありがとうございます。

> もう一つのコードはこれから読み解きます。(^^)

こちらは連想配列の応用技登場ですね。

エクセルマクロ・VBA発展編２演習と回答に記念投稿しました。
https://forum.pc5bai.com/lesson/page/1362

2021/01/02 21:08

たかちゃんさんのコメント

(コメントID: 7024)

田中宏明さん:
ありがとうございます！
先生と田中さんのコードを、ウォッチ式を使って注意深く動きを確認。その後２つのコードを自分でも書いてみてから、例のおにぎりのコードも書き直してみたところ成功しました。

ウォッチ式、動きを理解するのに本当に便利です。
dicとar（Variantの配列）の２つにセットして、１つ１つ動かしながら見たら理解できました。動きを見るのが一番理解しやすいです。
いつもありがとうございます。(^^)

> こちらは連想配列の応用技登場ですね。
>
> エクセルマクロ・VBA発展編２演習と回答に記念投稿しました。
> https://forum.pc5bai.com/lesson/page/1362

2021/01/03 14:00

小川慶一さんのコメント

(コメントID: 7028)

たかちゃんさん:

さすがです。
あとは、さらにやるなら、北から順に出力したい順に地域名が書き出されたリストを用意して、辞書のキーごとに、表の適切な箇所に出力するようにするくらいですかね。
これも、「地域名が書き出されたリスト」自体を地域名をキー、出力先の行を値とした辞書(*1)を作ってそれと照合するように書くと早いです。

その場合も、値がひとつも入らないセルが出現しないように書き出すための工夫もいくつか考えられそうです。
スマートな例のひとつは、(*1)の辞書のキーと同じキーがウェブページから拾ってきた値を元に生成した辞書のキーにあるかどうかを調べ、あった場合のみ、(*1)の元データとはまた別の出力先に書き出すという感じでしょうか。

そうなると、もはや、ウェブページ解析ではなく、連想配列の取り扱いの話ですが。

2021/01/03 19:40