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

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

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

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

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

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

f:id:maru0014:20200912124247p:plain

 

続きを読む

Slackのログ(JSONファイル)をExcel PowerQuery だけでなるべく楽に解析

Slackのログを分析するときは管理画面から一括ダウンロードするのが早い。しかし、出てくるデータはチャンネルごとにフォルダがバラバラかつ日付ごとにバラバラのJSONファイルとして出てくる。

CSVならあまり頭を使わなくてもそのまま開いたり、日付ごとのファイルでもbatで統合するなりすればいいがJSONファイルをExcelで扱うにはPowerQueryが最適VBA-JSONを使う手もあるが、初心者向けではない。


手順概要

  1. Slackからごそっとデータをダウンロード
  2. ZIPファイルを解凍
  3. Excel のデータタブでデータの取得→ファイルから→フォルダから
  4. 自動生成された関数を手直し
  5. シートに読み込む

 

1. Slackからごそっとデータをダウンロード

Slackの管理画面https://<ワークスペース名>.slack.com/admin/settings にアクセスし、画面右上の「データのインポート/エクスポート」をクリック。

f:id:maru0014:20200912123159p:plain  

続きを読む

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

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

code-life.hatenablog.com

code-life.hatenablog.com

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

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

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

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

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

ExcelVBAでWebページから情報を自動収集(スクレイピング)する時に超便利なメソッド querySelectorquerySelectorAll

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

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

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

続きを読む

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

第一回では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でキーワードを検索する」「検索結果が表示されるのを待つ」「検索結果から情報を取得」「取得した情報をシートに書き込む」という処理を加えていきます。

続きを読む