Office Space: Microsoft Office アプリケーションのスクリプト作成に関するヒントとテクニック
Office Space へようこそ。Office Space は、Microsoft® Office アプリケーションのスクリプト作成に関するヒントとテクニックを紹介する新しいコラムです。過去のヒントについては、Office Space アーカイブを参照してください。Microsoft Office でのスクリプト作成について質問がある場合は、scripter@microsoft.com (英語のみ) までお送りください。すべての質問に回答することはできないかもしれませんが、可能な限り対応いたします。
Excel スプレッドシートにデータを追加する
人はアドバイスするのは得意でも、具体的な実践方法をなかなか提案しないものです。
「定年後のために貯金を始めなさい。」
「もっといい仕事を探しなさい。」
「Baseball Tonight を 3 回も見る暇があったら他のことをやりなさい。」
実のところ、Scripting Guys は俊才の集まりというわけではありません (残念ながら、どこを探しても、私たちの方が勝っていると思える人にお目にかかったことがありません)。たとえば、私たちは何度となく、テキスト ファイルではなく Microsoft Excel をレポート作成ツールとして使うことをお勧めしてきました。スクリプトを実行するたびに新しいスプレッドシートを作成するのであれば、これは適切な助言です。では、来る日も来る日も同じスプレッドシートを使って既存のデータに単に新しいデータを追加していく場合はどうでしょう。Scripting Guys はこの問いに対して答えていません。
Scripting Guys も時に無責任な対応をしてきました。でも、これからは違います。今日のコラムでは、既存のスプレッドシートにデータを追加する方法を紹介します。時間があれば、定年後のための貯金の始め方についてもお話しましょう。
もちろん、お話するのは私たちの定年後のために皆さんが貯金する方法についてですが。
スプレッドシートにデータを追加する前に、どこまでが既存データかを把握しておく必要があります。そうしないと、既存データを上書きしてしまう危険があります。そこで、既存データの末尾と新規データの開始位置を確認するための 2 つの方法を紹介します。
次のように、なるべく単純なスプレッドシートから始めましょう。
ご覧のとおり、列 A、B、C にデータがあります。列 A はすべての行にデータがなければならない必須データとします。つまり、列 A に値を持たない新規行を追加することはできません。空白行も許可されないものとします。この例では、6 行目に次のデータが入力されます。この場合、新規データの開始位置を確認するのは簡単です。列 A に値がある最後の行を探せばよいのです。列 A のすべてのセルに値が必要なため、その次の行から新規データを追加すればよいことがわかります。
ここまではよろしいでしょうか。わかりにくい場合は、図をご覧ください。5 行目は列 A に値が存在する最後の行です。空白行が許可されず、列 A には値が必要であるため、次のデータは 6 行目に入力するほかありません。
これは、実際にスプレッドシートを見てみれば、すぐにわかることです。では、プログラムを使用する場合はどうすればよいでしょう。
1 つの方法を示します。Excel でデータ列の最後の行を取得するのは簡単です。カーソルをその列のセル (セル A1 など) に配置し、Ctrl キーを押しながら↓キーを押します。前の例では、カーソルはセル A5 に移動します。次に、↓キーを押して、カーソルを最初の空白行に移動します。Ctrl キーと↓キーの位置がわかっていれば、目をつぶっていても最初の空白行が見つかります。
この便利な方法をぜひ友人にも教えてあげてください。
スクリプトでも同じことをします。カーソルを最初の空白行に移動するスクリプトを次に示します。この動作については後述します。
Const xlDown = -4121
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1,1) = 1
objWorksheet.Cells(2,1) = 2
objWorksheet.Cells(3,1) = 3
objWorksheet.Cells(4,1) = 4
objWorksheet.Cells(5,1) = 5
Set objRange = objExcel.Range("A1")
objRange.End(xlDown).Activate
intNewRow = objExcel.ActiveCell.Row + 1
strNewCell = "A" & intNewRow
objExcel.Range(strNewCell).Activate
よろしいですか。まず、定数 xlDown を定義し、値を -4121 に設定します。この定数は後から Ctrl + ↓キーを表すために使用します。Excel の表示インスタンスを作成し、新しいブックとワークシートを追加して、セル A1 〜 A5 にデータを入力するコードを次に示します。これで完了です。
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1,1) = 1
objWorksheet.Cells(2,1) = 2
objWorksheet.Cells(3,1) = 3
objWorksheet.Cells(4,1) = 4
objWorksheet.Cells(5,1) = 5
これで、実際にスクリプトを作成する準備ができました。まず、セル A1 のみで構成される範囲を作成します。
Set objRange = objExcel.Range("A1")
なぜこの処理を行うのでしょうか。それは、後で Ctrl + ↓キーの動作を模倣する際に、セル A1 から開始する必要があるためです。そこで、1 つのセルのみから成る小さい範囲を作成します。
次に、Ctrl + ↓キーの動作を実際に模倣します。記述するコードは次の 1 行のみです。
objRange.End(xlDown).Activate
ご覧のとおり、ここでは End プロパティと Activate メソッドを使用します。適切なセルに移動するには、End の値を定数 xlDown に設定します。これで、列の最後に移動するように Excel に通知します (空白セルが見つかるまで列を下に移動するだけの操作です)。目的のセルに移動したら、Activate メソッドを呼び出して、そのセルをアクティブなセルにします。
これで、カーソルはセル A5 に配置されます。スプレッドシートのデザインに基づいて、セル A6 が最初の空白セルであることがわかっています。それでは、カーソルをセル A6 に移動するにはどうすればよいでしょうか。
その方法はいくつかあります。ここでは、カーソルの位置を追跡しやすい方法を紹介します。
intNewRow = objExcel.ActiveCell.Row + 1 strNewCell = "A" & intNewRow objExcel.Range(strNewCell).Activate
ここでは、変数 intNewRow に値を代入します。代入する値は、アクティブなセルの行番号 (5 行目) + 1 です。そのため、現在の行ではなく、次の行に移動します。
別の新しい変数 strNewCell に値を代入します。この変数には文字 A + intNewRow の値 (6) が代入されます。つまり、strNewCell に代入される値は A6 です。これは次の空白セルを示します。カーソルをこの新しいセルに移動するには、このセルで構成される範囲を作成し、Activate メソッドを呼び出します。カーソルが空白セルに移動し、このセルのアドレス (A6) もわかっているので、スプレッドシートに新規データを追加できるようになりました。
新規データを追加する簡単な例が必要であれば、次のスクリプトをお使いください。このスクリプトでは、元のスプレッドシ−トの最後にいくつかの数字を追加するだけです。このサンプル スクリプトでは、新規行を追加するたびにアクティブなセルを変更する必要はありません。データをスプレッドシートに追加するだけでよいのです。
Const xlDown = -4121
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1,1) = 1
objWorksheet.Cells(2,1) = 2
objWorksheet.Cells(3,1) = 3
objWorksheet.Cells(4,1) = 4
objWorksheet.Cells(5,1) = 5
Set objRange = objExcel.Range("A1")
objRange.End(xlDown).Activate
intNewRow = objExcel.ActiveCell.Row + 1
For i = 1 to 10
objWorksheet.Cells(intNewRow, 1) = i * 100
intNewRow = intNewRow + 1
Next
この方法は、空白行がないスプレッドシートには最適です。しかし、空白行が許可されるスプレッドシートの場合はどうでしょうか。たとえば、次のようなスプレッドシートの場合を考えてみましょう。
この場合は問題があります。ここでは、↓キーを使う方法は使用できません。その理由は、カーソルをセル A1 に置き、前述のスクリプトを実行すると、列 A の最初の空白セルは 4 行目であるため、カーソルがセル A4 に移動してしまうからです。これは最後のデータ行ではありませんが、スクリプトにはその判別はできません。スクリプトは最初の空白セルを探しますが、最初の空白セルがデータの最後とは限りません。最初のスクリプトをこのスプレッドシートに使用すると、データはセル A4 から追加され、5 行目と 6 行目の既存データが上書きされてしまいます。
これは確かに問題です。ただし、この問題には対処法があります。Excel の Range オブジェクトには、値があるすべてのセルを範囲とする UsedRange プロパティがあります。サンプルのスプレッドシートでは、UsedRange は次のようになります。
これで問題は解決です。データがある範囲の一番下の行を特定すればよいのです。その次にデータ入力が可能となる行は、この範囲の直後の行です。この処理を行うスクリプトを次に示します。
Const xlCellTypeLastCell = 11
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1,1) = 1
objWorksheet.Cells(2,1) = 2
objWorksheet.Cells(3,1) = 3
objWorksheet.Cells(5,1) = 4
objWorksheet.Cells(6,1) = 5
Set objRange = objWorksheet.UsedRange
objRange.SpecialCells(xlCellTypeLastCell).Activate
intNewRow = objExcel.ActiveCell.Row + 1
strNewCell = "A" & intNewRow
objExcel.Range(strNewCell).Activate
このスクリプトでは、まず、定数 xlCellTypeLastCell を定義して値を 11 に設定します。この定数と SpecialCells メソッドを使用して、カーソルを範囲の最後のセルに移動します。また、次のようなサンプルのスプレッドシートを作成します (セル A4 が空白であることに注意してください)。
次に、UsedRange プロパティに基づいて範囲を作成し、SpecialCells メソッドを使用して範囲の最後のセル (ここでは A6) に移動します。
Set objRange = objWorksheet.UsedRange objRange.SpecialCells(xlCellTypeLastCell).Activate
カーソルを次の使用可能なセル (セル A7) に移動すれば、データを追加する準備は完了です。
これで、データをスプレッドシートに追加できます。その代わり、もう Baseball Tonight ばかり見ていると非難しないでくださいね。
次はおまけのコードです。セルをアクティブなセルにすると、次のようなコードでセルのアドレス、行番号、および列番号を取得できます。
Wscript.Echo objExcel.ActiveCell.Address Wscript.Echo objExcel.ActiveCell.Row Wscript.Echo objExcel.ActiveCell.Column
