Accessにはデータインポート機能がついており、AccessにExcelのデータを移行することは比較的容易に実施できます。本ホームページでもいくつかのインポート方法を紹介してきました。
しかしながら、これら機能ではうまくいかない場面に出くわすこともあります。よくあるパターンとして、定型様式で1ファイル１様としたExcelファイルを用いてデータが管理されている場合が挙げられます。
この場合、多数のExcelファイルが作成されており、これらファイルに保存されているデータをAccessに移行しなければなりませんが、多数あるExcelファイルのデータをAccessへインポートするために一つ一つ処理することは面倒であり、また、やり直しが発生した場合には目も当てられない状況になります。
そこで、今回はあるフォルダ内に保存されている全てのExcelファイルのデータをAccessに一括インポートするVBAコードを紹介します。
サンプルデータの用意
まずは、図1に示すとおり、定型様式として用いるExcelファイルを準備します。
サンプルとして用意したExcelファイルは、「Sheet1」ワークシートのC2セルに品目、C3セルに価格、C4セルに購入日を入力する定型様式としています。
続いて、日付ごとにこれらExcelファイルを4つ作成し、図2に示すようにCドライブ直下に作成した「TEST」フォルダに保存します。
ここで、作成した各ファイルの内容は表1に示すとおりです。
表1 サンプルとして用意したExcelファイルの各内容
| Excelファイル名 | 230130 | 230131 | 230201 | 230202 |
| 品目 | えんぴつ | 消しゴム | ノート | えんぴつ |
| 価格 / 円 | 500 | 1200 | 2000 | 700 |
| 購入日 | 1月30日 | 1月31日 | 2月1日 | 2月2日 |
Accessファイルの準備
サンプルデータとしてExcelファイルを用意することができましたので、次はAccessファイルの準備をしていきます。
まずは、新規にAccessファイルを作成した後、「T_購買」テーブルを作成し、表2に示すようにフィールドとデータ型を設定してください。
表2 「T_購買」テーブルのフィールドとデータ型
| フィールド名 | データ型 |
| 品目 | 短いテキスト |
| 価格 | 通貨型 |
| 購入日 | 日付/時刻型 |
以上で、データの容れ物であるテーブルも用意することができましたので、VBAの実装に入っていきましょう。
VBAサンプルコード
早速、Accessの「データベースツール」タブのマクロセクションにある「Visual Basic」アイコンをクリックし、VBA画面を表示させます。そして、「挿入」→「標準モジュール」の順に選択し、新規に標準モジュールを作成します。
標準モジュールには以下のコードを記載します。
Private Sub Import()
On Error GoTo Err_Import
Dim FileName As String 'ファイル名
Dim xls As Object 'Excelオブジェクト変数
Dim wkb As Object 'Excelワークブックオブジェクト変数
Dim objSelection As Object 'Excel Activeシート
Dim cn As New ADODB.Connection 'ADO接続変数
Dim rs As New ADODB.Recordset 'ADOレコードセット変数
Dim i As Integer 'ループ処理用
'データベースに接続し、「T_購買」テーブルを開いた状態にする。
Set cn = CurrentProject.Connection
rs.Open "T_購買", cn, adOpenKeyset, adLockOptimistic
FileName = Dir("C:\TEST\" & "*xls*") ' TESTフォルダの中に含まれるファイルを取り出す
Do While FileName <> "" ' TESTフォルダにExcelファイルがなくなるまで繰り返す
'Excelを開く
Set xls = CreateObject("Excel.application")
Set wkb = xls.Workbooks.Open("C:\TEST\" & FileName)
'Excelの画面を開かずに処理をする。
wkb.Application.Visible = False
'Excelシートの参照先セット
Set objSelection = wkb.Worksheets("Sheet1")
With objSelection
'T_購買テーブルへの登録
rs.AddNew
rs!品目 = .Range("C2")
rs!価格 = .Range("C3")
rs!購入日 = .Range("C4")
rs.Update
End With
Set objSelection = Nothing
'Excelファイルを閉じる（Excelファイルは上書き保存しない。）
wkb.Close savechanges:=False
Set wkb = Nothing: Set xls = Nothing
'次のファイルへ移行
FileName = Dir() '
Loop
'データベースを閉じる
rs.Close
cn.Close
Set cn = Nothing
MsgBox "インポート処理を終了しました。"
Exit_Import:
Exit Sub
Err_Import:
MsgBox Err.Description & "データインポートエラー"
For Each wkb In xls.Workbooks
If wkb.Name = Dir(FilePath) Then
wkb.Close savechanges:=False
End If
Next wkb
Resume Exit_Import
End Sub
それでは、一つずつコードの確認をしていきます。まずは、変数宣言を行い、続いてADOを用いたデータベースへの接続、「T_購買」テーブルを開く処理をしています。ADOの使い方については以下の記事をご参照ください。
これで、Access側へのデータ書き込みができる状態になったため、各Excelのデータを取り込んでいきます。
今回は、Dir関数を用いて「C:\TEST」フォルダ内にあるExcelファイルの名前を取得します。ここで、「*xls*」としているのは、Excelファイルの拡張子には「xls」、「xlsx」や「xlsm」などがあるためです。
そして、Do While文を用いて「FileName」変数が空の文字列を返した場合にループ処理を終了させるようにしています。
ここで、ポイントとしてDir関数の使い方があります。コードの下の方を見ていくと、Do While文のLoopの前に「FileName = Dir()」という記載をしています。
「Dir()」は、直前に書いたDir関数と同じ処理をする、という意味になるのですが、唯一前回と異なる点は、2回目に見つけたファイル名を返すところにあります。つまり、今回の例では先に記載している「FileName = Dir(“C:\TEST\” & “*xls*”) 」と同じ意味になりますが、ループ処理により、2回目、3回目と順次見つけたファイル名が「FileName」変数に格納されることになるわけです。
続いて、ループ文の中を確認していきます。CreateObjectメソッドを用いてExcelを指定し、Dir関数で取得したファイル名のExcelファイルを開きます。
今回の場合、Excelからのインポート処理だけを行いExcel画面を開いて処理をするといった必要はありませんので、「Application.Visible ＝False」としています。（画面を開かない分、処理スピードも向上します。）
参照するシート名は先述のとおり「Sheet1」としていますので、「objSelection」変数にセットするシート名も「Sheet1」としています。
そして、ADOを用いて「T_購買」テーブルにレコードセットを追加し、各フィールドにExcelの各セルの値を入力しますが、毎回「objSelection.Range(○○)」と書くのは煩雑なので、Withを用いて記載を簡略化しています。
Excelデータを取り込んだ後、開いているファイルを閉じる処理をしますが、Excel側にデータの変更はありませんので、上書き保存はせずに終了しています。
以上の処理を繰り返し行い、対象ファイルが無くなった段階でループから抜けます。
ループから抜け出した後は、データベースを閉じる処理をし、インポートが終了したことを知らせるメッセージボックスを出すようにしています。
なお、エラーハンドラーでは、処理の途中で開いているExcelファイルがあった場合にタスクマネージャーで終了処理をしなくてはいけなくなるため、それを回避するための処理を記載しています。
VBAの実行
ここまでで、実装が完了しましたので、実際にインポートができるかを確認してみましょう。
VBAの実行は、対象のプロシージャ（今回の場合は、「Private Sub Import()」）にマウスでカーソルを合わせた後、「F5」キーを押下することで実行されます。または、「Sub/ユーザーフォームの実行」ボタン（緑色の▶印）をクリックすることでも実行されます。
実行するとしばらくしてから、「インポート処理を終了しました。」というメッセージボックスが表示されますので、その後に「T_購買」テーブルを開いてみてください。
図3に示す結果が得られます。
このようにして、多数あるExcelファイル中のデータを一括でインポートすることができました。
まとめ
今回の記事では、多数あるExcelファイルのデータを一括でAccessへインポートする方法について紹介しました。
コーディングは少し大変な面もありますが、一度実装してしまえば、ルーチン的な業務をコンピュータが自動で実行してくれますので、総合的には業務時間の短縮が図れ、またヒューマンエラーをなくすこともできます。
是非、皆さんも今回の記事を参考として業務効率の改善を図っていただければと思います。
今の時代、VBAに限らずプログラミングができるということは、当然のスキルとして広く社会で認知されています。いまだプログラミングが十分にできないという人は、今からでも遅くありません。できるだけ早く基本的なプログラミングスキルを習得することをお勧めします。
ご自身にあった専門書を参考に、実践的なプログラミング・データベース・サーバ、データ分析・機械学習など、システムエンジニアや今後を担うDX人材に必要とされているスキル獲得に向けて基礎から学んでいくことが大切です。
最初につまずきがちな点も、書籍を読みながら試行錯誤して課題解決していくことで、自身のスキルアップを目指すことができます。思い立ったが吉日、是非業務効率の改善に向けてプログラミング学習に勤しんでください！
スポンサーリンク
コメント