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

VBA

様々な業務を進める上で、Excelを用いてデータを集計し処理することは多々あるかと思います。Excelは直感的な使い勝手が良く、多くのユーザーが簡便に使用できるという点で幅広い用途があります。しかしながら、データベースという観点からデータを蓄積し、得られた情報を活用するという点ではAccessが優位になります。

このため、Excelにより定型化されたファイルをAccessへインポートするといったニーズは様々な場面で見られます。Accessには、インポート機能が備え付けられていますが、VBAで処理を実装することでより応用が利き、汎用性も高くなります。

なお、Accessに搭載されているインポート機能については以下の記事をご参照ください。

そこで今回は、VBAによるコーディングを主眼に置いて実装方法を紹介していきます。

スポンサーリンク

TransferSpreadsheetメソッドの活用

さて、ExcelのワークシートをAccessへインポートするには、「Docmd.TransferSpreadsheet」を用いれば良いのですが、実はこのメソッドはAccessからExcelワークシートをエクスポートする際にも用いることができ、その方法を以下の記事で紹介しています。

詳細の引数については、上記記事で確認いただければと思いますが、今回の場合ExcelのワークシートをAccessへインポートしますので、次のようなコーディングをします。

Docmd.TransferSpreadsheet acImport, , “T_Import”, FilePath, True

ここで、第1引数「acImport」はExcelファイルのインポートを意味し、第3引数「”T_Import”」はAccessインポート先のテーブル名を、第4引数の「FilePath」はインポート元のExcelファイルのファイルパスを格納した変数です。また、第5引数の「True」はワークシートの1行目をフィールド名として使用することを意味しています。1行目もデータとして使用する場合は「False」にします。

また、今回は第6引数を省略していますが、インポートする場合にワークシートの範囲を設定することもできますが、省略した場合はワークシート全体をインポートすることになります。

次に、今回のために用意したサンプルデータを紹介します。

サンプルデータ

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

サンプルデータの中身は、「登録ID」、「氏名」、「生年月日」及び「出身地」のフィールドで構成された20レコードから成ります。

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

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

VBAによる実装

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

Private Sub ExcelImport()

'変数宣言
Dim FilePath As String

'Excelインポート元のファイルパス
FilePath = "C:\TEST\Excel2AccessSample.xlsx"

'Excelファイルのインポート
DoCmd.TransferSpreadsheet acImport, , "T_Import", FilePath, True

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

End Sub

上記コードを一つずつ確認していきましょう。まずはお約束の変数宣言ですが、ここで宣言している変数を用いて、次にインポート元のExcelファイルパスを格納しています。

続いて、Docmd.TransferSpreadsheetによりAccessファイルに「T_Import」というテーブルを作成して「FilePath」で指定したExcelファイルの1行目をフィールド名としてインポートするという指示をしています。該当テーブルがなければ新たに、既に存在していれば、レコードを追加する形での処理が行われますので、データ型が不一致とならないように注意も必要です。

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

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

図1 ExcelワークシートをAccessへインポートした結果

また、作成された「T_Import」テーブルをデザインビューで開くと図2のように、それぞれのデータの種類に応じたデータ型が設定されていることが分かります。

図2 ExcelワークシートをAccessにインポートした場合のデータ型

このように、TransferSpreadsheetによるインポート処理の実装は非常に簡単にできますが、処理を繰り返すと同じレコードが重複して登録されますので、注意してください(図3参照)。

図3 ExcelワークシートをAccessにインポートする際の注意点

まとめ

今回は、ExcelのワークシートをAccessへインポートするためのVBAによる実装方法を紹介しました。簡単なコードで実装でき、また、他のコーディングと組み合わせて汎用性を高めることでより業務効率の向上に繋げることもできますので、まずは基本的なコードの内容を知っていただく良い機会になったのではないかと思います。

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

スポンサーリンク

Audible (オーディブル) - 本を聴くAmazonのサービス:Amazon.co.jp:Appstore for Android

コメント

タイトルとURLをコピーしました