エクセルVBAとWEBクエリを駆使し
WEBページからデータを次々とエクセルへ入力するプログラムを作成したいと考えています。
データを抜き取ってくるページはヤフーオークションです。
抜き取りたいデータは以下の写真1~12にあるデータになります。
抜き取りたいデータ(写真1):http://oskuni7.sakura.ne.jp/hatena/question7/pic1.jpg
抜き取りたいデータ(写真2):http://oskuni7.sakura.ne.jp/hatena/question7/pic2.jpg
データを読み込むエクセルは以下のようになります。
http://oskuni7.sakura.ne.jp/hatena/question7/query.htm
プログラムを実行すると、商品URLのURLへ飛び、その後写真番号1番のデータから順番に次々とエクセルへデータを書き込んでいくというプログラムになります。。
WEBクエリとVBAを駆使して以上のようなことをすることは可能でしょうか?
もし可能でしたらどのようにすれば可能になるか教えていただけないでしょうか?
もし不可能でしたらどのような工程を経たら可能になるでしょうか?
お手数をおかけしますがご回答をよろしくお願いいたします。
EXCEL の VBA を使用した例です。
ただ、この方法はサイトの形式が変わるとデータが取れなくなる欠点がありますし、
ページによって書式が違う場合もデータがとれません。
Option Explicit Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Sub getAuctionInfo() Dim objIE As Object Const startRow = 3 Set objIE = CreateObject("InternetExplorer.application") objIE.Visible = True Dim lastRow As Long lastRow = Range("A" & startRow).End(xlDown).Row Dim i As Long For i = startRow To lastRow browsAndGetInfo objIE, Cells(i, "A").Value, i Next objIE.Quit End Sub Sub browsAndGetInfo(objIE, url As String, num As Long) objIE.Navigate url While objIE.ReadyState <> 4 While objIE.Busy = True DoEvents Sleep 300 Wend Wend Cells(num, "B").Value = objIE.Document.getElementsByTagName("H1").Item(0).innertext Cells(num, "C").Value = objIE.Document.getElementById("modCtgPath").innertext Dim es As Object Dim ls As Variant Dim ll As Variant Dim ws As Variant Dim i As Long '----------------------------------- Set es = objIE.Document.getElementsByTagName("h3") For i = 0 To es.Length - 1 If es.Item(i).innertext = "出品者の情報" Then ls = Split(es.Item(i).ParentNode().innertext, vbNewLine) For Each ll In ls If InStr(ll, ":") > 0 Then ws = Split(ll, ":") Select Case Trim(ws(0)) Case "出品者" Cells(num, "C").Value = Replace(ws(1), "(自己紹介)", "") End Select End If Next End If Next '----------------------------------- Set es = objIE.Document.getElementsByTagName("th") For i = 0 To es.Length - 1 If es.Item(i).innertext = "現在の価格" Then ls = Split(es.Item(i).ParentNode().ParentNode().innertext, vbNewLine) For Each ll In ls If InStr(ll, ":") > 0 Then ws = Split(ll, ":") Select Case Trim(ws(0)) Case "現在の価格" Cells(num, "E").Value = Replace(ws(1), "(自己紹介)", "") Case "即決価格" Cells(num, "F").Value = ws(1) Case "残り時間" Cells(num, "G").Value = Replace(ws(1), "(詳細な残り時間)", "") Case "入札件数" Cells(num, "H").Value = Replace(ws(1), "(入札履歴)", "") End Select End If Next End If Next '----------------------------------- Set es = objIE.Document.getElementsByTagName("p") For i = 0 To es.Length - 1 If es.Item(i).innertext = "詳細情報" Then ls = Split(es.Item(i).ParentNode().innertext, vbNewLine) For Each ll In ls If InStr(ll, ":") > 0 Then ws = Split(ll, ":") Select Case Trim(ws(0)) Case "開始時の価格" Cells(num, "I").Value = ws(1) Case "落札者" Cells(num, "J").Value = ws(1) Case "開始日時" Cells(num, "K").Value = ws(1) Case "終了日時" Cells(num, "L").Value = ws(1) Case "オークションID" Cells(num, "M").Value = ws(1) End Select End If Next End If Next End Sub
出来るとは思いますが、WEB に特化されたスクリプトである PHP や Perl を使った方がいいと思います。
データの格納には MySQL や SQLite を使えばいいかと思います。
スクリプトで html を取得
正規表現で抜き出す
それらデータをデータベースにプッシュ
あとは定期的にバックアップでもとればよいかと。
こうすることで、よほど変な風に組まなければ Linux でも Windows でも同じスクリプトが動作します。
ご回答ありがとうございます。
WinHttp.WinHttpRequest とか XMLHTTP でHTMLソースを取得し、正規表現で抜き出せば良いかと思います。
参考
WinHTTP (Windows HTTP Services) 覚書
ご回答ありがとうございます。
WinHTTPやXMLHTTPでも試したことがありますが、ヤフオクのデータを取得する場合に限っていえば、ログインをどうやるかが問題です。
WinHTTPやXMLHTTPだとIEのcookieを流用できないのでVBAから手動でログイン操作とcookie管理を行わねばならず、(できなくはないですが)相当難しいと思います。
また、取れるのは生HTML(ヤフオクの場合EUC)なので、Excelで扱うには文字コード変換が必要です。(VBAにはEUCの変換機能はないので自力で作らねばならない。作ったことがあるので作れないわけではないですが)
こちらでちょろっとやり方を書いてますので参考になれば。
http://d.hatena.ne.jp/ardarim/20070110/1168449396
このやり方で条件に挙げるようなすべてのデータを取得してExcelで管理するVBAが作れます(実際に使ってます)
ご回答ありがとうございます。
ブログのほう拝見させていただきました。
今回はログイン機能は今のところ使用せず、ヤフオクの場合はゲストの状態で情報を取得するので
WinHTTPもしくはXMLHTTPを使ってみようかと思いました。
その際、ポイントとなってくるのはEUCの変換となりそうなのですが、やはり変換は難しいでしょうか?
また今回はデータを抽出するケースですが、逆にフォームの中にデータをどんどん入力していくケース、自動出品ソフトやメールフォーム一括入力などもこれと同じ様にWinHTTP等を使用すればできるのでしょうか?
その場合はログイン機能を使いたいと考えているのですが、、、。
よくわからず仕舞いですが、もし知っておりましたらご回答をよろしくお願いいたします。
ExcelVBAなら問題なくできます
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1220007...
こちらが参考になると思います XP IE6 Excel2000 での確認です
サンプルが必要なら用意します
ご回答ありがとうございます。
ヤフオクで使う前提であれば、XMLHTTPよりWinHTTPを使うことをお勧めします。XMLHTTPではうまく動かない場合がありました。ランダムなのか条件は分かりませんが、特定アイテムの詳細ページをロードした場合にヤフオクがログインページにリダイレクトするレスポンスを返すことがあり、XMLHTTPはリダイレクトをうまく扱えないためです。
VBAでのEUC変換については外部DLLを呼び出すか、IEのコンポーネントを利用するか、自力で関数を書くか、などの方法があり「vba euc」で検索すれば色々見つかります。
データの取出しとは逆にフォームに値を埋めていく場合は、WinHTTPでも可能だと思いますが、前回答で書いたとおりログイン状態を維持するのが難しい(というかできるかどうか不明)と思います。
cookie管理を別にして考えれば、フォームデータをVBAで作ってWinHTTPでPOSTするだけですのでそんなに難しいことではありません。
簡単なのは、IEをCOM経由で起動して操作することです(前回書いた方法です)。この場合はIEがcookieの管理をやってくれますので、表示されるIEのログイン画面でログインする(またはVBAからパスワードなどを流し込んで自動ログインする)ことで後はすべてIEに任せられます。
この場合はフォームデータはIE上のそれぞれの入力欄に対して、VBAから流し込む(document.getElementById('xxxx').value = 'yyyy'など)必要があります。
ただし、最近のヤフオクのシステム変更で操作にFlashやJavaScriptが多用されるようになったのでいずれにせようまく動かすために解析するのは難しいかもしれません。
ご回答ありがとうございます。
データの埋め込みはWinHTTPがいいということですね^^。
参考にさせていただきます。
EXCEL の VBA を使用した例です。
ただ、この方法はサイトの形式が変わるとデータが取れなくなる欠点がありますし、
ページによって書式が違う場合もデータがとれません。
Option Explicit Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Sub getAuctionInfo() Dim objIE As Object Const startRow = 3 Set objIE = CreateObject("InternetExplorer.application") objIE.Visible = True Dim lastRow As Long lastRow = Range("A" & startRow).End(xlDown).Row Dim i As Long For i = startRow To lastRow browsAndGetInfo objIE, Cells(i, "A").Value, i Next objIE.Quit End Sub Sub browsAndGetInfo(objIE, url As String, num As Long) objIE.Navigate url While objIE.ReadyState <> 4 While objIE.Busy = True DoEvents Sleep 300 Wend Wend Cells(num, "B").Value = objIE.Document.getElementsByTagName("H1").Item(0).innertext Cells(num, "C").Value = objIE.Document.getElementById("modCtgPath").innertext Dim es As Object Dim ls As Variant Dim ll As Variant Dim ws As Variant Dim i As Long '----------------------------------- Set es = objIE.Document.getElementsByTagName("h3") For i = 0 To es.Length - 1 If es.Item(i).innertext = "出品者の情報" Then ls = Split(es.Item(i).ParentNode().innertext, vbNewLine) For Each ll In ls If InStr(ll, ":") > 0 Then ws = Split(ll, ":") Select Case Trim(ws(0)) Case "出品者" Cells(num, "C").Value = Replace(ws(1), "(自己紹介)", "") End Select End If Next End If Next '----------------------------------- Set es = objIE.Document.getElementsByTagName("th") For i = 0 To es.Length - 1 If es.Item(i).innertext = "現在の価格" Then ls = Split(es.Item(i).ParentNode().ParentNode().innertext, vbNewLine) For Each ll In ls If InStr(ll, ":") > 0 Then ws = Split(ll, ":") Select Case Trim(ws(0)) Case "現在の価格" Cells(num, "E").Value = Replace(ws(1), "(自己紹介)", "") Case "即決価格" Cells(num, "F").Value = ws(1) Case "残り時間" Cells(num, "G").Value = Replace(ws(1), "(詳細な残り時間)", "") Case "入札件数" Cells(num, "H").Value = Replace(ws(1), "(入札履歴)", "") End Select End If Next End If Next '----------------------------------- Set es = objIE.Document.getElementsByTagName("p") For i = 0 To es.Length - 1 If es.Item(i).innertext = "詳細情報" Then ls = Split(es.Item(i).ParentNode().innertext, vbNewLine) For Each ll In ls If InStr(ll, ":") > 0 Then ws = Split(ll, ":") Select Case Trim(ws(0)) Case "開始時の価格" Cells(num, "I").Value = ws(1) Case "落札者" Cells(num, "J").Value = ws(1) Case "開始日時" Cells(num, "K").Value = ws(1) Case "終了日時" Cells(num, "L").Value = ws(1) Case "オークションID" Cells(num, "M").Value = ws(1) End Select End If Next End If Next End Sub
ご回答ありがとうございます。
試してみたいと思います。
ほとんど先の回答と同じコードですが、出品者の情報をとれるように改良しました。
(若干の処理の改良もしています。)
こちらでは落札者は出ていますが、出ていませんか?
出ていないのは、即決価格ですが、これは元のページに情報がないためです。
表示されているのに情報が取れない場合は、対象とした URL を提示ください。
Option Explicit Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Sub getAuctionInfo() Dim objIE As Object Const startRow = 3 Set objIE = CreateObject("InternetExplorer.application") objIE.Visible = True Dim lastRow As Long lastRow = Range("A" & startRow).End(xlDown).Row Dim i As Long For i = startRow To lastRow browsAndGetInfo objIE, Cells(i, "A").Value, i Next objIE.Quit End Sub Sub browsAndGetInfo(objIE, url As String, num As Long) objIE.Navigate url While objIE.ReadyState <> 4 While objIE.Busy = True DoEvents Sleep 300 Wend Wend Cells(num, "B").Value = objIE.Document.getElementsByTagName("H1").Item(0).innertext Cells(num, "C").Value = objIE.Document.getElementById("modCtgPath").innertext Dim es As Object Dim ls As Variant Dim ll As Variant Dim ws As Variant Dim i As Long '----------------------------------- Set es = objIE.Document.getElementsByTagName("th") For i = 0 To es.Length - 1 If es.Item(i).innertext = "出品者" Then ls = Split(es.Item(i).ParentNode().innertext, vbNewLine) For Each ll In ls If InStr(ll, ":") > 0 Then ws = Split(ll, ":") Select Case Trim(ws(0)) Case "出品者" Cells(num, "D").Value = Replace(ws(1), "(自己紹介)", "") End Select End If Next Exit For End If Next '----------------------------------- Set es = objIE.Document.getElementsByTagName("th") For i = 0 To es.Length - 1 If es.Item(i).innertext = "現在の価格" Then ls = Split(es.Item(i).ParentNode().ParentNode().innertext, vbNewLine) For Each ll In ls If InStr(ll, ":") > 0 Then ws = Split(ll, ":") Select Case Trim(ws(0)) Case "現在の価格" Cells(num, "E").Value = Replace(ws(1), "(自己紹介)", "") Case "即決価格" Cells(num, "F").Value = ws(1) Case "残り時間" Cells(num, "G").Value = Replace(ws(1), "(詳細な残り時間)", "") Case "入札件数" Cells(num, "H").Value = Replace(ws(1), "(入札履歴)", "") End Select End If Next Exit For End If Next '----------------------------------- Set es = objIE.Document.getElementsByTagName("p") For i = 0 To es.Length - 1 If es.Item(i).innertext = "詳細情報" Then ls = Split(es.Item(i).ParentNode().innertext, vbNewLine) For Each ll In ls If InStr(ll, ":") > 0 Then ws = Split(ll, ":") Select Case Trim(ws(0)) Case "開始時の価格" Cells(num, "I").Value = ws(1) Case "落札者" Cells(num, "J").Value = ws(1) Case "開始日時" Cells(num, "K").Value = ws(1) Case "終了日時" Cells(num, "L").Value = ws(1) Case "オークションID" Cells(num, "M").Value = ws(1) End Select End If Next Exit For End If Next End Sub
ご回答ありがとうございます。
実行してみたのですが出品者のところだけ取得できない状況です。
落札者のところは情報が取得できました。
エクセルに問題があるかもしれないので、私のエクセルをアップさせていただきました。
http://oskuni7.sakura.ne.jp/hatena/question8/excel.xls
お時間があるときでよろしいですので確認できましたらよろしくお願いいたします。
ご回答ありがとうございます。
試してみたいと思います。