vbaで 起動済みの IEを捕まえて、webページから必要な情報をゲットする。

マイクロソフトはもはやIEを使うなといっているが、以下のようなvbaでwebページを捕まえて、情報を抽出してみた。

Dim objIE As InternetExplorer
Dim objITEM As Object
Dim objTD As Object
Dim i As Long
Sub Getpage()
    Set objIE = Nothing
    Set objShell = CreateObject("Shell.Application")
    For Each objWindow In objShell.Windows
    If TypeName(objWindow.document) = "HTMLDocument" then 
        Set objIE = objWindows
        debug.print objIE.document.Title
        If objIE.document.Title = "page-title" then 
            objIE.document.forms("formname").param1.value = "KEYWORD"
            objIE.navigate "javascript:js_001;"   ' Execute javascript js_001
            Application.Wait Now + TimeValue(00:00:02")  '画面表示を2秒待つ
            For Each objTD In objIE.document.getElementsByTagName("tr")　　                   
                For Each objITEM In objIE.document.getElementsByTagName("a")
                '↑↑↑このgetElementsByTagNameで目的のaタグを手繰るところがミソ
                    If Instr(objITEM.outerHTML,"KEYWORD") > 0 then 
                     ' KEYWORDという文字列を持つaタグ（リンク）を探し、リンクをクリックすると、詳細ページが表示されるという構図。

　　　　　　　　　　　　　'このとき、debug.print objITEM.nameとかobjITEM.vakueとかで確認するのがコツです。

                        objITEM.click 'リンク（ボタン）をクリックする
                        Exit For
                    End If
                Next
            Next
            Application.Wait Now + TimeValue(00:00:02")　'画面表示を2秒待つ
    
            ' 上記コードをマネし、getElementsByTageNameでタグを手繰り、必要な情報をゲットする。
            ' HTMLページの解析には、IEの開発者タブF12を使う。
        End If
    End If
End Sub

これでいいかな

こうやって、RPA（robot process automation)をVBAの手作りで始められる。

冬の朝。まだ、日が昇る前に歩くのがいい。息は白い。

vbaでIEを起動し、webページから情報を抽出する。

マイクロソフトはもはやIEを使うな、edgeにしてくれといっているが、以下のようなvbaでIEを起動し、webページを巡回し、情報を抽出してみた。

Dim objIE As InternetExplorer
Dim objITEM As Object
Dim objTD As Object
Dim i As Long
Sub main()
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Visible = True
    objIE.Navigate2 "http://www.sample.com/index.html")
    Do While objIE.Busy = True Or objIE.readyState<>4 
        DoEvents
    Loop
    objIE.document.forms("form01").param1.Value = "KEYWORD"
    objIE.navigate "javascript:js_001;"   ' Execute javascript js_001
    Application.Wait Now + TimeValue(00:00:02")  '画面表示を2秒待つ
    For Each objTD In objIE.document.getElementsByTagName("tr")　　                   
      For Each objITEM In objIE.document.getElementsByTagName("a")
        '↑↑↑このgetElementsByTagNameで目的のaタグを手繰るところがミソ
        If Instr(objITEM.outerHTML,"KEYWORD") > 0 then 'KEYWORDという文字列を持つaタグを探し
          objITEM.click                                'リンク（ボタン）をクリックする
          Exit For
        End If
      Next
    Next
    Application.Wait Now + TimeValue(00:00:02")　'画面表示を2秒待つ
    
    上記コードをマネし、getElementsByTageNameでタグを手繰り、必要な情報をゲットする。
    HTMLページの解析には、IEの開発者タブF12を使う。
    
End Sub

　これでいいかな？

　途中にある以下のコードは、ページが完全に表示されるまでを待つコードとして、ググると出てくるコードだが、その機能はなさそうだ。

    Do While objIE.Busy = True Or objIE.readyState<>4 
        DoEvents
    Loop

　では、どうすればいいか？それは、表示されたページにあるはずの文字列KEYWORD2が出ているかを確認すればいい。こんな感じだ。文字列を間違えると、無限ループになるので、注意が必要だ。詳しくは、こちらで。EXCEL+VBAで仕事を効率化〜既に開いているIEページをつかまえる。InStr関数で確実に！

        Do While Instr(objIE.document.innertext,"KEYWORD2") = 0 then 'KEYWORD2という文字列が表示されていないならば
          Application.Wait Now + TimeValue(00:00:02")　'画面表示を2秒待つ
        End If

webページでテキストファイルのアップロードが失敗した。その理由は、そのwebシステムの改行コードがテキストファイルの改行コードとは異なるためだ。

　webページでテキストファイルのアップロードが失敗した。その理由は、そのwebシステムの改行コードがテキストファイルの改行コードとは異なるためだ。

　一般にwindows系のシステムでは、改行コードはCR+LFで2バイトだ（CRとはCarridge Returnの略で、値は0d。LFとはLine Feedの略で、値は0a）。一方、UNIX 系(Linux含む)のシステムの改行コードは、LFのみだ。CRだけのシステムもあるようだ。それゆえ、web系のシステムをlinuxで作ると、改行コードはLFとなる。

　それに対して、クライアントとして、windowsでアップロードするデータのテキストファイルをテキストディタとかvbaとかで作ると、通常、改行コードはCR+LFとなり、webシステムへのテキストファイルのアップロードがフォーマットエラーとなるわけだ。

　そうならないためには、改行コードを意識して、テキストファイルを作成する必要がある。

　たとえば、windowsクライアントのツールをvbaで作る場合、普通のprint　s(sはstringの文字変数)ではCR+LFが付くため、わざわざ、print s & vbLf; とするのだ。最後尾の;がCR+LFを抑止し、vbLfでLFのみを付与d切るのだ。

　テキストエディターの場合、そのソフトが改行コードを指定した保存をサポートしていれば、変更できる。

window系OSでテキストファイル（例えばa.txt）をバイナリ表示したければdosコマンドのdebugを使えば良い。

 windows系OSでwindowのテキストファイル（例えばa.txt）をバイナリ表示したければ、dosコマンドのコマンドラインでdebug a.txtと打ち、dでデータを16進表示できる。こんな感じだ。

debug a.txt
> d 
> nnnn:mmmm 31 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  1...............   

　ちなみにlinuxならばxxd ファイル名だ。
　ここで一息のコーナーです。味噌汁の出汁を取るのに「煮干し」を使っている。事前（例えば、前日）に煮干しのハラワタをとり、椎茸の粉末とともに水につけておく。そして、味噌汁を作るとき、５分ぐらい煮て、出汁を取る。困ったことは、出汁を取った後の出汁ガラの始末だ。捨てるのは、勿体ない。そこで、煮干しガラの佃煮にしている。鍋に煮干し出汁ガラを入れて、砂糖、醤油、みりん、唐辛子、しょうがを加えて、汁気がなくなるまで煮る。最後にゴマを散らして完成だ。とても旨い。

ある期間のデータを朝イチで自動取得するプログラムを作るとき、事前にその期間が決められない場合、どうすれば良いのか

　ある期間のデータを朝イチで自動取得するプログラムを作るとき、事前にその期間が決められない場合、どうすれば良いのか？

　それならば、毎日、データを取得し、その日付を付けたファイルにしておき、必要になったら、特定の日付のデータを選択し、分析なり、処理すればいい。

どんなパソコン作業でもこまめに保存することが実に大事だということに改めて気づく。

　EXCELにしてもACCESSにしても、いやどんなパソコン作業でもこまめに保存することこそが実に大事だということに改めて気づいだ。ある程度、文書なり、プログラムなり、データなりを蓄積したら、一旦、名前をつけて、保存しよう。そうしないと、何時間もかけて、積み上げた成果が一瞬で、消える。ワタシの時間を返してということになる。消えたものはどうにもならに。パソコンの前で、呆然となる自分がいる。そうならないためには、こまめな保存だ。そして、版数を付けて、途中の版も保存しよう！これこそが仕事をうまくこなすワザだ。こういう、常時保存を自動でやってくれる機能がパソコンにあるといいンだけどね。

　ここでちょっと一息のコーナー。昨日、米粉と餅粉にミルク、卵、ベーキングパウダー、しお、バターでパンケーキを作ってみた。ベーキングパウダーの量が少なかったのか、ふっくらとできなかった。次回は、ベーキングパウダーを増やし、リベンジだ。バターは重いので、オリーブオイルにしてみるか。水分も少なめにして、もっとふっくらとさせよう。

vbaでファイルのオープン・クローズを繰り返すとメモリ不足でEXCELがコケた。

　同じフォーマットのEXCEL シート（ファイル）をマージ（統合）するvbaコードで、たくさんファイルを読ませると、メモリ不足でEXCELがコケた。
　ググってみたら、マイクロソフトのバグ らしく、ファイルのオープン・クローズを繰り返すだけで、マイクロソフトはメモリ解放のタイミングを逃してしまうようだ。
　例によって、それが仕様なのでしょう。これを回避するには、closeの後にDoEventsという呪文をかけておかなくてはならないようだ。

Optoon Explicit
Dim maxRow As Long
Dim filePath As string
Dim fileName As String
Dim i,j,k As Long
Sub MergeALL()
    j = 1  ' the row counter of TO(COPY)
    filePath = ThisWorkbook.Path & "¥"
    fileName = Dir(filePath & "*DATA*.xlsx") ' Get a file to be merged
    Do While fileName <> "" ' Is there a file to be merged ? 
        Workbooks.Open Filename :=filePath & fileName
        maxRow = ActiveWorkbook.Worksheets(1).Cells(Roes.Count,3).End(xlUp).Row
        If j = 1 then k = 1    ' k: the row counter of FROM(COPY)
        If j > 1 then k = 3
        m = maxRow - ( k - 1 ) ' m: the numbers of lines to be copied
        ActiveWorkBook.Worksheets(1).Range(Rows(k),Rows(maxrow)).Copy
        ThisWorkBook.Worksheets("まとめ").Activate
        ThisWorkbook.Worksheets("まとめ").Range(Rows(j),Rows(j+m-1).PasteSpecial (xlPasteAll)
        application.CutCopyMode = False
        j = j + m
        Workbooks(fileName).Close
        DoEvents   ' give a chance to release memory area
        fileName = Dir() ' Get the next file to be merged
    Loop
End Sub

こんな感じで、今度こそいいと思う。たぶん。ここで、一息のコーナーです。NHKの100分で名著で、ル・ボンの「群集心理」を観た。政治家やメディアは、しばしば、精緻な論理などを打ち捨て、「断言」「反復」「感染」という手法を使って、群衆たちに「紋切り型のイメージ」「粗雑な陰謀論」「敵-味方の単純図式」を流布する。まさに、そのとおりだなと感じた。誠に、空恐ろしい。

同じフォーマットのEXCEL シートをマージ（統合）するvbaコードを書いてみた。

　同じフォーマット（1行目と2行目がタイトル行で、データは3行目から始まると仮定、ファイル名にDATAという文字列が含まれているものとする）のEXCEL シートをマージ（統合）するvbaコードを書いてみた。マージするファイルはマクロブックと同じフォルダにあるとものとする。使うのは、DIR関数とCopy,Pasteメソッドで、それをDo WhileでLoopさせる。とてもシンプルだ。このツールは自身のシート（”まとめ”）にマージした結果を抱え込む。menuシートでマクロ起動ボタンを作り、マクロ登録すれば、完成だ。

Option Explicit
Dim maxRow As Long
Dim filePath As string
Dim fileName As String
Dim i,j,k As Long
Sub MergeALL()
    j = 1  ' the row counter of TO(COPY)
    filePath = ThisWorkbook.Path & "¥"
    fileName = Dir(filePath & "*DATA*.xlsx") ' Get a file to be merged
    Do While fileName <> "" ' Is there a file to be merged ? 
        Workbooks.Open Filename :=filePath & fileName
        maxRow = ActiveWorkbook.Worksheets(1).Cells(Rows.Count,1).End(xlUp).Row
        If j = 1 then k = 1    ' k: the row counter of FROM(COPY)
        If j > 1 then k = 3
        m = maxRow - ( k - 1 ) ' m: the numbers of lines to be copied
        ActiveWorkBook.Worksheets(1).Range(Rows(k),Rows(maxrow)).Copy
        ThisWorkBook.Worksheets("まとめ").Activate
        ThisWorkbook.Worksheets("まとめ").Range(Rows(j),Rows(j+m-1)).PasteSpecial (xlPasteAll)
        application.CutCopyMode = False
        j = j + m
        Workbooks(fileName).Close
        fileName = Dir() ' Get the next file to be merged
    Loop
End Sub

こんな感じになると思う。