エクセルファイルのデータをAccessにインポートするため、下記3パターンを紹介します。自分のシステムに合った方法を選んでご利用ください。
その①｜ACCESSのインポート機能を使う
その②｜テーブルリンク機能から取り込む
その③｜VBAでエクセルデータを取り込む
取り込みたいエクセルデータ情報
D:\開発用フォルダ\取込表ファイル.xlsx
■その①｜ACCESSのインポート機能を使う
エクセルにある表形式のデータをAccessのテーブルにインポートする機能を使用します。
まずはAccessの「外部データ」タブのエクセルのインポートボタンをクリックします。
インポートするエクセルファイルを指定して、「現在のデータベースの新しいテーブルにソースデータをインポートする」にチェックを入れ、OKをクリックします。
「先頭行をフィールド名として使う」にチェックを入れ、表の各項目が正しく区切られているか確認してから「次へ」をクリック。
フィールドのオプション設定で「データ型」を確認して次へをクリック。ここでは、下記のようにデータ型を設定・変更しました。
NO = 長整数型
商品 = 短いテキスト
単価 = 長整数型
数量 = 長整数型
金額 = 長整数型
主キーの設定を行い、「次へ」をクリック。ここでは主キーの設定はしませんが、主キーは必要に応じて設定してください。
インポート先のテーブル名を決めてから、「完了」をクリック。
今回はインポート操作の保存はしないので、「閉じる」をクリック。
これでエクセルデータがテーブルにインポートされました。
■その②｜テーブルリンク機能から取り込む
エクセルのシートをリンクテーブルとして利用する。それだけでもいいのですが、そこからテーブル化するまで行います。厳密にはインポートとは言い難いかもしれませんが、データを取り込むには有効な手段だと思います。
エクセルにある表形式のデータをAccessのテーブルにインポートする機能を使用します。まずはAccessの「外部データ」タブのエクセルのインポートボタンをクリックします。
インポートするエクセルファイルをを指定して、「リンクテーブルを作成してソースデータにリンクする」にチェックを入れ、「OK」をクリックします。
「先頭行をフィールド名として使う」にチェックを入れ、表の各項目が正しく区切られているか確認してから「次へ」をクリック。
リンクテーブル名を決めてから、「完了」をクリック。
下記メッセージが出たら、「OK」をクリック。
これでエクセルのシートがリンクテーブルとなりました。
「作成」タブの「クエリデザイン」をクリックします。
「T_取込シート」を選択して「追加」をクリックし、「閉じる」をクリックする。
クエリのフィールドに各項目を挿入して、「テーブルの作成」をクリック。
テーブル名を入力して「OK」をクリック。今回のテーブル名は「TJ_取込シート」としました。
続けて「実行」ボタンをクリックして、テーブルを新規作成する。
これでテーブルが完成しました。ただしこのままでは、データ型が想定外のものになっていることが多いので、修正します。
今回の場合、数値型がすべて「倍精度浮動小数点型」となっていましたので、「長整数型」に変更しました。
さて、これ以降に同じリンクテーブルからデータを取り込むことがあると思います。テーブルを作成するクエリをこのまま保存してもいいのですが、その都度データ型を修正しないといけなくなるので、追加クエリとして保存しましょう。デザインタブの「追加」をクリックし、追加先のテーブル名を入力して「OK」をクリックします。
最後にクエリを保存して終了です。この場合、エクセルのデータを取り込む際、いったんテーブルのデータを削除してから追加クエリを起動すればデータ型が変更することなく何度でも取り込めます。
■その③｜VBAでエクセルデータを取り込む
まずはVBAを書くために標準モジュールを用意します。「データベースツール」タブの「Visual Basic」をクリックしてVBEを開きます。
VBEの「挿入」タブから「標準モジュール」をクリックする。
標準モジュール「Module1」を作成したら下記のコードを記入して実行する。これは「T_取込シート」テーブルにエクセルの「取込シート」のデータを新規作成または追加します。今回はテーブルが存在しない状態で実行したのでテーブルが新規作成しています。また、データはエクセルシートのA1からE10の範囲を取り込みしています。データのボリュームによりこの範囲を変更して実行してください。
Sub import()
Dim filepath As String
filepath = "D:\開発用フォルダ\取込表ファイル.xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "T_取込シート", filepath, True, "取込シート!A1:E10"
End Sub
ここで「T_取込シート」テーブルができあがります。しかし、テーブルのデータ型を修正する必要があるので、データ型を確認してください。
テーブルがない状態でVBAでインポートするとテーブルが作成されます。ただし、やはりデータ型は確認、修正する必要がありますので、同じ構成の表を何度も取り込む場合はあらかじめテーブルを作成しておいてからインポートVBAを起動することとなります。今後も同じシートで新規取込をする場合は、下記のようにはじめにテーブル内容を削除してから取り込むと良いでしょう。
Sub import()
DoCmd.RunSQL "DELETE FROM T_取込シート"
Dim filepath As String
filepath = "D:\開発用フォルダ\取込表ファイル.xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "T_取込シート", filepath, True, "取込シート!A1:E10"
End Sub
■最後に
インポートをするといつの間にかインポートエラーテーブルが出来てしまう事があります。特に取得したデータがおかしいわけでもなく、取込後の処理も問題ないようなら、インポートしてすぐにインポートエラーテーブルを削除するようにしています。エラーの発生を知らせるものなので、むやみに消すことはないのですがインポート都度下記のコードを含めておくと、”インポート”と"エラー"という文字列を含むテーブルを削除してくれますので適宜ご利用ください。
Dim err_tbl As Object
Dim tbl As Object
Set err_tbl = CurrentDb.TableDefs
Dim del_Table As Collection
Set del_Table = New Collection
For Each tbl In err_tbl
If tbl.Name Like "*インポート*エラー*" Then
del_Table.Add tbl.Name
End If
Next
For i = 1 To del_Table.Count
err_tbl.Delete (del_Table(i))
Next
以上エクセルのデータをAccessのテーブルにインポートする方法でした。
