自動化厨のプログラミングメモブログ │ CODE:LIFE

Python/ExcelVBA/JavaScript/Raspberry Piなどで色んなことを自動化

トップ > VBA

For文での線形探索より高速 VBAで連想配列 Dictionaryオブジェクト

VBA VBScript Excel

以下のような検索キー列がユニークであるテーブルからデータを取得したい（VLOOKUPみたいなイメージ）場合、処理回数が多いと WorksheetFanction.Vlookup やFor文での線形探索では時間がかかり過ぎる。

そんなときに役立つのが連想配列（Dictionaryオブジェクト）。

配列のようにインデックス番号からデータを取り出すのではなくキーの完全一致で要素を取り出せるので、処理が理解しやすいかつ高速であることが最大の利点。

f:id:maru0014:20200912124247p:plain

　

続きを読む

【VBA/VBScript】HTML特殊文字コードから16進数を抽出→10進数に変換してデコードする

VBA VBScript

CSV形式でダウンロードしたデータの一部が文字化けしていて困ってるという相談を受けて、「読み込み時のエンコード指定の問題でしょｗ」と見てみると部分的にHTMLエンコードされている謎のCSV・・・。

これに対応すべく部分的なHTMLエンコード文字をデコードして置換するスクリプトを作ってみました。

いろいろ調べてもHTMLエンコードかと思いきやURLエンコードの記事だったりして目標にたどり着くのにちょっと時間がかかってしまいましたが以下を発見。

[VBA] htmlデコード関数 | n218.info

シンプルで分かりやすく、これだ！と思ったけどそのままでは使えなかったの修正して使わせていただきました。

　

この記事で紹介している内容

  • テストのような数値文字参照のHTMLエンコードされた文字列をデコード
  • 文字列の中から変換対象を探し出してデコード後の文字列に置換する関数
  • ドラッグアンドドロップでCSVファイル内のHTMLエンコード文字列をデコードして置換するVBS

　

続きを読む

【ExcelVBAでスクレイピング入門】Googleで検索順位を自動取得してみる③ ～複数キーワードの検索とデータ取得～

Excel VBA

第二回では1キーワードを検索してExcelシートにキーワード、検索順位、ページタイトルを入力するところまでできました。

code-life.hatenablog.com

code-life.hatenablog.com

今回やっていくのは以下の内容。ここまでできれば検索結果のスクレイピングとしては十分な機能かな。

  • リンクURL、ディスクリプション
  • 複数のキーワードを検索
  • 2ページ目や3ページ目の結果も取得
続きを読む

【VBA】querySelectorAllで取得した要素に対してForEachを使うと謎のエラーが発生する

VBA Excel

この記事でできるようになること

  • querySelectorAllで取得したコレクションに対してループ処理

ExcelVBAでWebページから情報を自動収集（スクレイピング）する時に超便利なメソッド querySelectorとquerySelectorAll 。

CSSセレクタがそのまま使えて超便利だが、何故かquerySelectorAllに対してFor Eachを使ってループ処理を行うと処理を終えるタイミングで謎のエラーが発生して切断される事象に頭を悩ませていました。

f:id:maru0014:20190517222059p:plain
このWebページに問題があるため、Internet Explorerのタブを開き直しました

会社の人に相談しながらどうにか回避策を見つけたので共有したい。

続きを読む

【ExcelVBAでスクレイピング入門】Googleで検索順位を自動取得してみる② ～キーワードを検索して結果をシートに書き込む～

Excel VBA

第一回ではGoogleを表示するところまででしたが、今回はスクレイピングの肝となるデータを抜き出す部分です。

もうちょっと間空けて投稿しようかと思ったけど勢いで公開。ここらへんまでやらないと面白くないもんね。

code-life.hatenablog.com

  • 前回までのおさらい
  • キーワードを検索する
    • プログラムにキーワード検索する部分を追加する
  • 検索結果から情報を取得する
    • Googleの検索結果から情報を取得するには
  • 取得した情報をMsgBoxで表示するよう書き加える
  • 要素を探す前にページの読み込みを待たないとエラーになる　
  • Excelのシートに連続で入力していく
    • 処理の流れを確認
    • 16-17行目 querySelectorAll
    • 23-25行目 For～Next文
    • 19-21行目 シートの記入開始行番号
    • 27-34行目 シートへの記入
  • まとめ

前回までのおさらい

前回のプログラムの処理フローを再確認します。

  1. IEを準備する
  2. IEを表示する
  3. Googleを開く
  4. 3秒まつ
  5. IEを閉じる

これらの動きをVBAで書いたものが以下のプログラムです。

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub web_scraping()

    Dim oIE As InternetExplorer 'oIEという変数はInternet Explorerが入ると宣言
    Set oIE = New InternetExplorer 'oIEに新しいIEを起動して定義
    
    oIE.Visible = True 'IEを表示させる
    oIE.Navigate2 "https://www.google.co.jp/" 'URLにアクセス
    
    slepp 3000 '3秒待機
    
    oIE.Quit 'IEを閉じる
    
End Sub

これに「Googleでキーワードを検索する」「検索結果が表示されるのを待つ」「検索結果から情報を取得」「取得した情報をシートに書き込む」という処理を加えていきます。

続きを読む