この記事では、Excelファイルの表の値をAccessのテーブルに追加する方法についてご説明します。
【動画】Excelファイルの表の値をAccessのテーブルに追加する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
Excelのシート名を取得し、そのシート名を元にSELECT文を実行して表の値を取得します。
取得した値をINSERT文を実行してテーブル「T_生徒」に追加しています。
コード作成の流れ
AccessのデータベースファイルとExcelファイルの例
今回は次のAccessのデータベースファイルとExcelファイルを用意しました。
Accessのデータベースファイル
Accessのデータベースファイルには「T_生徒」というテーブルがあり、このテーブルにExcelファイルのシートにある表のデータを追加します。
今回フォーム「frm_work」を用意し、設置された実行ボタンをクリックするとコードが実行されます。
ボタン名は「btn_exec」と設定しています。
Excelファイル
Excelファイルには3つのシートがあり、このシートにそれぞれデータが用意されています。
この表のデータをAccessのデータベースファイルのテーブル「T_生徒」に追加します。
Excelファイルの表の値をAccessのデータベースファイルのテーブル「T_生徒」に追加した結果
Excelファイルの表の値をAccessのデータベースファイルのテーブル「T_生徒」に追加した結果は下のとおりです。
テーブル「T_生徒」に、Excelファイルの「1月」「2月」「3月」のデータが追加されています。
コードの例
Option Compare Database
Option Explicit
Private Sub btn_exec_Click()
Dim excelFile As String 'データのあるExcelファイル
Dim cn As Object 'Connection用変数
Dim db As DAO.Database 'DAOデータベース用オブジェクト用変数
Dim tdf As DAO.TableDef 'テーブル定義情報用変数
Dim fld As DAO.field 'テーブルのフィールド用変数
Dim strSQL As String 'SQL文用変数
Dim aryCnt As Long '配列用カウンタ
Dim eRs As Object 'レコードセット用変数(Excelのシート取得用)
Dim dRs As Object 'レコードセット用変数(Excelのデータ取得用)
Dim cnt As Long 'カウンタ
Dim fldType() As Variant 'フィールドの型格納用配列
Dim fldNM() As Variant 'フィールド名格納用配列
Dim fldVal() As Variant 'Excelの表の値格納用配列
'Excelのデータを追加するテーブル名を取得する
Const insTBL As String = "T_生徒"
'Excelファイルのフルパスを取得する
excelFile = Application.CurrentProject.Path & "\0003.xlsm"
'自分自身のデータベースを開く
Set db = CurrentDb
'テーブルの情報を取得するためTableDefsのインスタンスを生成する
Set tdf = db.TableDefs(insTBL)
'配列getvalを再定義する
ReDim getval(1, 0)
'フィールドの情報を繰り返し取得するFor文
For Each fld In tdf.Fields
'配列fldTypeを再定義する
ReDim Preserve fldType(aryCnt)
'配列fldNMを再定義する
ReDim Preserve fldNM(aryCnt)
'配列getvalにフィールドの型を格納する
fldType(aryCnt) = fld.Type
'配列getvalにフィールド名を格納する
fldNM(aryCnt) = fld.Name
aryCnt = aryCnt + 1
Next fld
aryCnt = 0
'Connectionオブジェクトのインスタンスを生成する
Set cn = CreateObject("ADODB.Connection")
'Excelファイルへの接続情報の取得
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & excelFile & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
'Excelファイルに接続する
cn.Open
'OpenSchemaメソッドを実行してExcelファイルのテーブル情報を取得しeRsに格納する
Set eRs = cn.OpenSchema(20)
'Excelのデータ取得用のRecordsetオブジェクトのインスタンスを生成する
Set dRs = CreateObject("ADODB.Recordset")
'Excelファイルのシートの数分処理を繰り返すループ
Do Until eRs.EOF
If eRs.Fields("TABLE_TYPE").Value = "TABLE" Then
'eRsのレコードセットのフィールド「TABLE_TYPE」が「TABLE」の場合
'Excelファイルのシート名を取得する
strSQL = "SELECT * FROM "
strSQL = strSQL & "[" & eRs.Fields("TABLE_NAME").Value & "]"
'SELECT文を実行する
dRs.Open strSQL, cn
'Excelの表のデータ件数分ループ
Do Until dRs.EOF
'フィールドの数分ループするFor文
For cnt = 0 To UBound(fldNM) - 1
'配列fldValを再定義する
ReDim Preserve fldVal(aryCnt)
Select Case fldType(aryCnt)
Case dbByte, dbInteger, dbLong, dbSingle, dbDouble, dbCurrency, dbDecimal
'フィールドのデータ型が数値型の場合
'配列fldValにExcelのシートの値を格納する
fldVal(aryCnt) = dRs.Fields(fldNM(aryCnt)).Value
Case Else
'フィールドのデータ型が数値型以外の場合
'配列fldValにExcelのシートの値を格納する
fldVal(aryCnt) = "'" & dRs.Fields(fldNM(aryCnt)).Value & "'"
End Select
aryCnt = aryCnt + 1
Next cnt
'配列fldValを再定義する
ReDim Preserve fldVal(aryCnt)
'配列fldValにシート名を格納する
fldVal(aryCnt) = Replace(eRs.Fields("TABLE_NAME").Value, "$", "")
'INSERT文を生成する
strSQL = "INSERT INTO " & insTBL
strSQL = strSQL & " ("
'フィールド名の数だけ処理を繰り返すFor文
For cnt = 0 To UBound(fldNM)
'生成途中のINSERT文にフィールド名を追加する
strSQL = strSQL & fldNM(cnt) & ", "
Next cnt
'生成途中のINSERT文の末尾のカンマとスペースを削除する
strSQL = RTrim(strSQL)
strSQL = Left(strSQL, Len(strSQL) - 1)
'生成途中のINSERT文に「) VALUES (」の文字列を追加する
strSQL = strSQL & ") VALUES ("
'Excelの表にある値の数だけ処理を繰り返すFor文
For cnt = 0 To UBound(fldVal)
'生成途中のINSERT文にExcelの表の値を追加する
strSQL = strSQL & fldVal(cnt) & ", "
Next cnt
'生成途中のINSERT文の末尾のカンマとスペースを削除する
strSQL = RTrim(strSQL)
strSQL = Left(strSQL, Len(strSQL) - 1)
'生成途中のINSERT文に「)」の文字を追加する
strSQL = strSQL & ")"
'Insert文を実行する
DoCmd.RunSQL strSQL
'Recordsetのレコードのカーソルを次に移動する
dRs.MoveNext
aryCnt = 0
Loop
End If
cnt = cnt + 1
'Recordsetを閉じる
dRs.Close
'Recordsetのレコードのカーソルを次に移動する
eRs.MoveNext
Loop
'Recordsetを閉じる
eRs.Close
'後処理
Set tdf = Nothing
Set db = Nothing
Set fld = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは22行目から25行目です。
'Excelのデータを追加するテーブル名を取得する
Const insTBL As String = "T_生徒"
'Excelファイルのフルパスを取得する
excelFile = Application.CurrentProject.Path & "\0003.xlsm"
コードの説明
以上のコードは、Excelのデータを追加するテーブル名とExcelファイルのフルパスを取得しているコードです。
注目すべきコード②
次に見て頂きたいのは28行目です。
'自分自身のデータベースを開く
Set db = CurrentDb
以上のコードは、Accessのデータベースの操作を行うのに必要なインスタンスを生成する処理のコードです。
生成されたインスタンスは、自分自身のAccessのデータベースファイルのテーブルの情報の取得するのに使います。
注目すべきコード③
次に見て頂きたいのは31行目です。
'テーブルの情報を取得するためTableDefsのインスタンスを生成する
Set tdf = db.TableDefs(insTBL)
コードの説明
以上のコードは、テーブルの情報を取得するためTableDefsのインスタンスを生成する処理のコードです。
TableDefsのインスタンスを生成することで、テーブルのフィールド名や型を取得することができるようになります。
注目すべきコード④
次に見て頂きたいのは37行目から53行目です。
'フィールドの情報を繰り返し取得するFor文
For Each fld In tdf.Fields
'配列fldTypeを再定義する
ReDim Preserve fldType(aryCnt)
'配列fldNMを再定義する
ReDim Preserve fldNM(aryCnt)
'配列getvalにフィールドの型を格納する
fldType(aryCnt) = fld.Type
'配列getvalにフィールド名を格納する
fldNM(aryCnt) = fld.Name
aryCnt = aryCnt + 1
Next fld
コードの説明
以上のコードは、テーブルのフィールド名と型を取得して配列に格納する処理のコードです。
テーブルのフィールド名はINSERT文を生成するのに使い、フィールドの型が数値型のものなのか、それ以外なのかを判定するのに使います。
コードの詳細
37行目のコードは、フィールドの情報を繰り返し取得するFor文です。
40行目と43行目のコードは、フィールドの型と名前を格納する配列をReDim Preserveを使って再定義しているコードです。
46行目のコードではフィールドの型を配列fldTypeに、49行目のコードではフィールドの名前を配列fldNMに格納しています。
配列に値を格納したら新たに取得する値を配列に格納したいので、1つ要素数を増やして値を格納できるように40行目と43行目のコードで再度配列をReDim Preserveを使って再定義します。
注目すべきコード⑤
次に見て頂きたいのは58行目から69行目です。
'Connectionオブジェクトのインスタンスを生成する
Set cn = CreateObject("ADODB.Connection")
'Excelファイルへの接続情報の取得
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & excelFile & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
'Excelファイルに接続する
cn.Open
'OpenSchemaメソッドを実行してExcelファイルのテーブル情報を取得しeRsに格納する
Set eRs = cn.OpenSchema(20)
コードの説明
以上のコードは、Connectionオブジェクトのインスタンスを生成し、Excelファイルへの接続後、OpenSchemaメソッドを実行してExcelファイルのテーブル情報を取得する処理のコードです。
OpenSchemaを実行することで、Excelファイルのシート名を取得することができます。
コードの説明
58行目のコードは、Connectionオブジェクトのインスタンスを生成するコードです。
ConnectionオブジェクトのインスタンスがないとExcelのファイルに接続することができないので必ず生成しておきます。
61行目から63行目のコードは、Excelファイルに接続するための接続情報を用意するコードです。
62行目のData Sourceには、接続するExcelファイルのフルパスを指定します。
66行目のコードでは、Openメソッドを実行してExcelファイルに接続します。
69行目のコードでは、OpenSchemaメソッドを実行してExcelファイルのテーブル情報を取得します。
テーブル情報には、Excelファイルのシート名も含まれます。
注目すべきコード⑥
次に見て頂きたいのは72行目です。
'Excelのデータ取得用のRecordsetオブジェクトのインスタンスを生成する
Set dRs = CreateObject("ADODB.Recordset")
コードの説明
以上のコードは、Excelのデータ取得用のRecordsetオブジェクトのインスタンスを生成する処理のコードです。
このインスタンスを生成することで、SELECT文を実行したり、SELECT文を実行して取得した値を扱うことができるようになります。
注目すべきコード⑦
次に見て頂きたいのは75行目です。
'Excelファイルのシートの数分処理を繰り返すループ
Do Until eRs.EOF
コードの説明
以上のコードは、Excelファイルのシートの数分処理を繰り返すループのDO文のコードです。
今回のExcelファイルのサンプルでは、「1月」「2月」「3月」の3つのシートがあるので、3回ループします。
注目すべきコード⑧
次に見て頂きたいのは77行目です。
If eRs.Fields("TABLE_TYPE").Value = "TABLE" Then
'eRsのレコードセットのフィールド「TABLE_TYPE」が「TABLE」の場合
コードの説明
以上のコードは、eRs.Fieldsの「TABLE_TYPE」が「TABLE」かどうかを判定するコードです。
「TABLE」の場合はシートなので82行目以降の処理を行います。
注目すべきコード⑨
次に見て頂きたいのは82行目から86行目です。
'Excelファイルのシート名を取得する
strSQL = "SELECT * FROM "
strSQL = strSQL & "[" & eRs.Fields("TABLE_NAME").Value & "]"
'SELECT文を実行する
dRs.Open strSQL, cn
コードの説明
以上のコードは、Excelのシートの表からデータを取得するSELECT文を実行する処理のコードです。
例えば「1月」のシートに対して実行するSELECT文は以下のとおりです。
SELECT
*
FROM
['1月$']
注目すべきコード⑩
次に見て頂きたいのは89行目です。
'Excelの表のデータ件数分ループ
Do Until dRs.EOF
コードの説明
以上のコードは、Excelの表のデータ件数分ループするDo文です。
注目すべきコード⑪
次に見て頂きたいのは92行目から117行目です。
'フィールドの数分ループするFor文
For cnt = 0 To UBound(fldNM) - 1
'配列fldValを再定義する
ReDim Preserve fldVal(aryCnt)
Select Case fldType(aryCnt)
Case dbByte, dbInteger, dbLong, dbSingle, dbDouble, dbCurrency, dbDecimal
'フィールドのデータ型が数値型の場合
'配列fldValにExcelのシートの値を格納する
fldVal(aryCnt) = dRs.Fields(fldNM(aryCnt)).Value
Case Else
'フィールドのデータ型が数値型以外の場合
'配列fldValにExcelのシートの値を格納する
fldVal(aryCnt) = "'" & dRs.Fields(fldNM(aryCnt)).Value & "'"
End Select
aryCnt = aryCnt + 1
Next cnt
コードの説明
以上のコードは、Excelのシートの表にある値を取得する処理のコードです。
取得した値は、配列fldValに格納します。
なお、取得した値を配列fldValに格納する前に、取得した値が数値型なのかそれ以外なのかを判定します。
数値型なのかそれ以外なのかを判定する理由は、INSERT文を生成するのに追加する値が数値型のものならそのままで、数値型以外なら追加する値を「’」(カンマ)で囲む必要があるからです。
生成したINSERT文では、値が数値型のものならそのままで、数値型以外なら追加する値を「’」(カンマ)で囲っています。
コードの詳細
92行目のコードは、フィールドの数分ループするFor文です。
95行目のコードでは、Excelのシートから取得した値を格納するための配列fldValを再定義します。
97行目はフィールドの型が数値型のものなのか、それ以外なのかを判定するSelect Case文で、数値型の場合は104行目で配列fldValにExcelのシートから取得した値を格納します。
数値型以外の場合は111行目で、Excelのシートから取得した値に「’」(カンマ)で囲って配列fldValに格納します。
注目すべきコード⑫
次に見て頂きたいのは123行目です。
'配列fldValにシート名を格納する
fldVal(aryCnt) = Replace(eRs.Fields("TABLE_NAME").Value, "$", "")
コードの説明
以上のコードは、配列fldValにシート名を格納する処理のコードです。
注目すべきコード⑬
次に見て頂きたいのは125行目から160行目です。
'INSERT文を生成する
strSQL = "INSERT INTO " & insTBL
strSQL = strSQL & " ("
'フィールド名の数だけ処理を繰り返すFor文
For cnt = 0 To UBound(fldNM)
'生成途中のINSERT文にフィールド名を追加する
strSQL = strSQL & fldNM(cnt) & ", "
Next cnt
'生成途中のINSERT文の末尾のカンマとスペースを削除する
strSQL = RTrim(strSQL)
strSQL = Left(strSQL, Len(strSQL) - 1)
'生成途中のINSERT文に「) VALUES (」の文字列を追加する
strSQL = strSQL & ") VALUES ("
'Excelの表にある値の数だけ処理を繰り返すFor文
For cnt = 0 To UBound(fldVal)
'生成途中のINSERT文にExcelの表の値を追加する
strSQL = strSQL & fldVal(cnt) & ", "
Next cnt
'生成途中のINSERT文の末尾のカンマとスペースを削除する
strSQL = RTrim(strSQL)
strSQL = Left(strSQL, Len(strSQL) - 1)
'生成途中のINSERT文に「)」の文字を追加する
strSQL = strSQL & ")"
'Insert文を実行する
DoCmd.RunSQL strSQL
コードの説明
以上のコードは、Excelのシートの値をテーブルに追加するINSERT文を生成して実行する処理のコードです。
今回のサンプルで実行されるINSERT文の一つを以下にお見せします。
INSERT INTO T_生徒(
名前,
国語,
数学,
理科,
社会,
英語,
シート名
)
VALUES(
'生徒1',
63,
90,
78,
66,
66,
'1月'
)
生成したINSERT文をRunSQLメソッドの引数に指定して実行すると、Insert文が実行されてテーブルにデータが追加されます。
コードの詳細
まずは126行目からINSERT文を組み立てていきます。
insTBLは追加先のテーブルです。
130行目から135行目のループでは、配列fldNMからフィールド名を取得してINSERT文に組み込んでいきます。
138行目と139行目ではINSERT文の生成途中で不要な末尾のスペース文字と「,」(カンマ)を削除し、142行目で「) VALUES (」の文字列を追加します。
145行目から150行目のループでは、配列fldValからExcelファイルから取得した値を取得してINSERT文に組み込んでいきます。
153行目と154行目ではINSERT文の生成途中で不要な末尾のスペース文字と「,」(カンマ)を削除し、157行目で「)」の文字列を追加します。
160行目のコードでは、生成したINSERT文をRunSQLメソッドの引数に指定して実行しています。
注目すべきコード⑭
次に見て頂きたいのは163行目です。
'Recordsetのレコードのカーソルを次に移動する
dRs.MoveNext
コードの説明
以上のコードは、Excelのシートのデータを持つRecordsetのレコードのカーソルを次に移動する処理のコードです。
例えば1つ目のデータを参照した後に2つ目のデータを参照するにはこのMoveNextを実行しないといけません。
MoveNextを実行しないと永久に1つ目のデータを参照し続けます。
なので、2つ目、3つ目・・・と順々にデータを参照するためには忘れずにMoveNextメソッドを実行します。
注目すべきコード⑮
次に見て頂きたいのは174行目から177行目です。
'Recordsetを閉じる
dRs.Close
'Recordsetのレコードのカーソルを次に移動する
eRs.MoveNext
コードの説明
以上のコードは、Excelのシートのデータを保持するRecordsetを閉じる処理と、Excelのシート名を持つRecordsetのレコードのカーソルを次に移動する処理のコードです。
1つのシートにある全てのレコードに対するINSERT文を作ったので、次のシート用にINSERT文を作るためここでRecordsetを閉じておきます。
また、次のシートのデータを取得するため、シートを保持しているRecordsetのインスタンスeRsのMoveNextメソッドを実行します。
MoveNextを実行しないと永久に同じシート名を参照し続けます。
なので、2つ目、3つ目・・・と順々にシート名を参照するためには忘れずにMoveNextメソッドを実行します。
注目すべきコード⑯
次に見て頂きたいのは182行目です。
'Recordsetを閉じる
eRs.Close
以上のコードは、Excelのシートのデータを保持するRecordsetを閉じる処理のコードです。
このコード以降はExcelのシート名を持つRecordsetのレコードは使わないのと、Recordsetが開いたままサブルーチンが終わってしまうので、Recordsetを閉じておきます。
動作確認
「AccessのデータベースファイルとExcelファイルの例」をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft Office 15.0 Access databese engine Object Library(ACEDAO.DLL)
なぜ必要かというと、Excelのマクロのコードの8行目の「DAO.Database」、9行目の「DAO.TableDef」、10行目の「DAO.field」というオブジェクトが「msado28.tlb」を、7行目の「dao.Database」というオブジェクトが「ACEDAO.DLL」というファイルを参照するからです。
Dim db As DAO.Database 'DAOデータベース用オブジェクト用変数
Dim tdf As DAO.TableDef 'テーブル定義情報用変数
Dim fld As DAO.field 'テーブルのフィールド用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「ACEDAO.DLL」は何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「DAO.Database」「DAO.TableDef」「DAO.field」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、Excelファイルの表の値をAccessのテーブルに追加する方法についてご説明しました。
Excelファイルの表の値をAccessのテーブルに追加したいときは本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
