Access VBA　任意のExcelワークシートをインポートする方法

業務をする中では、多岐に亘るデータを様々な条件に応じて処理する必要が出てきます。また、定期的にデータが発生するケースもあるのではないでしょうか。そういったとき、Excelファイルで複数のワークシートにまたがりデータ処理をすることは良くあることだと思います。このようなExcelファイルから、Accessデータベースへインポート処理をするためには、どのワークシートの情報をインポートしたいか指定する必要が出てきます。

これまでに、Access VBAでExcelワークシートのインポート処理を紹介してきましたが、今回は少し踏み込んで、任意のExcelワークシートをインポートする方法を紹介します。

なお、一つのワークシートからAccessへインポートする方法については以下の記事で紹介していますので、に搭載されているインポート機能については以下の記事をご参照ください。

サンプルデータ
VBAによる実装
まとめ

サンプルデータ

今回は、Excelファイル（xlsx形式）で以下添付のとおりサンプルデータを作成しました。

wkSheet2Access ダウンロード

サンプルデータの中身は、「Sheet1」及び「Sheet2」のワークシートから成り、それぞれのワークシートには「氏名」、「年齢」及び「生年月日」のフィールドで構成された5レコードが含まれます。また、1行目はタイトル行としています。

また、作成したサンプルファイルは「C:\TEST¥wkSheet2Access.xlsx」のファイルパスとなるように保存します。

Access側では、「T_Import」テーブルを作成し、「ID」、「氏名」、「年齢」及び「生年月日」フィールドを作成し、それぞれのデータ型を「オートナンバー型」、「短いテキスト」、「数値型」及び「日付/時刻型」とします。

これを基に、次からはVBAによる実装をしていきます。

VBAによる実装

まずは、VBA画面で次のコードを実装します。

Private Sub ExcelWorkSheetsImport()

'変数宣言
Dim SheetName As String
Dim i As Integer

'Sheet1から順番にインポートするための数字
i = 1

'ワークシートを順番にインポート処理する。
Do Until i = 3

    'ワークシート名の設定
    SheetName = "Sheet" & i & "!"

    'インポート処理
    DoCmd.TransferSpreadsheet acImport, 10, "T_Import", "C:\TEST¥wkSheet2Access.xlsx", True, SheetName

    i = i + 1

Loop

'Excelファイルをインポートした旨を通知する。
MsgBox "Excelファイルをインポートしました。"

End Sub

ここで、今回のインポート処理は「Docmd.TransferSpreadsheet」メソッドを用いている点に着目してください。先ほど紹介しました過去の記事で具体的な内容を紹介していますが、同様のメソッドでワークシートを指定することが可能です。

それでは、上記コードを一つずつ確認していきましょう。まずはお約束の変数宣言ですが、宣言した変数を用いて、シート番号を順次切り替えられるようにし、インポート元のExcelファイル中のワークシートを可変処理しています。

ポイントとしては「SheetName」変数にワークシート名と合わせてエクスクラメーションマーク「！」を格納している点です。これにより対象がワークシートであることを示しています。

続いて、制御文によりワークシートの分だけデータをインポートする処理を設定しています。今回は2つのワークシートのため、「Do Until i = 3」として、「i = 3」になった段階でループ文から抜ける処理としています。

制御文の中では、ワークシート名を都度作成するようにし、続く「Docmd.TransferSpreadsheet」メソッドによりAccessファイル中の「T_Import」テーブルへ、ファイルパスで指定したExcelファイル中の「SheetName」で指定したワークシート中の値をインポートします。この際、当該メソッドの第5引数を「True」としているため、１行目はタイトル行と認識され2行目からを実データとして取り込みます。インポート時に該当テーブルがなければ新たに作成され、既に存在していれば、レコードを追加する形での処理が行われますので、データ型が不一致とならないように注意も必要です。

その他、今回は「ID」フィールドを「オートナンバー型」にしていますが、データインポート時に自動で付番されます。

最後に、Excelのワークシートをインポートしたことを示すために、メッセージボックスを表示させて処理が完了します。

図1にAccessへ取り込んだ結果である「T_Import」テーブルの中身を示します。

このように、各ワークシートから5レコードずつ、計10レコードがインポートされていることが分かります。

まとめ

今回は、Excelファイル中に複数あるワークシートからAccessへインポートする処理を紹介しました。サンプルでは制御文を用いて繰り返し処理をしましたが、単純に「Docmd.TransferSpreadsheet」メソッドを用いることで、特定のワークシートを指定して必要情報を取得することも可能です。

是非、今後の業務効率改善に向けて本サンプルを参考にしていただければと思います。

ここで紹介した内容は、専門書などでも紹介されていますので、是非お手元に用意されておくことで、今後のAccess開発にも役立つかと思います。