Takenoff Labs

[Notes/Domino] Lotus Script で Excel とデータをやりとりする際の Tips

Excel からデータを取り込んだり出力したりする処理はよくやると思いますが、いくつかの点に注意しないと思わぬトラブルに見舞われることがあります。以下、今まで管理人が体験したことをまとめてみます。

セルにエラーがある場合

セルに「#REF!」などのエラーがある場合、Range.Value でその値を取り込もうとすると、ノーツが落ちてしまいます。例外返すならまだしも、落ちるって……orz

この場合の解決策は、わたしの知る限り2とおりあります。まずは、Range.Text のほうを使うことです。Range.Text であれば、Excel を開いたときにセルに表示されている値がそのまま取得できます。そのかわり本当にそのまんまなので、テキスト型以外の値は面倒になります。

2番目は、ドミノ懇談室でノーツスペシャリストなわろさんが書かれているとおり、「ExcelApplication.WorksheetFunction.IsError」を使うことです。これであれば特に型を気にせず使えます。ただし、速度はちょっと遅くなってしまいます。「ExcelApplication.WorksheetFunction.IsError」を何度も呼び出すと遅くなるので、いったんこの部分を変数に入れてから使うことをおすすめします。（それでもちょっと遅くはなりますが……。）

なるべく配列で処理する

これはよく知られていることですが、1セル1セルを個別に処理していると、処理件数が多い場合に非常に遅くなってしまいます。取り込み時も出力時も、なるべく配列で処理するのが望ましいです。

出力時は、以下のように配列を Range.Value に入れるだけです。Variant を使えば、文字列・数値・日付型を混ぜて使うこともできます。

Dim v(2) As Variant
v(0) = "aaa"
v(1) = 123
v(2) = Now
Worksheet.Range("A1:C1").Value = v

Range で "A1:C1" のようなセル範囲を割り出すのが面倒な場合は、以下のようなコードでもOKです。ただし、処理速度は多少遅くなるかも？（まぁたいしたオーバーヘッドでもないかな？と思い、管理人はいつもこちらを使っています）

Dim r1 As Variant, r2 As Variant
Dim v(2) As Variant
v(0) = "aaa"
v(1) = 123
v(2) = Now
Set r1 = Worksheet.Cells(1, 1)
Set r2 = Worksheet.Cells(1, 3)
Worksheet.Range(r1, r2).Value = v

配列で出力する際の最大の注意点は、長い文字列を出力するとエラーになる点。出力前に配列の中身をチェックし、800文字（バイトではない）程度を超えるものがあったら、以下のどちらかの方法で回避する必要があります。

1セルごとに個別出力するようにする。
長い文字列の箇所を覚えておき、とりあえずNULLをセットして出力し、長い文字列はあとで個別に出力する。

取り込み時は、「Worksheet.Range("A1:C1").Value」のように、Rangeが複数セルを対象にしていれば、自動的に戻り値も配列になります。ただし、1行のデータであっても2次元配列になるため、その点は注意が必要です。（取り込み時は、なぜか長い文字列が含まれていてもエラーにならないです。なんでだろう？？？）

先頭に記号が付いているデータを出力する場合

先頭に記号が付いているデータを出力する場合は、注意が必要です。たとえば、シングルクォーテーション「'」は文字列の意味になりますから、本当に「'」自体を出力したい場合は、2つ重ねて「''」と出力しなければなりません。

また、「-」などの演算記号が付く場合は特に注意が必要です。「-」などの演算記号が先頭に付いた場合、式とみなされ、式の最大サイズ（Excel2003では1024文字）を超える場合はエラーとなってしまいます。1024文字を超えるデータには、問答無用で「'」を付けるのがよいかもしれません。（この件はN尾先生にご協力いただきました。ありがとうございますm(_ _)m）