読者です 読者をやめる 読者になる 読者になる

WorksheetFunction.Transposeの使用に際しての注意

MS-Office & Windows

仕事中うっかり事故ったのでメモ.


発端

仕事中,「Accessデータベースファイル上のテーブルをExcelのワークシート上に表示する.ただし,DoCmd.TransferSpreadsheetは使用しない」という作業が発生しました.まぁ大したことない作業です.
本来なら,「レコードセットを開き,最終行まで1行づつ,適当に編集をかましながら二次元配列に値を代入.それを最後にシート上に書き出す」とでもやっているでしょう*1.融通も利きますし.
ただ今回はそういった事情もなく,単に全行全列を取得・表示するだけでOKであり,テーブルのサイズ自体も大きくはなかったため,Recordset.GetRowsメソッド(DAO)*2を使用することにしました*3

問題発生

Recordset.GetRowsメソッドはその実行結果を二次元の配列で返します.
ところが,この配列は(列, 行)という形式で返されるため,そのままの形式で出力するのは不適当です*4
しかし幸いにも,Excelには配列の行列を入れ替えるWorksheetFunction.Tranposeメソッドが用意されています.それを使いましょう.

→エラーになりました(実行時エラー13:型が一致しません).なぜじゃ.

解決

WorksheetFunction.Tranposeメソッドには,ヘルプには記載されていないいくつかの制限があるようなのですが,その中に「配列に Null 値を含めることはできません。」という制限があるようです*5
そして,今回表示させようとしたテーブルにはしっかりNULLが入っていました.

仕方がないので結局,Recordset.GetRowsメソッドの利用は放棄しました.以上.

教訓

手を抜くんじゃなかった.

というかさ,Recordset.GetRowsメソッドの使い勝手,悪くね?
もし素直に(行, 列)形式の配列が返されるなら,ほかに何か処理をかます必要もないし,何よりそっちのほうが直感的にわかりやすい気がするのだけれど.

*1:なお,いったん配列を経由するのは速度上の問題のため.レコードセットのサイズが大きい場合,1行づつワークシートに値を書き込んでいくと相当遅くなる

*2:操作対象がAccessデータベースファイルのみである場合,基本的にDAOを使用するのが手っ取り早い.

*3:Recordset.GetRowsは一度に大量の情報を取得するにはあまり向かない.

*4:Range.Value(Excel)で配列を出力する場合,それは(行, 列)という形式にしておく必要がある.

*5:参照:https://support.microsoft.com/ja-jp/kb/246335