スクリプトからMicrosoft Excelを操作する(Webページから株価を取得)
(目次)
1.概要
ここでは、ススクリプトからMicrosoft Excel(以下、Excelと略します)を操作する例を紹介します。
なお、動作確認は、Microsoft Excel 2002 で行いました。
今回紹介するのは、Webページから取得したテキスト(株価)をExcelのワークシートに書き込むスクリプトです。
このスクリプトは、スクリプトでこんなこともできるという紹介が目的のものです。更に高度なことをしたい場合は、末尾の参考リンクのホームページなどを参考に各自研究してみてください。
2.サンプルスクリプトの概要
今回紹介するサンプルスクリプトの動作内容は下記の通りです。
- Yahoo!ファイナンスからソニー(株)の企業の株価情報のページを表示する。
- 表示したWebページから株価を取得する。
- Excel を起動する
- 取得した株価を日付と共にExcelのセルに書き込む
以下の圧縮ファイル内のExcelファイルを適当なフォルダにダウンロードして、スクリプト先頭のcstrDateFilenameの部分をダウンロードしたパスに修正して使用して下さい。
ダウンロード:Stock.zip(8KB)
Excelファイルの中身は下図のようなものです。
毎日、株式の取引終了後にこのスクリプトを実行すれば、毎日の株価を記録することができます。
3.ソースリスト全文
下が今回紹介するするスクリプトになります。お気に入りフォルダなどにスクリプトファイル(*.sbv)として保存して実行してください。
スクリプト前半の株価を取得するまでのコードは、テーブル内(表内)のテキストを取得する方法を参考にして下さい。
スクリプト後半の取得した株価をExcelのセルに書き込む部分は「5.項」で解説します。
RegExpオブジェクトやReplace関数等、VBScriptの各機能についてはVBScriptのリファレンスを参照して下さい。
option explicit ' 下のファイル名は環境に合わせて適宜修正してください。 Const cstrDateFilename = "t:\stock.xls" ' 正規表現を使用して文字列を置換する関数 Function ReplaceText(strTarget, strPtrn, strRepl) Dim regEx Set regEx = New RegExp regEx.Pattern = strPtrn ReplaceText = regEx.Replace(strTarget, strRepl) End Function ' セル内のテキストから数値を抜き出す関数 ' 先ず数値以外のテキストを削除してから最後にテキストを数値に変換している Function GetNumber(strValue) ' 改行を削除する strValue = Replace(strValue, vbCRLF, "", 1, -1, vbTextCompare) ' 文字列の末尾から数字の部分のみ抜き出す strValue = ReplaceText(strValue, "^.*[^0-9,\.]([0-9,\.]+)$", "$1") ' 取得した値に,(カンマ)が含まれている場合は削除 GetNumber = CDbl(Replace(strValue, "," , "", 1, -1, vbTextCompare)) End Function Dim Window1 Dim iCol, iRow Dim dSiti, dTaka, dYasu, dOwa, dDeki Dim tbl ' Yahoo!ファイナンスのソニーのページを表示 ' 他の企業の株価を取得するには6758.tの部分を各企業の銘柄コードに置き換える Set Window1 = NavigateNew("http://quote.yahoo.co.jp/q?s=6758.t&d=t") Window1.SetFocus Set tbl = TableOfCellText2(Window1.document.Body, "前日終値", iCol, iRow) dSiti = GetNumber(tbl.Rows(iRow+1).Cells(iCol - 2).innerText) dTaka = GetNumber(tbl.Rows(iRow+1).Cells(iCol - 1).innerText) dYasu = GetNumber(tbl.Rows(iRow+1).Cells(iCol ).innerText) dOwa = GetNumber(tbl.Rows(iRow ).Cells(iCol - 2).innerText) dDeki = GetNumber(tbl.Rows(iRow ).Cells(iCol + 1).innerText) ' 下は、動作確認のデバッグ用 ' msgbox dSiti & vbCRLF & dTaka & vbCRLF & dYasu & vbCRLF & dOwa & vbCRLF & dDeki ' ========================================================= ' (1) Excel の操作開始 ' ========================================================= Dim WorkBook Dim Excel Set WorkBook = GetObject(cstrDateFilename) Set Excel = WorkBook.Application ' Excel本体のオブジェクトを取得 Excel.Visible = TRUE ' Excel本体のウィンドウを表示する WorkBook.Activate ' WorkBookをアクティブにする WorkBook.Windows(1).Visible = TRUE ' WorkBookのウィンドウを表示する Dim WorkSheet Set WorkSheet = WorkBook.WorkSheets(1) ' 最初のワークシートを取得する WorkSheet.Activate ' 取得したワークシートをアクティブにする ' (2)未入力のセルを検索する iRow = 2 while (WorkSheet.Cells(iRow, 1).Value <> "") ' セルに値が代入されている間、iRowを1増やす iRow = iRow + 1 wend ' (3)株価を各セルに代入する WorkSheet.Cells(iRow, 1).Value = Year(Date()) & "/" & Month(Date()) & "/" & Day(Date()) WorkSheet.Cells(iRow, 2).Value = dDeki WorkSheet.Cells(iRow, 3).Value = dSiti WorkSheet.Cells(iRow, 4).Value = dTaka WorkSheet.Cells(iRow, 5).Value = dYasu WorkSheet.Cells(iRow, 6).Value = dOwa
4.スクリプトからExcelを操作する方法
VBScriptからExcelを操作するには、Windowsのオートメーション機能を使用します。
具体的にはGetObject関数でExcelのドキュメント(ワークブック)のオブジェクトを取得します。この時、Excelが起動していない場合は自動的に起動されます。既にファイルが開いている場合は、開いているワークブックのオブジェクトを取得します。この取得したオブジェクトを使用して、Excelを操作します
取得したExcelのオブジェクトで使用できるメソッドやプロパティはExcelのヘルプの「Microsoft Excel Visual Basic リファレンス」に記載されています。Excel 2002の場合は、ヘルプの目次の「プログラミング情報」の下にあります。なお、Visual Basicのヘルプがインストールされていない場合は追加インストールする必要があります。
Excelの操作をスクリプトでどのメソッドや関数で実現するかを調査するには、Excelの「マクロの記録」機能を使用して調査したい操作をマクロに記録すると便利です。マクロの記録はVBA(Visual Basic for Applications)で行われますがVBAとVBScriptは非常によく似た言語なので大変参考になります。
5.サンプルスクリプトでExcelを操作している部分の解説
以下、サンプルスクリプトのExcelを操作している部分について解説します。スクリプトの各行のコメントも参考にしてください。
スクリプト内のコメントの「(1) Excel の操作開始」以降が、Excelを操作する部分になります。
まず、GetObject関数を使用して指定したファイル名のExcelのワークブックオブジェクトを取得します。
次にWorkBook.ApplicationプロパティでExcel本体のオブジェクトを取得します。
そして、Excelのオブジェクトを操作してウィンドウを表示して更にウィンドウをアクティブにしています。なお、ここで使用しているプロパティ等についてはExcelのヘルプを参照してください。
「(2)未入力のセルを検索する」の部分ではワークシート内でまだ入力されていない行を検索しています。単純に空のセルが見つかるまでiRow変数に1を加えていって検索しています。
最後に、「(3)株価を各セルに代入する」の部分でWebページから取得した株価情報を各セルに代入しています。
6.参考リンク
以下に、スクリプトからExcelを操作するのに参考になりそうなホームページを紹介します。
これらのホームページの解説は、WSH(Microsoft Windows Scripting Host)からExcelを操作する場合の解説です。ScriptBrowserKのスクリプト内ではWscriptオブジェクトの機能は使用できないので注意して下さい。
- 「Happy! Happy! Island」のホームページの「cafe de VBS」-「4.エクセルの操作」
- 「MSDNライブラリ」の「あなたの仕事は自動化できますか!?」
以上です。
2007/08/05