ねらい：DB の「正本データ」を、安全に・何度でも・同じ形で Excel に同期する
前の「Excel → DB 同期」は、“編集した結果を DB に反映する話”でした。
今回はその逆、DB にある正本データを Excel に
- いつでも同じ SQL で引き出す
- 同じレイアウトに貼り付ける
- 必要なら条件（期間・ID）を変えて何パターンも出力する
ための「DB → Excel 同期テンプレ」を作っていきます。
ここをテンプレ化しておくと、
- マスタ参照用一覧（顧客マスタ・商品マスタ・ユーザ一覧など）
- 期間指定の明細ダウンロード（売上・アクセスログなど）
- 部署ごと・担当者ごとの抽出結果
を、ボタン1つで「DB → Excel」に持ってこられるようになります。
共通基盤：ADO 接続・SQL 実行・シート貼り付けの土台
DB 接続（SQL Server の例。Access / MySQL も同じ型で書ける）
まずは「DB につなぐ」共通部品を 1 か所にまとめておきます。
' ModDb_Base.bas
Option Explicit
Public Function GetDbConnection() As Object
Dim conn As Object
Set conn = CreateObject("ADODB.Connection")
Dim connStr As String
connStr = ""
connStr = connStr & "Provider=SQLOLEDB;"
connStr = connStr & "Data Source=SERVER_NAME;" ' サーバ名
connStr = connStr & "Initial Catalog=DB_NAME;" ' DB名
connStr = connStr & "Integrated Security=SSPI;" ' または User ID=...;Password=...;
conn.Open connStr
Set GetDbConnection = conn
End Function
ここをあなたの環境に合わせて 1 回だけ設定してしまえば、あとはどのマクロからでも GetDbConnection を呼べば DB につながる、という形になります。
Access の場合は Provider を Microsoft.ACE.OLEDB.12.0 にしたり、MySQL なら ODBC ドライバを使ったり、接続文字列だけ差し替えれば同じ構造で動かせます。
SELECT 結果を配列に取り込む共通関数
DB → Excel 同期の“心臓”は「SELECT → 配列 → シート」なので、ここも部品化しておきます。
Public Function ExecuteSelectToArray(ByVal sql As String) As Variant
Dim conn As Object
Set conn = GetDbConnection()
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, conn, 1, 1 ' 1=adOpenKeyset, 1=adLockReadOnly
If rs.EOF And rs.BOF Then
ExecuteSelectToArray = Array() ' 空配列
Else
ExecuteSelectToArray = rs.GetRows() ' 行×列ではなく [列,行] になる点に注意
End If
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Function
注意ポイントは、GetRows が「[列, 行] 配列」を返すことです。
これをそのまま Range.Value に突っ込むと、行と列が逆転します。
そこで「[列, 行] → [行, 列]」に入れ替える小さい関数を用意します。
GetRows を「行×列」に変換してシートに貼れる形にする
Public Function RecordsetArrayToRowCol(ByVal rsData As Variant) As Variant
If IsEmpty(rsData) Then
RecordsetArrayToRowCol = Array()
Exit Function
End If
Dim colCount As Long, rowCount As Long
colCount = UBound(rsData, 1) - LBound(rsData, 1) + 1
rowCount = UBound(rsData, 2) - LBound(rsData, 2) + 1
Dim out() As Variant
ReDim out(1 To rowCount, 1 To colCount)
Dim r As Long, c As Long
For c = 0 To colCount - 1
For r = 0 To rowCount - 1
out(r + 1, c + 1) = rsData(c, r)
Next
Next
RecordsetArrayToRowCol = out
End Function
これで、
ExecuteSelectToArrayで DB からデータを配列取得RecordsetArrayToRowColで「行×列配列」に変換Range("A2").Resize(…) = 配列で一発貼り付け
という流れを毎回同じように使い回せます。
マスタ同期：Customers テーブル → Customers シートへ一括ダウンロード
顧客マスタを「全件」Excel に同期するテンプレ
まずは一番シンプルなパターン、顧客マスタ全件をシートに同期する例です。
' ModDb_ToExcel_Customers.bas
Option Explicit
Public Sub Sync_Customers_FromDb()
Dim sql As String
sql = ""
sql = sql & "SELECT "
sql = sql & " CustomerID,"
sql = sql & " CustomerName,"
sql = sql & " Email,"
sql = sql & " Phone "
sql = sql & "FROM Customers "
sql = sql & "ORDER BY CustomerID"
Dim rsData As Variant
rsData = ExecuteSelectToArray(sql)
Dim a As Variant
a = RecordsetArrayToRowCol(rsData)
Dim ws As Worksheet
Set ws = Worksheets("Customers")
ws.Cells.Clear
ws.Range("A1").Value = "CustomerID"
ws.Range("B1").Value = "CustomerName"
ws.Range("C1").Value = "Email"
ws.Range("D1").Value = "Phone"
If Not IsEmpty(a) Then
ws.Range("A2").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End If
With ws.Range("A1").CurrentRegion
.Columns.AutoFit
.Borders.LineStyle = xlContinuous
End With
MsgBox "DB から Customers を同期しました。", vbInformation
End Sub
ここでは
- SQL は「欲しい列だけ、DB側で順番も決めておく」
- シート側は「ヘッダを1行目に固定 → 2行目以下にデータ貼り付け」
という“型”にしています。
ヘッダ名を DB 側と揃えておくと、この後の「Excel → DB 同期」などとの整合も取りやすくなります。
条件付き同期：期間やIDで絞り込んで Excel に持ってくる
期間で絞った売上明細を同期する例
売上テーブルを想定します。
Sales テーブル（DB）
SaleDate, CustomerID, Amount, …
今日の例では、「指定した期間の売上」を Excel に同期するテンプレを書きます。
期間（From/To）は、シートのセルから読み取る形にします。
' ModDb_ToExcel_Sales.bas
Option Explicit
Public Sub Sync_Sales_ByPeriod()
Dim fromDate As Date
Dim toDate As Date
fromDate = Worksheets("Control").Range("B2").Value ' 期間From
toDate = Worksheets("Control").Range("B3").Value ' 期間To
Dim sql As String
sql = ""
sql = sql & "SELECT "
sql = sql & " SaleDate,"
sql = sql & " CustomerID,"
sql = sql & " Amount "
sql = sql & "FROM Sales "
sql = sql & "WHERE SaleDate >= '" & Format(fromDate, "yyyy-mm-dd") & "' "
sql = sql & "AND SaleDate <= '" & Format(toDate, "yyyy-mm-dd") & "' "
sql = sql & "ORDER BY SaleDate, CustomerID"
Dim rsData As Variant
rsData = ExecuteSelectToArray(sql)
Dim a As Variant
a = RecordsetArrayToRowCol(rsData)
Dim ws As Worksheet
Set ws = Worksheets("Sales_Extract")
ws.Cells.Clear
ws.Range("A1").Value = "SaleDate"
ws.Range("B1").Value = "CustomerID"
ws.Range("C1").Value = "Amount"
If Not IsEmpty(a) Then
ws.Range("A2").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End If
With ws.Range("A1").CurrentRegion
.Columns.AutoFit
.Borders.LineStyle = xlContinuous
.Columns(1).NumberFormatLocal = "yyyy-mm-dd"
.Columns(3).NumberFormatLocal = "#,##0"
End With
MsgBox "期間 " & Format(fromDate, "yyyy-mm-dd") & " ～ " & _
Format(toDate, "yyyy-mm-dd") & " の売上を同期しました。", vbInformation
End Sub
ここでの重要ポイントを少し深掘りします。
日付の条件は、DB 側の型が DATE/TIMESTAMP であることを前提に "yyyy-mm-dd" 文字列で指定しています。
Excel 上のセルの見た目が「2025/1/1」であっても、Format(…, "yyyy-mm-dd") で必ず DB 側に合う形に変換してから SQL に埋め込むのが安全です。
金額列には #,##0 の書式をかけておくことで、ダウンロード直後から“見えるレポート”として使えます。
本格運用なら本当はパラメータ化（Command.Parameters）を使いたいところですが、
まずは「日付文字列をきちんと整形して埋める」という癖をつけるだけでも事故はぐっと減ります。
パラメータで何パターンも同期できる「汎用テンプレ」にする
“SQL＋パラメータ”をシートで管理して、共通マクロで流す
もう一段再利用性を上げる方法として、
「どのテーブルを、どんな条件で、どのシートに落とすか」を一覧シートで管理し、
共通マクロで順番に実行していく“DB同期版 ReportList”のような設計もできます。
DbSyncList シートの構成例：
A列: Enable（Y の行だけ実行）
B列: SyncID（任意のID）
C列: OutputSheet（貼り付け先シート名）
D列: SqlTemplate（パラメータ付きの SQL テンプレ。{FromDate} など）
E列: FromDateCell（期間Fromを読むセルアドレス。例 “Control!B2″）
F列: ToDateCell（期間Toを読むセルアドレス）
G列: Status
H列: Message
この「SqlTemplate」にこんな感じの文字列を入れておきます。
SELECT SaleDate, CustomerID, Amount
FROM Sales
WHERE SaleDate >= '{FromDate}'
AND SaleDate <= '{ToDate}'
ORDER BY SaleDate, CustomerID
これを VBA 側で {FromDate} や {ToDate} を置き換えて実行するイメージです。
汎用DB→Excel同期マクロの例
' ModDb_ToExcel_Generic.bas
Option Explicit
Private Function ReplaceParam(ByVal template As String, ByVal key As String, ByVal value As String) As String
ReplaceParam = Replace(template, "{" & key & "}", value)
End Function
Public Sub Run_DbSyncList()
Dim ws As Worksheet
Set ws = Worksheets("DbSyncList")
Dim a As Variant
a = ws.Range("A1").CurrentRegion.Value
Dim r As Long
For r = 2 To UBound(a, 1)
Dim flag As String
flag = UCase$(Trim$(CStr(a(r, 1))))
If flag <> "Y" Then GoTo NextRow
Dim syncId As String
Dim outSheetName As String
Dim sqlTemplate As String
Dim fromAddr As String
Dim toAddr As String
syncId = CStr(a(r, 2))
outSheetName = CStr(a(r, 3))
sqlTemplate = CStr(a(r, 4))
fromAddr = CStr(a(r, 5))
toAddr = CStr(a(r, 6))
On Error GoTo ErrHandler
Dim fromDate As Date
Dim toDate As Date
If Len(fromAddr) > 0 Then fromDate = Range(fromAddr).Value
If Len(toAddr) > 0 Then toDate = Range(toAddr).Value
Dim sql As String
sql = sqlTemplate
If Len(fromAddr) > 0 Then sql = ReplaceParam(sql, "FromDate", Format(fromDate, "yyyy-mm-dd"))
If Len(toAddr) > 0 Then sql = ReplaceParam(sql, "ToDate", Format(toDate, "yyyy-mm-dd"))
Dim rsData As Variant
rsData = ExecuteSelectToArray(sql)
Dim b As Variant
b = RecordsetArrayToRowCol(rsData)
Dim wsOut As Worksheet
On Error Resume Next
Set wsOut = Worksheets(outSheetName)
On Error GoTo 0
If wsOut Is Nothing Then
Set wsOut = Worksheets.Add
wsOut.Name = outSheetName
End If
wsOut.Cells.Clear
If Not IsEmpty(b) Then
wsOut.Range("A2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End If
' ヘッダは SQL の列名を取得する方法もあるが、簡易には手書き／別セルから貼るなどでOK
' ここではデータだけ同期する最小構成に留める
With wsOut.Range("A1").CurrentRegion
.Columns.AutoFit
.Borders.LineStyle = xlContinuous
End With
ws.Cells(r, 7).Value = "OK"
ws.Cells(r, 8).Value = "完了"
GoTo NextRow
ErrHandler:
ws.Cells(r, 7).Value = "NG"
ws.Cells(r, 8).Value = "エラー: " & Err.Number & " - " & Err.Description
Err.Clear
NextRow:
Next r
MsgBox "DbSyncList の一括同期が完了しました。", vbInformation
End Sub
ここまで来ると、
- DbSyncList に「欲しいテーブルと条件」を増やす
- SQLテンプレを書き足す
- Enable=Y にする
だけで、「DB→Excelの同期ジョブ」を増やしていけるようになります。
例題：顧客マスタ＆期間売上を一括同期してレポートの材料にする
まとめとして、次のような運用イメージを描けるようにします。
Control シート
B2: 期間From
B3: 期間To
DbSyncList シート
1行目: ヘッダ
2行目: 顧客マスタ同期（Enable=Y, SyncID=”CUSTOMERS”, OutputSheet=”Customers”, SqlTemplate=”SELECT … FROM Customers”, FromDateCell空, ToDateCell空）
3行目: 売上同期（Enable=Y, SyncID=”SALES”, OutputSheet=”Sales_Extract”, SqlTemplate=”SELECT … WHERE SaleDate between ‘{FromDate}’ and ‘{ToDate}'”, FromDateCell=”Control!B2″, ToDateCell=”Control!B3″）
あとは、
- 日次／月次処理の冒頭で Control!B2/B3 に対象期間をセット
Run_DbSyncListを呼んで「顧客マスタ＆売上明細」を DB から最新状態で Excel に同期- そのデータをもとに一括レポート自動作成・売上レポートツール・分析テンプレなどを回す
という「DB→Excel→レポート」の一連の流れを組めます。
落とし穴と対策（重要部分の深掘り）
本番DBを直接触る前に“必ずテスト環境で”確認する
DB → Excel なので「書き込みはしないから安全」と思いがちですが、
- 重い SQL を打って本番 DB に負荷をかける
- 不適切な条件で想定外に巨大な件数を取ってきて Excel がフリーズする
などのリスクは残ります。
接続先・テーブル構成が同じテスト環境があれば、必ずそちらで動作確認してから本番へ接続先を切り替える、という癖をつけてください。
SELECT * は便利だが“列変更で即死”しやすい
SELECT * FROM Customers は簡単ですが、DB 側で列追加・削除が行われた瞬間に、Excel 側の「列順前提」のマクロが崩れます。
できる限り
- 欲しい列だけを列名で書く
- 列順もSQLで決めておく
というスタイルにしておくと、将来の変更に強いです。
日付と数値のフォーマットを“取込直後に整える”
DB → Excel 同期のあと、
- 日付列は
yyyy-mm-dd - 数値列は
#,##0
など、最低限の書式をすぐにかけておくと、そのままレポートやピボットに回しやすくなります。
取込と整形（書式）をひとつのマクロにまとめておくのがポイントです。
大量データの取り過ぎに注意（条件・上限の設計）
業務によっては 1 テーブルが数百万件あることも珍しくありません。
Excel には行数制限もありますし、それ以前にメモリで落ちます。
必ず
- 期間条件（From/To）
- ID やフラグによる絞り込み
- TOP N などの上限
を設計に含め、「1 回で持ってくる件数」を制御する癖をつけてください。
まとめ：DB を“正本”、Excel を“ビュー＆加工の場”と割り切り、同期ロジックを型にする
DB → Excel 同期の本質は、
DB にある正本データを、毎回同じ条件で、毎回同じ形で、Excel に連れてくる“道”を作ることです。
そのために
- ADO接続・SELECT→配列・配列→シート貼り付けを共通関数化
- Terms（期間・ID）を Control シートから読み取り、SQLを安定して組み立てる
- DbSyncList のような一覧で「どのSQLをどのシートに流すか」を管理
という“型”を作っておくと、あとは「一覧を足すだけ」で同期ジョブを増やしていけます。