エクセルVBA×ADOでDB操作【Recordset】

Excel/VBA

本日はVBA!ADOを用いて、別ファイル(エクセルやCSV)を取り込む操作です。

今回のSQLは SELECT ALLなのであれですけども、
複雑なのやファイルによって変動させたりする場合はDebug.Printでイミディエイトに出力した方がメンテしやすいです。
実行環境 MicroSoft Office 2010 Professional Plus 2010

スポンサーリンク

ドラッグ&ドロップで取り込む

事前準備

今回データの取り込み口はリストビューとして、ここにD&Dで取り込んでいきます。
Alt+F11 でVBE (Visual Basic Editor) を開きます。
参照設定はこんな感じになってます
※64Bit版Excelは残念ながらリストビュー使えません。


参照設定を追加したら、まず初めにフォームを作成します。

ポイントはOLEDropModeをccOLEDropManualにするべし。
次は取り込み先のファイルを適当に作成します。

次に、呼び出し方は何でも大丈夫ですが、今回はボタンを配置して、
ここからフォームを呼び出してみます。

コード本文

準備が出来たら次はフォーム本体に下記コードを入力します。コピペでOK!


最後にSheet1上のボタンを押してフォームを呼び出し作成したエクセルをD&D!

いとも簡単に取り込みが完了します。

CSV取り込み時の注意事項

取り込む時にデータ型を明示的に指定したり変更は出来ず、
頭から8行(デフォルトの場合。 1 ~ 16に変更も可)をスキャンしてフィールドのデータ型を勝手に決めつけてきます。

この超絶極悪仕様により、例えばもしも数値カラムであると判断されてしまった場合、
そのカラムの中に文字列レコードが存在していたらそのレコードだけが消えてしまうのです。 まさに外道

そんな時はここは文字列だよ!と強制的に認識させる為に頭に1文字(何でもいい)付与するか、
下記のようにFileSystemObjectを使い、全て読み取り別シートに張り付けた上でADO接続します。

こうすれば一旦すべてのデータを抜けるので、ここからSQLで細かい指定を行い書き換えてやりましょう。

まとめ

使いこなせれば非常ーに強力な手段です。
ただし、エクセル等からの取り込みの場合は綺麗な表形式になっていないことが多いので、
それらに耐える事が出来る汎用的な作りにする事且つ、
VBAが分からない人でも簡単にメンテ出来るような作りを心がけましょう!

コメント