この記事では、AccessのテーブルデータをExcelのシートに貼り付ける方法についてご説明します。
【動画】AccessのテーブルデータをExcelのシートに貼り付ける実際の動き
本題に入る前に、まずは次のツイートをご覧ください。
Excelの最大行数を超える件数のAccessのテーブルデータをExcelのシートに貼り付ける処理を動画に撮ってみました☺️
テスト用に用意した630万件データの貼付完了に約12分かかるので、動画を2分程度に退縮してあります💦
(主にシートにデータを貼り付けるところをカットしました😅)#ExcelVBA#Access pic.twitter.com/HShsU4YtZt— まさ@Excel、VBA、マクロ（経験年数16年） (@masamasa9785) August 19, 2021
マクロからAccessのテーブルデータを取得し、Excelのシートに貼り付けています。
Excelのシートの最大行数を超えるデータはどうやって貼り付けるの？
Excelのシートの最大行数は1,048,576行ですが、その行数を超えるデータが存在する場合にそのデータ全てをどうやってExcelのシートに貼り付けるの？という疑問が出てくるかと思います。
確かに1シートにはデータ全てを貼り付けることはできません。
そこで今回は、次の処理の流れでAccessのテーブルデータをExcelのシートに貼り付けていくことにしました。
- ①取得元のAccessのテーブルと同じテーブルを作成し、そのテーブル(※)にデータをコピーする
※作業テーブルと呼ぶことにします。 - ② ①の作業テーブルから上位1,048,575件のデータをExcelのマクロが取得してExcelのシートに貼り付ける
※Excelシートの1行目は列名で使いたいので、1,048,575件でデータを取得しています。 - ③作業テーブルから、②で取得したデータを削除する
- ④ 作業テーブルからデータが無くなるまで②から④を繰り返す
処理の説明
なぜ取得元のAccessのテーブルと同じテーブルの作成・データコピーをしているのかというと、取得元のAccessのテーブルのデータを触らせない(削除処理をさせない)ためです。
今回の方法は取得元のAccessのテーブルの「データ」に対して削除処理を行わないといけないので、どうしても取得元のAccessのテーブルのデータを別テーブルに移しておき、そちらで削除処理を行いたいのです。
なので、取得元のAccessのテーブルと同じ構造のテーブル(作業テーブル)の作成・データコピーをします。
ここで行う削除処理とは、「テーブルのデータの先頭からx件を取得した後にそのデータを削除すること」です。
例えばテーブルに2000件データがあったとして、「データの先頭から数えて555番目に存在するデータから連続する200件のデータを取得する」にはどうすればいいのか？というと、
「DELETE * FROM (SELECT TOP 555 * FROM テーブル名)」のSQL文で先頭から555件のデータを削除します。(残ったデータ件数は1445件)
削除後に残されたデータの先頭は、2000件データがあった時から見て556件目になります。
残されたデータに対してSELECT文「SELECT TOP 200 * FROM テーブル名」を実行することで、「データの先頭から数えて555番目に存在するデータから連続する200件のデータを取得する」ことができます。
AccessのテーブルデータをExcelのシートに貼り付ける方法
AccessのテーブルデータをExcelのシートに貼り付けるには、次の流れの通りにコードを書いていきます。
Excelのマクロ
このインスタンスは、Accessのテーブルデータの参照・挿入に必要です。
この設定により、Recordsetオブジェクトを開いたり操作することができるようになります。
同じ構造のテーブルを作成し、そのテーブルにデータをコピーするにはSQL文の「SELECT INTO」を使います。
SELECT INTOは、あるテーブルから同じ構造のテーブルを作成し、そのテーブルにデータを複製します。
取得するデータはTOP句を使い、Excelシートに貼り付けたい件数分の上位のデータを取得します。(【例】 SELECT TOP 1048575 * FROM wktbl)
貼り付けるデータが無くなったら次のSTEPに進みます。
コードの例
Excelのマクロのコード(例)
Dim DBName As String 'データベース名
Dim tblNM As String '取得元のテーブル名
Dim connDB As String 'データベース接続情報
Dim sqlStr As String 'SQL文
Dim cntF As Long 'カウンタ1
Dim cntS As Long 'カウンタ2
Dim DNmCnt As Integer 'データ件数カウンタ
Dim dataNum As Long 'データ総数
Dim r As Long '2次元配列用変数
Dim c As Long '2次元配列用変数
Dim getVal() As Variant '取得したデータ格納用並列
Dim wk_getVal() As Variant '縦横入れ替え用配列
Dim ws As Worksheet 'ワークシート用変数
Dim adoCON As New ADODB.Connection 'Connection用変数
Dim adoRS As ADODB.Recordset 'レコードセット用変数
Dim objCat As New ADOX.catalog 'データソースのスキーマカタログを記述するコレクション
Dim objTable As New ADOX.Table '列、インデックス、およびキーを含むデータベーステーブル
'Excelの最大行数
Const rowMaxCnt As Long = 1048575
'作業テーブル名(主に取得元のテーブルデータを格納するのに使う)
Const wk_tblNM As String = "wktbl"
'データ件数カウンタを初期化する
DNmCnt = 0
'カレントディレクトリのデータベースパスを取得
DBName = ActiveWorkbook.Path & "\" & "0062.mdb"
'データを取得するテーブル名を取得
tblNM = "tbl_info_list"
'データベース接続情報の取得
adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" _
& DBName & ""
'Accessに接続する
adoCON.Open connDB
'Recordsetオブジェクトのインスタンスを生成する
Set adoRS = CreateObject("ADODB.Recordset")
'クライアントサイドカーソルに変更
adoRS.CursorLocation = 3
'先ほど取得したデータベース接続情報が設定されているadoCONをActiveConnectionプロパティに設定し、
'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
'⇒Recordsetオブジェクトを開いたり操作するのに必要な設定
adoRS.ActiveConnection = adoCON
'データの件数を取得する
sqlStr = "select count(*) from " & tblNM
adoRS.Open sqlStr, adoCON, adOpenDynamic
'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
getVal = adoRS.GetRows
'レコードセットを閉じる
adoRS.Close
'取得したデータの件数を変数に格納する
dataNum = getVal(0, 0)
'先ほど取得したデータベース接続情報が設定されているadoCONをActiveConnectionプロパティに設定し、
'Connectionオブジェクトとcatalogオブジェクトを関連付ける
'⇒作業テーブルがAccessに存在するか確認するために必要な処理
objCat.ActiveConnection = adoCON
For Each objTable In objCat.Tables
'存在するテーブルやクエリ、フォームなどの数だけループさせる
If objTable.Type = "TABLE" Then
'「TABLE」の場合
If objTable.Name = wk_tblNM Then
'作業テーブルが存在する場合
'作業テーブルを削除する
sqlStr = "DROP TABLE "
sqlStr = sqlStr & wk_tblNM
adoRS.Open sqlStr, adoCON, adOpenDynamic
Exit For
End If
End If
Next
'catalogオブジェクトへの参照解除
Set objCat = Nothing
'取得元のテーブルデータを作業テーブルにコピーする
sqlStr = "SELECT * INTO " & wk_tblNM
sqlStr = sqlStr & " from " & tblNM
adoRS.Open sqlStr, adoCON, adOpenDynamic
Do
'Excelのシートに入る最大行数分のデータを取得する
sqlStr = "SELECT TOP " & rowMaxCnt & " * FROM " & wk_tblNM
adoRS.Open sqlStr, adoCON, adOpenDynamic
'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
getVal = adoRS.GetRows
'レコードセットを閉じる
adoRS.Close
'2次元配列の配列のサイズを取得する
r = UBound(getVal, 1) + 1
c = UBound(getVal, 2) + 1
'縦横入れ替え用配列のメモリ領域を再割り当てする
ReDim wk_getVal(UBound(getVal, 2), UBound(getVal, 1))
'ディレクトリ＆ファイルの一覧編集処理後に配列の縦横を別の配列に入れ替える処理
'※配列の縦横を入れ替えるのにtranseposeメソッドを使いたいが、
' Excel2013ではtranseposeメソッドが対応する配列の要素数が65536までしかないので､
' その回避策として、1つ配列を用意してその配列に縦横を入れ替えたデータを移し替える
For cntF = 0 To UBound(getVal, 1)
For cntS = 0 To UBound(getVal, 2)
'作業テーブルから取得したデータを格納した配列の縦横を入れ替える
wk_getVal(cntS, cntF) = getVal(cntF, cntS)
DoEvents
Next
Next
'シートを作成する
Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
.Name = "data" & DNmCnt + 1
'recordsetからフィールド名を取得してセルに設定する
For cntF = 0 To adoRS.Fields.Count - 1
Sheets("data" & DNmCnt + 1).Range(Sheets("data" & DNmCnt + 1).Cells(1, cntF + 1), Sheets("data" & DNmCnt + 1).Cells(1, cntF + 1)).Value = adoRS.Fields.Item(cntF).Name
Next
'Accessから取得したデータをセルに貼り付ける
Sheets("data" & DNmCnt + 1).Range("A2").Resize(c, r).Value = wk_getVal
'作業テーブルのデータを、先ほど取得したExcelのシートに入る最大行数分削除する
sqlStr = "DELETE * FROM (SELECT TOP " & rowMaxCnt & " * FROM " & wk_tblNM & ")"
adoRS.Open sqlStr, adoCON, adOpenDynamic
'カウンタを1増やす
DNmCnt = DNmCnt + 1
DoEvents
Loop While (dataNum > (rowMaxCnt * DNmCnt) + 1)
'作業テーブルを削除する
sqlStr = "DROP TABLE "
sqlStr = sqlStr & wk_tblNM
adoRS.Open sqlStr, adoCON, adOpenDynamic
'各終了処理
adoCON.Close
If Not adoRS Is Nothing Then Set adoRS = Nothing
If Not adoCON Is Nothing Then Set adoCON = Nothing
コードの解説
注目すべきコード①
最初に見て頂きたいのは139行目です。
'Accessから取得したデータをセルに貼り付ける
Sheets("data" & DNmCnt + 1).Range("A2").Resize(c, r).Value = wk_getVal
この139行目でAccessから取得したデータをセルに貼り付けています。
Accessの作業テーブルから取得したデータが格納された配列wk_getValを、ExcelのシートのセルA2に貼り付けています。
注目すべきコード②
次に見て頂きたいのは94行目から96行目です。
'取得元のテーブルデータを作業テーブルにコピーする
sqlStr = "SELECT * INTO " & wk_tblNM
sqlStr = sqlStr & " from " & tblNM
adoRS.Open sqlStr, adoCON, adOpenDynamic
取得元のテーブルデータを作業テーブルにコピーします。
この作業テーブルから、Excelのシートの貼り付けるデータを取得し、その後に作業テーブルからその取得したデータを削除していきます。
※SELECT INTOは、あるテーブルから同じ構造のテーブルを作成し、そのテーブルにデータを複製します。
先ほどご紹介した、取得元のテーブルデータを作業テーブルにコピーするSQL文です。(94行目と95行目)
先ほどのコードではSQL文が分かりにくいと思いますので、実際のSQL文をご紹介します。
SELECT * INTO wktbl from tbl_info_list
テーブルtbl_info_listを元に、作業テーブルwktblを作成してデータをコピーします。
注目すべきコード③
次に見て頂きたいのは100行目から104行目です。
'Excelのシートに入る最大行数分のデータを取得する
sqlStr = "SELECT TOP " & rowMaxCnt & " * FROM " & wk_tblNM
adoRS.Open sqlStr, adoCON, adOpenDynamic
'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
getVal = adoRS.GetRows
「注目すべきコード②」で作成した作業テーブルからテーブルデータを取得し、配列getValに格納します。
取得するデータはTOP句を使い、Excelシートに貼り付けたい件数分の上位のデータを取得します。
先ほどご紹介した、Excelシートに貼り付けたい件数分の上位のデータを取得するSQL文です。(100行目)
先ほどのコードではSQL文が分かりにくいと思いますので、実際のSQL文をご紹介します。
SELECT TOP 1048575 * FROM wktbl
上位1,048,575件のデータを取得するSelect文は上記となります。
※Excelシートの1行目は列名で使いたいので、データを1,048,575件で取得しています。
注目すべきコード④
次に見て頂きたいのは120行目から127行目です。
'ディレクトリ＆ファイルの一覧編集処理後に配列の縦横を別の配列に入れ替える処理
'※配列の縦横を入れ替えるのにtranseposeメソッドを使いたいが、
' Excel2013ではtranseposeメソッドが対応する配列の要素数が65536までしかないので､
' その回避策として、1つ配列を用意してその配列に縦横を入れ替えたデータを移し替える
For cntF = 0 To UBound(getVal, 1)
For cntS = 0 To UBound(getVal, 2)
'作業テーブルから取得したデータを格納した配列の縦横を入れ替える
wk_getVal(cntS, cntF) = getVal(cntF, cntS)
DoEvents
Next
Next
Accessのテーブルから取得したデータを格納した配列は、データの縦横が逆になっているので、そのままExcelのシートに貼り付けることができません。
なので、120行目から127行目の通り配列の縦横を入れ替える必要があります。
配列の縦横を入れ替えずにそのままExcelのシートに貼り付けた場合
列が3つのAccessのテーブルデータの場合、そのテーブルデータをマクロ側が受け取って配列に格納すると、データの縦横が逆になってしまいます。
縦横が逆になっているので2行だけデータが貼り付けられていますが、3行目以降のデータが「#N/A」と表示されており、正しくデータが貼り付きません。
配列の縦横を入れ替えてからExcelのシートに貼り付けた場合
Accessのテーブルデータが正しく2列で貼り付けられています。
ただし、Excel2013以降のExcelをお使いならこの120行目から127行目のコードの通りに書かなくても、transeposeメソッドで構いません。
Excel2013だとtranseposeメソッドが正しく機能しないため、transeposeメソッドは使えません。
Excel2013だとtranseposeメソッドが正しく機能するのは、配列の要素数が65536までです。
Excel2013はをお使いなら120行目から127行目のコードの通りに書かないと配列の縦横を入れ替えることができません。
配列の要素数が65537を超えると正しく機能しません。
配列の要素数が65536以内の場合
正しく配列の値がExcelのシートに貼り付けられています。
配列の要素数が65536を超える場合
配列の値ではない想定外の値が貼り付けられています。
transeposeメソッドのバグというわけではないのですが、Excel2013でのtranseposeメソッドの仕様であるとの理解が正しいでしょう。
Excel2013はをお使いなら120行目から127行目のコードの通りに書いておくのが無難です。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
- Microsoft ADO Ext. 2.8 for DDL and Security(msadox28.tlb)
なぜ必要かというと、先ほどのコードの14行目の「ADODB.Connection」と15行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを、16行目の「ADOX.catalog」と17行目の「ADOX.Table」というオブジェクトが「msadox28.tlb」というファイルを参照するからです。
Dim adoCON As New ADODB.Connection 'Connection用変数
Dim adoRS As ADODB.Recordset 'レコードセット用変数
Dim objCat As New ADOX.catalog 'データソースのスキーマカタログを記述するコレクション
Dim objTable As New ADOX.Table '列、インデックス、およびキーを含むデータベーステーブル
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」「msadox28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」「ADOX.catalog」「ADOX.Table」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、AccessのテーブルのデータをExcelのシートに貼り付ける方法についてご説明しました。
AccessのテーブルのデータをExcelのシートに貼り付ける処理の流れは次の通りです。
- ①取得元のAccessのテーブルデータ件数を取得する
- ②Accessのテーブルと同じテーブルを作成し、そのテーブルにデータをコピーする
- ③作業テーブルから、Excelのシートに貼り付けたい件数のデータを取得する
- ④ ③のデータを格納した配列の縦横を入れ替える
- ⑤シートを作成
- ⑥ ④のデータを⑤で作成したシートに貼り付ける
- ⑦ ③のデータを作業テーブルから削除する
- ⑥ Accessのテーブルデータの件数がExcelのシートの最大行数を超える場合は③から⑦を繰り返す(貼り付けるデータが無くなったら⑨に進む)
- ⑨ 作業テーブルを削除する
かなりザックリしていますが、上記が処理の流れです。
AccessのテーブルのデータをExcelのシートに貼り付けたい時は本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
