この記事では、SQL ServerのテーブルのデータをExcelのシートに貼り付ける方法についてご説明します。
【動画】SQL ServerのテーブルのデータをExcelのシートに貼り付ける実際の動き
本題に入る前に、まずは次のツイートをご覧ください。
Excelの最大行数を超える件数のSQLServerのテーブルデータをExcelのシートに貼り付ける処理を動画に撮ってみました☺️
630万件データの貼付完了に約18分かかるので、動画を2分程度に短縮してあります😅
(シートにデータを貼り付けるところを主にカットしました💦)#ExcelVBA#SQLServer pic.twitter.com/grLJuAYNPx— まさ@Excel、VBA、マクロ（経験年数16年） (@masamasa9785) August 13, 2021
マクロからSQL Serverのテーブルデータを取得し、Excelのシートに貼り付けています。
Excelのシートの最大行数を超えるデータはどうやって貼り付けるの？
Excelのシートの最大行数は1,048,576行ですが、その行数を超えるデータが存在する場合にそのデータ全てをどうやってExcelのシートに貼り付けるの？という疑問が出てくるかと思います。
確かに1シートにはデータ全てを貼り付けることはできません。
そこで今回は、Excelのシートの最大行数を超えるデータが存在する場合はExcelのシートを新規で作成し、そのシートに貼り付けるようにしました。
1件目～1,048,576件目は1シートを作成してそのシートに貼り付けて、1,048,577件目から2,097,152件目はさらにもう一つシート作成してそのシートに貼り付けて、・・・・といった感じで新規でシートを作成してそのシートにデータを貼り付けていきます。
SQL ServerのテーブルのデータをExcelのシートに貼り付ける方法
SQL ServerのテーブルのデータをExcelのシートに貼り付けるには、次の流れの通りにコードを書いていきます。
Excelのマクロ
このインスタンスは、マクロがSQL Serverに接続するために必要です。
このインスタンスは、SQL Serverのテーブルデータの参照・挿入に必要です。
貼り付けるデータが無くなったら処理を終了します。
コードの例
Excelのマクロのコード(例)
Dim DBName 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 oCon As ADODB.Connection 'Connection用変数
Dim oRS As ADODB.Recordset 'レコードセット用変数
'Excelの最大行数
Const rowMaxCnt As Long = 1048575
'初期化する
DNmCnt = 0
'DBの名前
DBName = "testDataDB"
'データベース接続情報を取得
connDB = "Provider=SQLNCLI11.1;"
connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;"
connDB = connDB & "Initial Catalog=" & DBName & ";"
connDB = connDB & "Trusted_Connection=yes;"
'Connectionオブジェクトのインスタンスを生成する
Set oCon = New ADODB.Connection
'SQL Serverに接続する
oCon.Open connDB
'Recordsetオブジェクトのインスタンスを生成する
Set oRS = New ADODB.Recordset
'先ほど取得したデータベース接続情報が設定されているoConをActiveConnectionプロパティに設定し、
'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
'⇒Recordsetオブジェクトを開いたり操作するのに必要な設定
oRS.ActiveConnection = oCon
'データの件数を取得するSQL文を用意する
sqlStr = "select count(*) from tbl_info_list"
'データを抽出するSQL文をSourceプロパティに設定する
oRS.Source = sqlStr
'指定したテーブルのデータを参照する(アクセスする)
oRS.Open
'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
getVal = oRS.GetRows
'レコードセットを閉じる
oRS.Close
'取得したデータの件数を変数に格納する
dataNum = getVal(0, 0)
Do
sqlStr = "select * from ("
sqlStr = sqlStr & "select row_number() over(order by name asc) num"
sqlStr = sqlStr & ",a.name"
sqlStr = sqlStr & ",a.description from"
sqlStr = sqlStr & "("
sqlStr = sqlStr & "select * from tbl_info_list"
sqlStr = sqlStr & ") a"
sqlStr = sqlStr & ") b"
'初回と初回以降で、データを取得する範囲を指定する
If DNmCnt = 0 Then
'初回の場合
sqlStr = sqlStr & " where b.num between 1 and " & rowMaxCnt
Else
'初回以降の場合
sqlStr = sqlStr & " where b.num between " & (rowMaxCnt * DNmCnt) + 1 & _
" and " & rowMaxCnt + (rowMaxCnt * DNmCnt)
End If
'データを抽出するSQL文をSourceプロパティに設定する
oRS.Source = sqlStr
'指定したテーブルのデータを参照する(アクセスする)
oRS.Open
'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
getVal = oRS.GetRows
'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 oRS.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 = oRS.Fields.Item(cntF).Name
Next
'SQL Serverから取得したデータをセルに貼り付ける
Sheets("data" & DNmCnt + 1).Range("A2").Resize(c, r).Value = wk_getVal
'レコードセットを閉じる
oRS.Close
'カウンタを1増やす
DNmCnt = DNmCnt + 1
DoEvents
Loop While (dataNum > (rowMaxCnt * DNmCnt) + 1)
'各終了処理
oCon.Close
If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCon Is Nothing Then Set oCon = Nothing
コードの解説
注目すべきコード①
最初に見て頂きたいのは121行目です。
'SQL Serverから取得したデータをセルに貼り付ける
Sheets("data" & DNmCnt + 1).Range("A2").Resize(c, r).Value = wk_getVal
この121行目でSQL Serverから取得したデータをセルに貼り付けています。
SQL Serverから取得したデータが格納された配列wk_getValを、ExcelのシートのセルA2に貼り付けています。
注目すべきコード②
次に見て頂きたいのは65行目から82行目です。
sqlStr = "select * from ("
sqlStr = sqlStr & "select row_number() over(order by name asc) num"
sqlStr = sqlStr & ",a.name"
sqlStr = sqlStr & ",a.description from"
sqlStr = sqlStr & "("
sqlStr = sqlStr & "select * from tbl_info_list"
sqlStr = sqlStr & ") a"
sqlStr = sqlStr & ") b"
'初回と初回以降で、データを取得する範囲を指定する
If DNmCnt = 0 Then
'初回の場合
sqlStr = sqlStr & " where b.num between 1 and " & rowMaxCnt
Else
'初回以降の場合
sqlStr = sqlStr & " where b.num between " & (rowMaxCnt * DNmCnt) + 1 & _
" and " & rowMaxCnt + (rowMaxCnt * DNmCnt)
End If
SQL Serverからテーブルデータを取得するSQL文を用意します。
先ほどご紹介したコードのSQL文です。(65行目から82行目)
先ほどのコードではSQL文が分かりにくいと思いますので、実際のSQL文をご紹介します。
select
*
from
(
select
row_number() over(order by name asc) num,
a.name,
a.description
from(
select
*
from
tbl_info_list
) a
) b
where
b.num between 1 and 1048575
1件目から1,048,575件を取得するSelect文は上記となります。
※Excelシートの1行目は列名で使うので1,048,575件を取得しています。
select
*
from
(
select
row_number() over(order by name asc) num,
a.name,
a.description
from(
select
*
from
tbl_info_list
) a
) b
where
b.num between 1048576 and 2097150
1,048,576件目からさらに1,048,575件を取得するSelect文は上記となります。
注目すべきコード③
次に見て頂きたいのは88行目から91行目です。
'指定したテーブルのデータを参照する(アクセスする)
oRS.Open
'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
getVal = oRS.GetRows
88行目でSQL Serverからデータを取得します。
取得するデータは、先ほどご紹介した「注目すべきコード②」のSelect文で取得したデータです。
91行目でrecordsetのGetRowsメソッドで取得し、取得したデータを配列getValに格納しています。
注目すべきコード④
次に見て頂きたいのは104行目から109行目です。
'ディレクトリ＆ファイルの一覧編集処理後に配列の縦横を別の配列に入れ替える処理
'※配列の縦横を入れ替えるのに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
SQL Serverのテーブルから取得したデータを格納した配列は、データの縦横が逆になっているので、そのままExcelのシートに貼り付けることができません。
なので、104行目から109行目の通り配列の縦横を入れ替える必要があります。
配列の縦横を入れ替えずにそのままExcelのシートに貼り付けた場合
列が3つのSQL Serverのテーブルデータの場合、そのテーブルデータをマクロ側が受け取って配列に格納すると、データの縦横が逆になってしまいます。
縦横が逆になっているので3行だけデータが貼り付けられていますが、4行目以降のデータが「#N/A」と表示されており、正しくデータが貼り付きません。
配列の縦横を入れ替えてからExcelのシートに貼り付けた場合
SQL Serverのテーブルデータが正しく3列で貼り付けられています。
ただし、Excel2013以降のExcelをお使いならこの104行目から109行目のコードの通りに書かなくても、transeposeメソッドで構いません。
Excel2013だとtranseposeメソッドが正しく機能しないため、transeposeメソッドは使えません。
Excel2013だとtranseposeメソッドが正しく機能するのは、配列の要素数が65536までです。
Excel2013はをお使いなら104行目から109行目のコードの通りに書かないと配列の縦横を入れ替えることができません。
配列の要素数が65537を超えると正しく機能しません。
配列の要素数が65536以内の場合
正しく配列の値がExcelのシートに貼り付けられています。
配列の要素数が65536を超える場合
配列の値ではない想定外の値が貼り付けられています。
transeposeメソッドのバグというわけではないのですが、Excel2013でのtranseposeメソッドの仕様であるとの理解が正しいでしょう。
Excel2013はをお使いなら104行目から109行目のコードの通りに書いておくのが無難です。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、先ほどのコードの13行目の「ADODB.Connection」と14行目の「ADODB.Recordset」というオブジェクトが、「msado28.tlb」というファイルを参照するからです。
Dim oCon As ADODB.Connection 'Connection用変数
Dim oRS As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」と「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、SQL ServerのテーブルのデータをExcelのシートに貼り付ける方法についてご説明しました。
SQL ServerのテーブルのデータをExcelのシートに貼り付ける処理の流れは次の通りです。
- ①SQL ServerからExcelのシートに貼り付けたいデータの件数を取得する
- ②SQL Serverのテーブルから、Excelのシートの最大行数分のデータを取得する(取得したデータは配列に格納する)
- ③ ②のデータを格納した配列の縦横を入れ替える
- ④シートを作成
- ⑤ ③のデータを④で作成したシートに貼り付ける
- ⑥ SQL Serverのテーブルデータの件数がExcelのシートの最大行数を超える場合は②から⑤を繰り返す(貼り付けるデータが無くなったら処理を終了)
かなりザックリしていますが、上記が処理の流れです。
SQL ServerのテーブルのデータをExcelのシートに貼り付けたい時は本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
