IE+VBAで仕事を効率化 Webページのラジオボタンを操作（チェック）する

　社内システムのWebページからデータを取ってくる際のノウハウです。
　Webページに「番号のリスト」があり、その番号に対する内容を「表示する」といったケース（例えば、伝票番号に対する現在のステータスなどの詳細情報を表示する）で、登場するのが「ラジオボタン」ですね。番号の隣に「二重丸◎の形状で選択すると真ん中が黒くなる」ボタンです。それを選択（クリック）して、そのページのどこかにある「表示」ボタンをクリックして、詳細情報を表示するような感じになります。
　このラジオボタンをVBAで操作する方法を説明します。
ラジオボタンは、INPUTタグで作られますので、まず、このINPUTタグに付けられたnameが何かを探します。 見つけたら、そのオブジェクトに対して、ObjXX.Checked = Trueとすれば、二重丸の黒丸が入り、「選択」（チェック）された状態になります。
  この例では、テーブルのエントリは６個のTDタグで構成され、 最初のエントリにラジオボタンのINPUTタグもあり、 "radio-button-name" という名前を持つとします。テーブルは、複数のエントリで構成されます。（例えば、伝票番号の数だけのエントリがあります。）
mは、テーブルのエントリ（６個のTDタグを持つ）をカウントするために使用します。
mmは、TDタグの最大個数をカウントするために使用します。
nは、TDタグをくるくると回すために使用します。
　GetElementsByTagNameでタグを取ると、すべてのTDタグが取れるので、くるくる回しながら、必要なタグをたぐる必要があるのです。
nnは、ラジオボタンを持つTDタグをカウント（探す）するのに使用します。
　まず、GetElementsByTagNameで現在のページのTDタグの最大数をmmにセットします。

mm = 0
For Each objTD In objIE.document.Frames("bodys").document.GetElementsByTagName("TD")
    mm = mm + 1
Next

　次に、再び、GetElementsByTagNameでTDタグをたぐりながら、テーブルのエントリごとに伝票番号のラジオボタンを選択し、その伝票番号の内容を表示するために「表示」ボタンをクリックし、表示のページに飛んで、情報を抜いて参ります。

m = 1
Do 
  n = 0
' *** turn a radio button on ***
  For Each objTD In objIE.document.Frames("bodys").document.GetElementsByTagName("TD")
     n = n + 1
     nn = 0
    For Each objITEM in objTD.document.getbyTagName'("INPUT")
      If objITEM.Name = "radio-button-name" Then
        nn = nn + 1
        If nn = m then
          objITEM.Checked = True　　　　　　　' radio button on
          Exit For
        End If
      End If   
      Next
      If n ＜ ( m * 6 ) Then GoTo Continue　’  skip　
      If ( m * 6 ) > mm then GoTo FINISH                      ' the last TD?
      If n Mod 6 = 0 Then Exit For　            '  6 * n 
Continue:
    Next
    m = m + 1   
  Next

ここにラジオボタンに黒丸をした後にやりたいこと。例えば、表示ボタンを押し、 表示されたページを捕まえて、テープルから情報を抜いてくる処理を記述します。 例えば、６番目のタグに伝票番号が入っていたとして、それを取り込んで、 EXCELシートの名前にします。 ActiveWorkBook.Sheets.Add.Name = objTD.innertext ここの処理で、伝票番号をキーに伝票番号の詳細情報のページを表示したとき、元の画面が残らずに、詳細ページへジャンプするようなシステムであれば、伝票番号の詳細情報を抜いた後に、最初の伝票番号のリストのページへ復帰する必要があります。 最初の伝票番号のリストのページ名が”伝票システムの番号リスト”であれば、 そのページへ戻るコードは、以下。

For Each objWindow In objShell.Windows
  If TypeName(objWindow.document) = "HTMLDocument" Then
    Set objIE = objWindow
    If objIE.document.title = "伝票システムの番号リスト” then Exit For
  End If
Next

なお、objShellは、IEの初期化で
URL01 = "http://www.伝票システムのURL"
URL02 = "http://www.伝票システムのURL/login.jsp"
Set objIE = CreateObject("InternetExplorer.Application)
objIE.Visible = True
objIE.Navigate URL01
Application.Wait (Now + TimeValue(0:00:03"))
Set objIE = Nothing
Set objShell = CreateObject("Shell.Application")
Set objIE = ObjShell.windows.Item(objShell.windows.Count -1 )
objIE,Navigate URL02
Application.Wait (Now + TimeValue(0:00:03"))
Do While objIE,Readystate <> 1
Do While objIE,busy = True
Loop
Loop

という感じで設定されております。 　実は、今回のノウハウのキーとなるIEでのラジオボタン操作は、ネットで探して、コピペして、作りました。TDタグを全部取って、クルクルしなくてもできると思うのですが、できればいいんですよね。特に、TDタグの最大数を取ってくるところは、もっとスマートな方法があるとは思いますが、できればいいンですねー。 クルクルしながら、ラジオボランを順に回すところは、ブレークポイントを駆使して、ちゃんと動いているか確認。とても泥臭いプログラミング、デバッグ手法でした。 　ここでちょっと一息のコーナーです。 　いのうえ　ひさしの言葉から（これを目指してます。出来ていないけど）。 　　難しいことをやさしく 　　やさしいことを深く 　　深いことを愉快に 　　愉快なことをまじめに書くこと。

仕事で覚えたEXCEL小技 番外編〜必ず、名前と版数を付けて、こまめにセーブする

　仕事でEXCELでいろいろ作ったときは、必ず、名前と版数を付けてセーブしておく。
    途中結果でもバリエーションができそうなら、「こまめ」に版数を付けてセーブする。「神経質なくらいにセーブ。」これが、ワタシの全く泥臭いですが、EXCELで仕事するときの基本のキホン。いわば、鉄則です。
　とは言いながら、ときどき、忘れてしまうこともあり、運悪く、一度もセーブせずに、数時間の作業に及んだ大作がおじゃんになることもありますが・・・。大作のときに限って、不幸なことになりがち。

　ここでひといきのコーナー。

　SCIENTIFIC AMERICAのfebruary 2001で紹介されたセールストークのコツ。
　ホテルの予約係がお客に「変更があったら、電話を入れてください」といっていたのを、「変更があったら、電話を入れていただけますか？」で、お客に返事してもらうようにしたところ、無断キャンセルが減ったという話が載っていました。
　パブリックコミットメントという手法とのこと。人は自ら言ったことは守りたいと思うようです。
　なるほど、やっぱり、相手に返事してもらう。意思表示をしてもらうことはとても効果があることなんですね。




   
　

仕事で覚えたEXCEL ピボット（PIVOT）でクロス集計する

　ピボットって何よ？というレベルで仕事でEXCEL を覚えたワタシですが、なかなか使いこなせてるとまでは言えない。
　一言でいえば、クロス集計ですが、どういう便利さがあるのかな ぁ？

　例えば、EXCELで家計簿を作成したとします。
　といっても、買った品物ごとに入れるのも大変なので、店ごとというか、レシートごとに日付、店名（品名）、金額が１行で入れてあるものとします。このとき、費目として、食費、教養・娯楽、光熱・水道、教養・娯楽、その他などを入れておくと、費目ごとの集計がピボットでできるようになります。

  まず、家計簿の行を選択して、「ツール」タブからピボットの作成ウィザードを起動します。

　こんな感じですね。

ピボットにする範囲を選択します。

必要な項目を選んで、例えば、費目ごとの月の出費がわかるようには、以下のようなピボットを作ります、

　
















　ここでちょっとひといきのコーナーです。

　先日、山梨に出かけたとき、道の駅「富士吉田」で富士山のおいしい水を汲んできました。富士山からの水の水汲み場があり、多くの方が自前の大きな容器を持参され、混雑しておりました。ワタシも売店で１００円で１．５リットルのペットボトルを購入して、富士山のおいしい水を持ち帰りました。翌朝、その水でコーヒーを入れたのですが、これがまた、とても旨かった。

るんるんモノマネEXCEL VBA 本〜仕事で覚えたEXCEL＆VBA小技 BOOK〜 キモの関数、そしてIEを自在に操るVBA。初めはモノマネが一番いい！

highschoolgirl

　自分のレベルにあった市販のEXCEL本。出会ったらいいね。でもそんな本はなかなか見つかりません。ワタシも幾つか買いましたが、仕事の効率化に役立つ、これぞという本に巡り会うことはできませんでした。それは、求めれば求めるほど離れていく恋人のようなものかもしれません。もっといいj本があるはずだ。明日こそ、見つかるかも。いや、こうなったら、自分で作るしかないなと。
　てなわけで、EXCELの必須関数や日常業務の自動化を実現するための「EXCEL VBA の始めかた」を書いてみました。
　最初の１歩の踏み出し方さえわかれば、あとは自転車と同じで、スイスイと、いや「どうにかこうにか」できるようになります。
　「買うより始めろ。習うより慣れろ。」
　仕事で苦しみもがき、何度も心が折れそうにながらも、必死で覚えた「１０年間のノウハウ」をすべて詰め込みました。
　仕事で覚えたEXCEL、渾身のEXCELノウハウ集（作品）です。
 　特に、「IEブラウザを自在に操るVBAテクニック」をマネしてください。動けばよいのです。動いたら、「勝ち」です。
　すべて、るんるんでモノマネに始まり、モノマネで終わってもいい。

はじめに　イチローのように・・・
第１章　キモの関数
１．１　SUMとSUBTOTALを使い分ける
１．２　ピボット（PIVOT）でクロス集計する
１．３　ブイルックアップ（VLOOKUP）する
１．４　VLOOKUPでは＄（ダラ）が必須だら 

１．５　SUMIFSとCOUNTIFSで集計する（プログラムレス）

第２章　IEを自在に操るVBA
２．０　VBAのはじめかた
２．１　VBAでIEを自在に操る
２．１．１　Webシステムへログインする
２．１．２　行きたいページへジャンプする
２．１．３　欲しい情報をゲットする
２．１．４　ラジオボタンを操作する
２．２　家計簿をつくる
２．２．１　 モノまねVBAで家計簿〜まずは、キホン設計！
２．２．２　 モノまねVBAで家計簿を作る〜その２ ユーザフォームでデータ入力する　
２．２．３　 ものマネVBAで家計簿～その３ ピボットで収支管理　これでバッチグー！

第３章　文字列を自在に加工する
３．１　複数のセルで特定の長さの文字列を生成する 
３．２　YYYYMMという文字列からQを抽出
３．３　文字列除去 
３．４　フィルタで〜以上が使えないときには

第４章　とても重宝する「小技（コワザ）」
４．１　見えてるセル（可視セル）のみへコピペする
４．２　いっぱい行をdeleteしたらexcelがコケたならば並べ替えて消せ

４．３　フィルター繰り返しつつ、特定の作業を行う（ autofilter とcurrentReagion ）

第５章　トラブルシューテング
５．１　視点を変えてみる
５．２　デバッグ  
５．３　トラブル原因は社内のセキュリティ強化ではないかも
５．４   そして、まずは、Windowsパッチを疑え   

付録１　VBAの出力メッセージをコピペする 
付録２　最終月曜日を求める
付録３　金額がプラスなら青字にマイナスなら赤字に（正規表現でちゃちゃと）

番外編　習得のコツは隣の師匠

　ここでちょっとひといきのコーナー。

　年を取り、引退（定年退職）すると、「教育」と「教養」が必要なのだそうです。そう聞くと、すごくハードルが高そうですが、
つまり、
・きょういく（今日、行く）ところがある。
・きょうよう（今日、用）がある。
というのが、よろしいようです。

仕事で覚えたEXCEL+VBA〜金額がプラスなら青字に、マイナスなら赤字に設定する

　EXCELで「プラス金額なら青字で、マイナス金額なら赤字で」わかりやく、色を付けたいことがある。
　そこで、EXCELで「選択」しているセル（複数かつ、点在していてもOK）について、
＋５百万であれば、青字で＋５百万円、−５百万円ならば、赤字で−５百万円と表示するVBAマクロは、以下のような感じ。

excergirl

Sub minusakaandplusaoHK()
Dim strPat As String
Dim strTest As String
'  -digits red
For Each r In Selection
  strTest =r.Value
  Set RE =CreateObject("VBScript:RegExp")
  Set Matches = RE.Execute (strTest)
  strPat = "(¥-|−)([0-9]|[０−９])+(.|．)? ([0-9]|[０−９]) *百万円"   --- pattern for search
  With RE
    .Pattern = StrPat
    .IgnoreCase =False
    .Global = True
  End With
  Set Matches = RE.Exceutes(strTest)
  If Matches.Count <> 0 then
    For Each Match In Matches
      r.Characters(Start:=Match.FirstIndex + 1,Length:=Match.Length).Font.ColorINdex = 3
  Next
  Endif
 Next r
'+digits blue
For Each r In Selection
  strTest =r.Value
  Set RE =CreateObject("VBScript:RegExp")
  Set Matches = RE.Execute (strTest)
  strPat = "(¥+|＋)[0-9]|[０−９])+(.|．)? ([0-9]|[０−９]) *百万円"   --- pattern for search
  With RE
    .Pattern = StrPat
    .IgnoreCase =False
    .Global = True
  End With
  Set Matches = RE.Exceutes(strTest)
  If Matches.Count <> 0 then
    For Each Match In Matches
      r.Characters(Start:=Match.FirstIndex + 1,Length:=Match.Length).Font.ColorINdex = 5
  Next
  Endif
 Next r
End Sub

ポイントとしては、「選択」しているセルを取り出すFor Each r In Selectionと「＋ｎ百万円」を正規表現RegExpで見つけているところ。

ちょっと一息のコーナー。
　トマス ・Ｈ・クックの「鹿の死んだ夜」（染田屋茂　訳、文春文庫）は、月曜日に「自宅の窓ごしに眺めると、リアダンには街が、でたらめな音と方向性のない動きの巨大なパッチワークに見えた。」という描写で静かに始まる。
　もう、随分前にこの本と出会い、思い浮かべるたびにその哀しい余韻が印象に残っている。原書のタイトルは、ブラッド・イノセント（blood innocent）。何年も絶版になった原書を探していたところ、電子書籍の時代となり、kindle本を購入することができた。
　原書では、MONDAY Watching it from his window, Reardon saw the city only as an immense patchwork of random sound and direction-less movement. で始まる。
　immenseは、巨大ななんだー。kindle本は単語の意味がでるので、便利ですね。
　タイトルが「ブラッド・イノセント」ではなく、「鹿の死んだ夜」になっていることのも「いいね」ですが、原書を読み返すたびに、染田屋訳の凄さ、絶妙さを感じています。トマス・クックもこれが処女作というのも凄い。

日々の仕事スタートでは、まず、技（道具）磨く～イチローのように・・・

  日々の仕事は、本当に地味で、辛気臭い作業が多い。だからの、少しでも楽するために様々な道具（EXCEL+VBA,ACCESS,IE+VBA,rubyやperlなど）を駆使して可能な限り、効率化したい。

ストレスなく、毎日、楽しく仕事できる（と思える）ようにするため、仕事のスタートでは「道具を磨く」かのように「スキルを磨く」。「ルーティン」として体に覚えさせていく。スポーツ選手が準備運動でいつもの定型メニューをこなしながら、体をつくるように、効率化のネタを探し、スキルを身につける。「昨日よりも、今日。今日よりも明日の進化」を目指して。
　イチローが試合前にまずは、決まったメニューで体を作るように・・・。昼は炊きたてご飯にカレーというように・・・・。

仕事で覚えたEXCELVBAの初めかた～まずは、マクロの記録で始めたよ

 いきなり、EXCEL　VBAをコーディングするのは、大変ですね。
ワタシは、まず、EXCELの「ツール」タブにあるマクロの記録で、EXCELの操作をVBAへ記録し、それを編集するという形で、始めました。
 最初は、モノまねから入ることをお勧めします。難しく考えずにやってみることが大切ですね！？

「ツール」タブのマクロの記録の画面は、こんな感じです。

 例えば、家計簿の費目ごとの合計を出すピボットテーブルを更新するVBAをマクロの記録で作成します。









そして、ピボットテーブルを更新する操作を「マクロ」として記録します。


記録したVBAコードは、ピボットテーブルを作り、集計するというコードで、こんな感じ。

 Sub Macro1()
' Macro1 Macro
' マクロ記録日 : 2010/3/15  ユーザー名 : Jaga Taro
'
     Worksheets("ピボット").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ").RefreshTable
 End Sub

これに対して、コードを多少手直しする。
手直しのポイントは、以下の３つ。
・シートの選択
・最終行の算出
・ピボットテーブルの範囲（Sourcedata）の設定


こんな感じで、行が増えても、ピボットテーブルを更新できるように変更します。マクロ名もわかり易い名前、例えば、Updatae_Pivot_area（）にしておきます。

Sub Update_Pivot_area()
'
' Macro1 Macro
' マクロ記録日 : 2010/3/15  ユーザー名 : Jaga Taro
'
    Sheets("家計簿").Select

    最終行 = Range("A3").End(xlDown).Row

    Worksheets("ピボット").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ").SourceData = "家計簿!R2C1:R" & 最終行 & "C7"

    Worksheets("ピボット").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ").RefreshTable

End Sub


　このコードを、ピボットテーブル更新という「ボタン（長方形）」に割り当て、このボタンをクリックすると、ピボットテーブルが更新されるようになります。

ここでちょっと一息のコーナー。

　静岡の登呂遺跡の隣に芹沢けい介美術館があります。先日、ミニ仮面作り体験（教えて頂いたお姉さん、ありがとうございました）につられて、出かけました。 芹沢けい介は、日本の染色の草分け、独自の文様感覚が光っており、ググーッと引き込まれました。芹沢先生は。世界の工芸品コレクター でもあり、石ノ森章太郎の「がんばれ！ロボコン」みたいな木彫りの人形、アフリカの仮面（マスク）、これまた、丁寧で、しっかりとした手作りの暖かさがある、魅力的な世界に浸ることができました。
　ついでに美術館の隣にある、登呂遺跡で弥生時代にタイムトリップすることもできました。

 There's Serizawa Keisuke museum next to TORO park in Shizuoka,Japan. Serizawa Keisuke had been a artist in dyeing with pattern and also collected all sorts of artifacts. His abilities to design and colletions of handwork were so splendid that I felt like being in another world. It was pretty amazing.
I have experienced making a mini-mask thanks to a staff of the museum. 
 Go to there if you visited to Shizuoka city.

それじゃ。Bye Bye