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

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

【ExcelVBAでスクレイピング入門】Googleで検索順位を自動取得してみる① ~IEを開く、待つ、閉じるまで~

スクレイピングとは?

ウェブスクレイピング。ウェブサイトのHTMLから欲しい情報を指定して取得すること。

簡単に言うと手作業でウェブサイトからExcelにデータをコピペコピペコピペ・・・という地獄の作業を自動化してしまおうというものです。最近流行りの業務の自動化という考え方 RPA(Robotic Process Automation)にも通ずる というかRPAツール使わなくてもVBAでRPAできるってこと。

そもそもプログラム勉強するの嫌だけど仕事は自動化したいという人のために作られたのがRPAツールなので、プログラム勉強するモチベーションがある人は、是非とも仕事を自動化するプログラムを作ることに全力を注いでほしいです。(ただし、自分以外の仕事を自動化するという場合はメンテナンスが問題になるのでそこはご注意ください

 

さて、本連載ではmarumoの得意分野をさらに磨くべく、ExcelVBAでここまでできるんだぞと布教するべく、検索順位チェックツールのようなものを作っていきます。今回はデータ取得が主ですが、リンクをクリックするメソッドなどもあるのでWEBベースのシステムを使った業務であれば応用次第で割と簡単に業務自動化を実現できるかもしれませんね。

API使えや・・・という意見もあるかと思いますが、やっぱりブラウザ経由のスクレイピングは汎用性が高くて便利。あとJSONのパースとかしなくていい。APIキー取得しなくていい。APIで提供されていない項目も取得できる。 などの理由もあり今回はわかりやすい題材として検索順位を取得対象にしています。

APIでのデータ取得もそのうちやりたいな。楽天市場の検索APIとかね。

  • スクレイピングとは?
    • この連載記事でできるようになること
    • もしかしたら、おまけでやるかもしれないやーつ
  • 設計~Google検索するまで
    • やりたいことと処理の流れをイメージ
      • 処理フロー概要
    • VBAで書いていくよ
      • VBE(VBAを書くやつ)を起動してModule1を作成
      • ブックをxlsm形式で保存
      • 前準備(参照設定)
      • Internet ExplorerでGoogleを開く
      • Internet Explorerを閉じる
      • 「待つ」処理を追加する
        • sleepメソッドで3秒待つ
        • Waitメソッドで3秒待つ
    • まとめ
この連載記事でできるようになること
  • 指定したキーワードでGoogleの検索順位を一括取得
  • 次のページへのリンクをクリックしてページ移動しながら取得
  • スクレイピングに便利なCSSセレクタを知る
  • Seleniumの使いどころを知る

 

もしかしたら、おまけでやるかもしれないやーつ
  • 指定文字列を含むリンクタグを取得するFunction
  • JavaScriptを実行させる方法
  • 要素取得のリトライ処理(Seleniumみたいな使い勝手にしたい)

 

設計~Google検索するまで

第一回目では「Googleで検索する」ところまでをやっていきたいと思います。

一気に記事書くと説明が雑になりそう、後半力尽きて雑になりそう、飽きてきて雑になりそうなどの理由です。楽しくなってくるのは第二回目でシートに情報を書き込んで行くところからかなーと思います。

 

続きを読む

ExcelVBAからパスワード付き圧縮コマンド(7-zip/Lhaplus)を実行する

ExcelVBAからパスワード付き圧縮コマンド(7-zip/Lhaplus)を実行する

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

  • コマンドでファイルをパスワード付きZIP圧縮
  • ExcelVBAからcmd.exeにコマンドを渡す

毎日の作業を楽にするべくExcelVBAで作ったメール一括送信ツール。しかし、仕事で実用化するには添付ファイルにまつわる悪しき習慣"パスワード付きZIP"が立ちはだかる。

 

添付ファイルは何故パスワード付きzip?

ファイル送信直後にパスワードを記載したメールをもう一通送る。メールを不正に傍受する攻撃者がいたとしたら2通目も傍受しているはずであり、結局はパスワードを付けて暗号化していたとしてもパスワードを手に入れられたら無防備と変わらない。

しかし、調べてみると全く意味がないわけではないらしい。

 

一つ目はヒューマンエラー対策。メーラーのTo入力時に入力候補が出るような時、うっかり見た目が似ているアドレスに誤送信してしまうパターン。この場合にzipファイルを暗号化していれば受信者が悪意を持って解読しない限り、体裁上は暗号化していたので一安心となるわけである。

二つ目はPマークやISMS認証のためというもの。社外とデータをやり取りする場合は暗号化を施していないと審査に影響があるらしい。

 

以上のように全く意味がないわけではないが、送る方も受け取る方も手間のかかる作業であることに変わりはない。

参考:https://www.lrm.jp/mailzipper/attachment-encryption/

せめて送信者側の負担を軽減しましょうということでExcelVBAからパスワード付きZIP圧縮を実行する方法を紹介する。

続きを読む

【落書き】勢いでExcelVBA使って神龍Webテストを再現してみた

qiita.com

神龍何回名乗るんだよww と思いながらも面白そうだったのでチャレンジ。

VBAのClassモジュールは初めて使ったけど作り込んだら便利そう。

mainモジュール

Sub callDragon()

    Dim 我が名は神龍 As New ieDoragon
        我が名は神龍.URLにアクセスしてやろう "https://kids.yahoo.co.jp/"
        我が名は神龍.フィールドに入力してやろう "調べたいことばをいれてみよう!", "ねこ"
        我が名は神龍.クリックしてやろう "さがす", "button"
        我が名は神龍.テキストがあるか確認してやろう "ネコ - Wikipedia"
        我が名は神龍.入力フィールドに文字を追加してやろう "調べたいことばを入れてみよう!", " 買い方"
        我が名は神龍.クリックしてやろう "さがす", "button"
        我が名は神龍.テキストがあるか確認してやろう "さがしているのは"
        我が名は神龍.クリックしてやろう "猫の飼い方", "a"
        我が名は神龍.入力フィールドに文字が入っているか確認してやろう "調べたいことばを入れてみよう!", "猫の飼い方"
        我が名は神龍.フィールドに入力してやろう "調べたいことばを入れてみよう!", "不倫"
        我が名は神龍.クリックしてやろう "さがす", "button"
        我が名は神龍.テキストがあるか確認してやろう "このページは表示できません。"
        我が名は神龍.スクリーンショットを撮ってやろう ThisWorkbook.Path
        
End Sub
続きを読む

ExcelVBAでセルの文字列の折り返しを解除する方法.WrapText = False

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

  • セル内改行を含むエクセルのシートをVBAで折り返し解除する

セル内改行があると勝手にセルの文字列が折り返される

f:id:maru0014:20171128034043p:plain

VBAで商品ページデータなどを処理することが最近多いんですが、このようなHTMLを含むセルはCSVを読み込んだ段階で画像のように文字の折り返しが勝手に有効化されてしまいます。

これを読み込んだ直後にシート全体の文字の折り返しを解除するマクロがこれ

.WrapText = False

Sub Macro1
  Cells.WrapText = False
End Sub

これを実行すると以下のように文字列の折り返しが解除されます。

f:id:maru0014:20171128040957p:plain

ちなみに折り返し解除するだけでは手動でセルを編集すると、再度折り返しが有効化されて行の高さが変わってしまいます。

行の高さを固定するには直接シート全体の行の高さを指定します。

Sub Macro2
  Rows.RowHeight = 18
End Sub

たったこれだけのことですが、毎回手で高さ指定するのが面倒ならVBAでやってしまえば便利ですね。

シートの最終行番号を取得するユーザー定義関数 - VBA(エクセルマクロ)

最近仕事で受注・発注業務のオートメーション化をするにあたって、VBAで良く使う「シート内で利用している最終行番号を取得する」「特定のセルを基準に一番下のセルの行番号を取得する」ユーザー定義関数をメモとして残しておきます。

シート名を引数にユーザー定義関数を記述

Function maxRow(ByVal sheetName As String) As Integer 
    maxRow = Sheets(sheetName).UsedRange.Rows.Count
End Function

これで関数の定義はできました。

シートの名前を指定して関数を呼び出す

    Debug.Print maxRow("sheet1") 

定義した関数を呼び出すにはmaxRow()のカッコ内に最終行番号を取得したいシート名を入れる。

使用例

下記のようなワークシートで先程の関数を呼び出すと「5」が返されます。

f:id:maru0014:20170203220506p:plain

最終行まで間が空いていてもカウントされる

下記のような場合、6~8行目まで空白になっていますが9行目にデータが入力されているので戻り値は「9」となります。

f:id:maru0014:20170203221937p:plain

特定のセルを基準に一番下のセルの行番号を取得したい場合

先程のユーザー定義関数で最終行を取得する場合は行の途中で空白があっても飛び越えて一番下の行を取得します。 しかし、下記のようにA列の最終行番号を取得したいといった場合には別の方法を使いましょう。

f:id:maru0014:20170203222529p:plain

  今度はmaxRowCellという名前の関数を作ってみました。 シート名を指定した後ろが.range(rangeName).End(xlDown).Rowに変わっています。 こう記述すると指定したセルの列における最終行番号を返します。

Function maxRowCell(ByVal sheetName As String, ByVal rangeName As String) As Integer
    maxRowCell = Sheets(sheetName).range(rangeName).End(xlDown).Row
End Function

シートの名前とセル名を指定して関数を呼び出す

    Debug.Print maxRowCell("sheet1", "A1")

このように呼び出すとイミディエイトウィンドウには「5」と表示されます。

概ねこれらの2パターンで最終行の取得は可能なので、for文の繰り返し回数の指定などに応用すればかなり使い勝手のいい関数かと思います。

ついでなので、For文での繰り返し処理のやり方も書いておきます。

最終行まで繰り返し処理を行うFor文の書き方

下記のようなシートがあったとして、A列のアルファベットに応じてB列に1から順番に数字を入力していきたい場合。

f:id:maru0014:20170203224228p:plain

まずはA列の最終行番号を取得してB列の何行目まで入力すれば良いのかを計算します。

A列の最終行番号を取得したいのでmaxRowCellを使うことにします。

    For i = 1 To maxRowCell("sheet1", "A1")
        range("B" & i).Value = i
    Next

こんな感じですね。 For文の「i」は繰り返すごとに1づつカウントアップされていくのでrange("B" & i)と記述すれば繰り返すごとに B1→B2→B3 となっていくわけです。 そしてそのセルの中身に今回は数字を入れるだけなのでvalue = iで 1→2→3 と入力されていきます。

実行結果

f:id:maru0014:20170203225706p:plain

無事にB列に1~5の数字が入りましたね。

このようにシートや列の最終行番号を取得することでいろいろな繰り返し処理の変数へ当てはめることができるので、自動でデータの入力や数式の入力を行うマクロを作る際にはとても重宝しています。 ただ、もしこれよりも良い書き方などがあればコメントなどで教えてもらえると助かります。

 

↓これで勉強してます

たった1秒で仕事が片づく Excel自動化の教科書

たった1秒で仕事が片づく Excel自動化の教科書