一定時間ごとに自動更新

　テキストデータの取り込みが完了したら、取り込んできたデータを元にピボッドテーブルを作成します。この場合、取り込んできたデータの範囲はマクロなどで取り出す必要はありません。実は、外部データを取り込むと、その抽出範囲には自動的に名前がつけられるのです。テキストデータをインポートした後で、数式バーの左側にある「名前ボックス」を見るとそのことが確認できるでしょう。また、この「名前」で参照できる範囲は外部データ範囲を更新するごとに自動的に変更され、常に最新の取り込み範囲を参照することができます。そこで、ピボッドテーブルで使用するデータ範囲の指定にこの「名前」を利用し、［外部データ範囲の更新］-［ピボッドテーブルの更新］といった順番で処理を行なえば、最新のデータを元にピボッドテーブルを作成することができるようになります。コードとしては、以下のようになります。

 Sub GetNewData()
     Sheets(1).range("A1").QueryTable.Refresh _ 
     BackgroundQuery:=False
     Sheets(2).PivotTables(1).RefreshTable
     ' 外部データ取り込み範囲の更新が完全に終了してから
     ' ピボッドテーブルを更新する
 End Sub

　また、Excel 2000では一定時間ごとに外部データの自動更新を行なうことができます。発注の状況を30分ごとに確認したり、最新の株価情報を確認したり、計器類の測定値を表示したりなど、さまざまな用途に利用することができます。設定するには、外部データの抽出されている範囲で右クリックし、「データ範囲プロパティ」を選択し、表示させます。その中の「コントロールの更新」項目のうち「更新の周期」で何分ごとに更新をかけるかどうかを設定することができます。またこの時、合わせて「更新時にファイル名を確認」のチェックをはずしておくと、いちいち更新のたびにファイルを指定する手間が省けて、さらに便利になります（図22）。マクロで設定する場合は、「QueryTableオブジェクト」のそれぞれ、「RefreshPeriodプロパティ」「TextFilePromptOnRefreshプロパティ」を下記のように設定すると、30分ごとに外部データを自動で更新させるように設定することができます。

図22：「外部データ範囲のプロパティ」で更新までの時間などを設定する

 With Range("A1").QueryTable
     .RefreshPeriod = 30
     .TextFilePromptOnRefresh = False
End With

　それに対して、先程取り込んできたテキストデータをデータソースに設定したピボッドテーブルなどのような“内部の”データを参照しているものに関しては、自動更新の機能は備えていません。もし、どうしてもQueryTableオブジェクトでイベントを使用したいのであれば、あらかじめクラスモジュールを新規作成して、イベント付きのQueryTableオブジェクトを宣言しておく必要があります。クラスモジュールについては本誌の別の項でも紹介しているので、そちらも参考にしてください。
　また、もうひとつの方法として、外部データを取り込む範囲を参照するダミーの式を作成しておいて、データ更新により「Calculateイベント」を発生させ、それを利用して更新する方法があります。しかし、こちらはややアクロバティックな方法なので、あまりお勧めできません。もし、どうしてもリアルタイムに更新したデータが必要ならば、やはりクラスモジュールでイベントを作成しておくのが一番確実な方法だと言えます。
　以上のような処理で作成したピボッドテーブルが図23のようになります。どの商品をどの取引先にどれだけ注文を受けているのか、また、合計で商品をどれだけ仕入れればいいのかが一目瞭然です。データの中に伝票番号の項目も一緒に入れておけば、伝票別・顧客別の商品の一覧表の作成も可能となり、事務作業の効率化に一役買ってくれることでしょう。

図23：テキストファイルを元に最新の情報に更新

「外部データ範囲のプロパティ」での設定に合わせて、データが更新される