Fippiyのプログラム学習内容アウトプットBlog

日々の学習内容をアウトプットして振り返りを実施する。

VBAプログラム開発、スクレイピング・ログイン機能【2-4】ログインプロシージャを既存VBAに適用する

VBA Excel

ログインプロシージャ化が完了したので、いよいよ既存のVBAコードへ反映させてログイン機能付きの処理とします。

これが完成すれば、毎回手動でログインしていた手間がなくなります。

今回の目的

ログインプロシージャを書籍登録VBAに組み込む

なぜやるか

書籍登録VBAを動作させるだけでログイン処理から自動的に実施できるようにするため

やりたいこと

  • ログインプロシージャを既存VBAへ組み込む
  • 引数として必要な変数を既存VBA側で準備する
  • Call呼び出しによるログインができることを確認する
  • 既存VBAの変数を全体的に見直す
  • 配列設定を見直す
  • 処理サイトアクセス毎にCall呼び出しを入れる

やったこと

  • プロシージャ組み込み順序を検討する
  • ログインプロシージャをコピーして扱えるようにする
  • 引数として設定している変数を用意する
  • ログインができることを確認する
  • 既存VBAの変数を見直す
  • 配列を再定義する
  • 処理サイトアクセスにCallを使う

実施内容 

ログイン処理をどのように組み込むか 

テスト用のメインプロシージャとログインプロシージャという形でログイン処理を作成していました。

ログイン処理はそのまま使用し、既存VBAからCallを使用して呼び出して処理できれば今回の目的は達成できます。

作成済みのVBAはいくつかありますが、今回は複数のISBNコードを読み取って書籍情報を登録するVBAにログイン処理を追加していきます。

 

組み込み手順

いきなり全て組むのではなく、処理できることを確認しながら組み込んでいきます。

  1. ログインプロシージャをコピー
  2. 初回ログイン用に呼び出せる位置へCall配置
  3. 引数に対応する変数を設定
  4. ログインができることを確認

まずこの工程ができれば、書籍登録VBAを動作させると、最初にログインが実施できるようになります。

ここまでを実装して、その後に全体的な変数の見直し等も行っていきます。 

 

ログインプロシージャを組み込む

作成済みプロシージャを適用する

まずは手順1.2を行います。

プロシージャをコピーして、Callで呼び出せる形を記述します。

# 書籍登録プロシージャ（ログイン機能をコピー）

Sub inputSomeBookdataISBN()

　'オブジェクト設定
　　'IE
　　Dim objIE As InternetExplorer 'IEオブジェクトを準備
　　Set objIE = CreateObject("Internetexplorer.Application") '新しいIEオブジェクトを作成してセット
　　objIE.Visible = False 'IEを表示
　　'HTML
　　Dim htmlDoc As HTMLDocument 'HTML全体
　　'作業ワークシート
　　Dim ISSheet As Worksheet 'ISBNWorksheet
　　Set ISSheet = ThisWorkbook.Worksheets("ISBN")
　　'登録ISBN
　　Dim InputISBN As Collection 'データ取得
　　Set InputISBN = New Collection
　　Dim ISBNAllCount As Integer 'ISBN総数
　　Const LimitEntry As Integer = 20 'フォーム入力ISBN上限
　　Dim EntryISBN() As String 'フォーム入力用csv(上限毎)
　　Dim MaxRepeat As Long 'ISBN処理回数
　　Dim LastISBNCount As Integer '最終ISBN件数
　　Dim ElementCounter As Long '要素取得カウンタ
　　'データ取得URL
　　Dim InputISBNPage As String
　　InputISBNPage = _

　　　"https://protected-fortress-61913.herokuapp.com/book/isbn_some_input"
　　'繰り返し処理
　　Dim i As Integer
　　Dim j As Integer
　　i = 2
　　'処理完了メッセージ
　　Dim ExitMsg As String

　'2.ログイン確認
　Call CheckLogin(objIE, htmlDoc, Domain, ProcessDir, CheckFirstLogin)

　～ 書籍登録処理 ～

End Sub

 

Sub CheckLogin()

　～ 1.ログインプロシージャ（そのままコピー） ～

End Sub

 

まずはCheckLogin()プロシージャをそのままコピーして貼り付けます。

そして、書籍登録VBAで呼び出せるようにします。呼び出すのは書籍登録処理を行う前です。Call CheckLogin()とすれば呼び出せます。

ログイン処理は書籍登録の処理を実施する前に行います。そのため他の処理より手前に配置する必要があります。

しかし、Callによる呼び出しを行う前に変数と値は準備しておく必要がありるので、変数宣言の直後で呼び出せるようにしました。

 

Call先が動作するように変数を用意する

しかし、これだけではまだ動きません。

呼び出し時の引数が書籍登録VBAに全て入っていればいいのですが、今回設定が足らないので追加で宣言する必要が有ります。

まずは単純に足らない値を追加設定してとにかく動かします。

# 書籍登録プロシージャ（URL関連変数宣言編集）

'データ取得URL
Dim Domain As String
Dim ProcessDir As String
Domain = "https://protected-fortress-61913.herokuapp.com/"
ProcessDir = "book/isbn_some_input"
Dim InputISBNPage As String
InputISBNPage = Domain & ProcessDir
InputISBNPage = "https://protected-fortress-61913.herokuapp.com/book/isbn_some_input"
'初回ログインチェック
Dim CheckFirstLogin As Boolean
CheckFirstLogin = True

URLに係わる設定を編集しました。編集と言うよりほぼ追加していますが…。

書籍処理ページURLを直接指定していましたが、Domain,ProcessDirの2つに分割しました。

また、ログインプロシージャ動作が初回かどうかをチェックする変数がありましたので、追加しています。

 

そして、InputISBNPageについては、元々URL全体を直接指定していました。今回ドメイン名＋ディレクトリ構成ですので、併せて記述を変更しました。

 

VBAを動作させる 

この状態でログインを含む全処理が行えるかを確認します。

元々の変数は結果的にInputISBNPageしか変更していないので、ログイン後の処理まで完了できる…はずです。

f:id:Fippiy:20190704155729j:plain

書籍情報取得

結果としては、全ての処理が完了しました。

ログアウト状態であることを事前に確認しておきVBAを動作させます。

そして、ログイン＋書籍登録を全て完了させることができました。

 

ひとまず、書籍登録処理にログイン処理を追加はできました。

 

既存VBAの変数を見直す 

順序や処理内容を考慮したコードへ変更する

ログインプロシージャ作成時に、初回ログイン時のチェックとその後の処理時にログイン状態チェックをできることを考慮していました。

このように、状況に応じた処理を今回の書籍登録VBAで確認しつつコードの再配置や名前の変更を行っていきます。

 

変数宣言の見直し

まずは書籍登録VBAの変数宣言を見直します。

現状の宣言状況を確認します。

# 書籍登録プロシージャ（変数編集前）

Sub inputSomeBookdataISBN()

　'オブジェクト設定
　　'IE
　　Dim objIE As InternetExplorer 'IEオブジェクトを準備
　　Set objIE = CreateObject("Internetexplorer.Application")
　　objIE.Visible = False 'IEを表示
　　'HTML
　　Dim htmlDoc As HTMLDocument 'HTML全体
　　'作業ワークシート
　　Dim ISSheet As Worksheet 'ISBNWorksheet
　　Set ISSheet = ThisWorkbook.Worksheets("ISBN")
　　'登録ISBN
　　Dim InputISBN As Collection 'データ取得
　　Set InputISBN = New Collection
　　Dim ISBNAllCount As Integer 'ISBN総数
　　Const LimitEntry As Integer = 20 'フォーム入力ISBN上限
　　Dim EntryISBN() As String 'フォーム入力用csv(上限毎)
　　Dim MaxRepeat As Long 'ISBN処理回数
　　Dim LastISBNCount As Integer '最終ISBN件数
　　Dim ElementCounter As Long '要素取得カウンタ
　　'データ取得URL
　　Dim InputISBNPage As String
　　InputISBNPage = _

　　　"https://protected-fortress-61913.herokuapp.com/book/isbn_some_input"
　　'繰り返し処理
　　Dim i As Integer
　　Dim j As Integer
　　i = 2
　　'処理完了メッセージ
　　Dim ExitMsg As String

　'ログイン状態チェック
　Call CheckLogin(objIE, htmlDoc, Domain, ProcessDir, CheckFirstLogin)

　～ 書籍登録処理 ～

End Sub

今まではログインを手動で実施後に書籍登録するのを前提としていました。今回ログイン処理が加わったことで処理順序が変わっています。

 

いままでの処理順序は…

※手動でログイン済み

  1. ワークシートからISBN読み取り
  2. カンマ区切り作成
  3. フォーム入力
  4. 結果出力

 

ログイン機能追加後は…

  1. ログイン実施
  2. ワークシートからISBN読み取り
  3. カンマ区切り作成
  4. フォーム入力
  5. 結果出力

ログイン機能を追加しているので、一番最初にログイン実施という機能が追加されています。

 

今回、ログイン機能がついているので仮にログインに失敗すると残りの処理はできません。

しかし、今のままだとログイン実施前にログイン後に実施する変数を宣言したり値をいれています。ワークシート名やワークシートから取得する値を格納する変数などがそうです。

早い話が失敗して不要になるかもしれない情報を先に準備しているのです。無駄になる可能性があります。 

 

そこで順序を全体的になおしました。

 

# 書籍登録プロシージャ（変数編集後）

Sub inputSomeBookdataISBN()

　'===↓VBA全体オブジェクト設定↓===
　　'IEオブジェクト
　　Dim objIE As InternetExplorer 'IEオブジェクトを準備
　　Set objIE = CreateObject("Internetexplorer.Application") '新しいIEオブジェクトを作成してセット
　　objIE.Visible = False 'IEを表示
　　'HTMLオブジェクト
　　Dim htmlDoc As HTMLDocument 'HTML全体
　　'データ取得URL
　　Dim Domain As String 'Webドメイン名
　　Dim ProcessDir As String '処理実施ディレクトリ
　　Domain = "https://protected-fortress-61913.herokuapp.com/"
　　ProcessDir = "book/isbn_some_input"
　　'VBA動作初回ログインチェック
　　Dim CheckFirstLogin As Boolean 'ログインチェックフラグ
　　CheckFirstLogin = True
　'===↑VBA全体オブジェクト設定↑===

　'ログイン状態チェック
　Call CheckLogin(objIE, htmlDoc, Domain, ProcessDir, CheckFirstLogin)

　'===↓処理用オブジェクト設定↓===

　　'作業ワークシート設定
　　Dim ISSheet As Worksheet 'ISBNWorksheet
　　Set ISSheet = ThisWorkbook.Worksheets("ISBN")
　　'登録ISBNコード取得設定
　　Dim InputISBN As Collection 'データ取得
　　Set InputISBN = New Collection
　　Dim ISBNAllCount As Integer 'ISBN総数
　　Const LimitEntry As Integer = 20 'フォーム入力ISBN上限
　　Dim EntryISBN() As String 'フォーム入力用csv(上限毎)
　　Dim MaxRepeat As Long 'ISBN処理回数
　　Dim LastISBNCount As Integer '最終ISBN件数
　　Dim ElementCounter As Long '要素取得カウンタ
　　'繰り返し処理
　　Dim i As Integer
　　Dim j As Integer
　　'出力メッセージ
　　Dim ExitMsg As String
　'===↑処理用オブジェクト設定↑===

まず初めにログイン処理として必要な引数をまとめて宣言し値を設定します。

そしてログイン状態を確認するためCallによる呼び出しを行います。

ログイン成功orログイン済みが確認できれば、処理は継続されるので、この段階で残りの必要な値を設定するようにしました。

 

配列宣言の見直し

VBAコードの扱いについていろいろ確認している中で、配列の扱い方を変更することにしました。

まずは、現状のコードから確認します。

# 書籍登録プロシージャ（ISBNコード処理）

'ワークシートからISBNコード取得
i = 2
Do Until ISSheet.Cells(i, 2).Value = ""
　InputISBN.Add ISSheet.Cells(i, 2).Value
　i = i + 1
Loop
ISBNAllCount = InputISBN.Count 'ISBNコード総数


'Web上限毎に登録処理をするカンマ区切りテキストを準備

　'ISBN処理回数算出
　MaxRepeat = Application.RoundUp(ISBNAllCount / LimitEntry, 0) '繰り返し回数
　LastISBNCount = ISBNAllCount Mod LimitEntry '繰り返しラスト取得件数
　ReDim EntryISBN(1 To MaxRepeat) '配列として要素指定して再宣言

　ElementCounter = 1 '要素取得カウンタ初期値

　'Web処理上限毎に処理できるようにする
　For j = 0 To MaxRepeat - 1
　　i = 1 '繰り返し変数初期化
　　'カンマ区切りテキスト生成(全ISBN or 上限件数まで)
　　Do
　　　EntryISBN(j) = EntryISBN(j) & InputISBN(ElementCounter) 'ISBNコードを要素として追加
　　　'処理上限orISBN総数ラストはカンマなし
　　　If ElementCounter <> ISBNAllCount And i <> LimitEntry Then EntryISBN(j) = _

　　　　EntryISBN(j) & ","
　　　ElementCounter = ElementCounter + 1
　　　i = i + 1
　　Loop Until i > LimitEntry Or ElementCounter > ISBNAllCount
　Next j

書籍登録VBAのISBNコード値取得のコードを再掲しました。

ISBN件数/登録上限を計算して、Web上で登録する回数を算出しておき、カンマ区切りテキストを数回に分けて登録処理していました。

 

仮に登録件数が50件、上限が20件だと、RoundUP(50/20)=3となり、3回の繰り返しとなります。

3回の処理を実施するのに対して用意される配列と3で指定すると配列に設定できる要素は3つできますが、通常配列は0からカウントするので「0～2」の配列要素が準備されます。

しかし、繰り返し処理回数3回であり1回目〜3回目として実行されるので、数値の扱が異なります。 

この為、いままでは0〜繰り返し回数-1を指定する方法としていました。

 

これを1〜繰り返し回数に変更します。

 

変更後のコードがこちらです。配列要素数の指定方法を変更しました。 

# 書籍登録プロシージャ（ISBNコード処理変更後）

'ISBN処理回数算出
MaxRepeat = Application.RoundUp(ISBNAllCount / LimitEntry, 0)
LastISBNCount = ISBNAllCount Mod LimitEntry
ReDim EntryISBN(MaxRepeat - 1)
ReDim EntryISBN(1 To MaxRepeat)

ElementCounter = 1

For j = 0 To MaxRepeat - 1
For j = 1 To MaxRepeat

　i = 1
　Do
　　EntryISBN(j) = EntryISBN(j) & InputISBN(ElementCounter)
　　If ElementCounter <> ISBNAllCount And i <> LimitEntry Then EntryISBN(j) = _

　　　EntryISBN(j) & ","
　　ElementCounter = ElementCounter + 1
　　i = i + 1
　Loop Until i > LimitEntry Or ElementCounter > ISBNAllCount
Next j

配列要素の宣言でReDim EntryISBN(1 To MaxRepeat)とすることで1～上限までとなるので、先程の例でいくと1～3が指定できます。0は宣言していないので要素として作成されません。

配列要素数と繰り返し数双方共に1～3となったので、これで-1等の対応は不要となり可読性もあがりました。

 

各ページアクセス時のチェック

ログイン状態確認後は、書籍登録を行う上でISBNコードを入力するフォームにアクセスすることになります。

ページ遷移が伴うので、この時タイムアウト等でログアウト状態となっていないか確認が必要となるため、ログインプロシージャを呼び出して状態確認をできるようにしていきます。

そして、ログイン状態確認後は接続サイトからHTMLを取得して処理を継続して行っていきます。

以上の内容を反映させてみました。 

# 書籍登録プロシージャ（フォームにカンマ区切りISBNを登録）

'カンマ区切りテキストを全て反映させる
i = 2 '結果出力テキスト挿入位置初期化

For j = 0 To MaxRepeat - 1
For j = 1 To MaxRepeat

　'フォームを開く
　objIE.navigate InputISBNPage 'IEでURLを開く
　Call WaitResponse(objIE) '読み込み待ち
　Set htmlDoc = objIE.document 'objIEで読み込まれているHTMLドキュメントをセット
　'ログイン状態チェック
　Call CheckLogin(objIE, htmlDoc, Domain, ProcessDir, CheckFirstLogin)

　'フォーム入力
　htmlDoc.getElementsByClassName("form-input__detail")(0).Value = EntryISBN(j)
　htmlDoc.getElementsByClassName("send isbn")(0).Click

　'フォーム結果HTML取得
　Call WaitResponse(objIE) '読み込み待ち
　Set htmlDoc = objIE.document 'objIEで読み込まれているHTMLドキュメントをセット

　'フォーム処理結果取得
　Call getISBNAnswers(htmlDoc, ISSheet, i)
Next j

'全件処理完了まで繰り返し

フォームに入力するデータ作成後の処理です。

 

まず、フォームにアクセスする必要があります。

今まではURLを指定してサイトを開き、HTMLデータを取得していました。

この処理はログインプロシージャで行います。

ログイン処理中に指定サイトは開きます。開いた後にURL状態を確認し、その後HTMLも取得された状態となります。

 

そして、先程登場した配列の値設定も修正していたので、併せて繰り返し処理も修正しています。

 

 

以上で書籍登録ISBNに対してログイン処理を追加できました。

このVBAのみ動作させればログイン処理＋書籍登録処理が一度に実施できるようになります。

 

最後に、コピーしたログインプロシージャ内で確認に使っていたデバッグコードを削除すれば完了です。