Excel VBAでデータベースからデータを抽出する場合は、接続だけでなく、認証、抽出条件、出力先、型、0件時の処理、エラー時の接続解放まで決めることが重要です。本記事ではWindows版デスクトップExcel＋ADO＋SQL Serverに対象を絞り、パラメーター付きSELECTの結果を新しいブックへ出力する例を説明します。Excel for the webやMac版Excelで、そのまま動くコードではありません。
取得したデータはExcel側へコピーされます。データベースの閲覧権限があっても、ファイルへの持ち出し・保存・共有が許可されているとは限らないため、業務ルールを確認してください。以下は架空のEmployeesテーブルを想定した学習用コードで、実際の接続先・証明書・権限・Office環境での検証が必要です。
VBA・ADO・SQLの役割を整理する
VBAはExcelの自動化を行う言語、ADOはデータ接続や結果セットを扱う仕組み、SQLはデータベースへ抽出条件を伝える言語です。ADOのConnectionで接続し、Commandでパラメーター付きSQLを実行し、Recordsetから結果を読み取ります。ADO.NETとは別のAPIです。
SQL Server、MySQL、Oracleなどは接続ドライバー・SQL方言・認証方式が異なります。NoSQLを含むあらゆるデータベースへ同じ接続文字列で接続できるわけではありません。単純な定期取り込みならPower Queryなどの標準接続機能が適することもあり、細かな処理制御が必要な場合にVBAを選びます。
SELECT EmployeeID, Name, Age, HireDate, Sales
FROM dbo.Employees
WHERE Age >= 30
ORDER BY Name, EmployeeID;SELECTは必要な列、FROMはテーブル、WHEREは抽出条件、ORDER BYは順序を指定します。学習用のSELECT *は全列を返しますが、実装では列追加や機密列の混入を防ぐため必要な列を明示します。同名の人がいても順序が安定するよう、上の例では一意なEmployeeIDを最後の並び順にしています。
接続前に準備するもの
マクロを保存するブックは .xlsm にし、Alt＋F11でVBEを開いて標準モジュールを追加します。本記事は早期バインディングの例なので、「ツール」→「参照設定」で利用可能なMicrosoft ActiveX Data Objects x.x Libraryを選びます。「参照不可」の項目がある場合は先に確認してください。マクロを実行するために「すべてのマクロを有効」にするのではなく、組織の署名・信頼ポリシーに従います。
SQL Server用にはMicrosoft OLE DB Driver for SQL Serverを使います。次の例はDriver 19のプロバイダー名 MSOLEDBSQL19 を指定します。Driver 18の MSOLEDBSQL や旧 SQLOLEDB と名前・暗号化設定を混同しないでください。Excelプロセスが32bitか64bitかと、利用できるプロバイダーの登録状況を管理者に確認します。
ADOの参照設定を追加することと、SQL Serverの接続ドライバーをインストールすることは別です。サーバー名、データベース名、接続方法、Windows認証の利用可否、読み取り権限、証明書の信頼をDB管理者と確認します。利用者には必要なテーブルやビューへのSELECTだけを許可するなど、DB側で最小権限を設計します。
接続文字列：パスワードをVBAへ書き込まない
例はWindows認証を使用し、ブックへユーザー名やパスワードを埋め込みません。サーバーのホスト名は証明書と一致し、信頼できる証明書チェーンを構成する必要があります。接続エラーを消すために暗号化や証明書検証を無効にするのではなく、接続先・証明書・ドライバーの条件を確認します。
ADO向けの暗号化キーワードは Use Encryption for Data、証明書検証の省略を制御するものは Trust Server Certificate です。他のAPI向けの接続文字列を無検証で流用しないでください。SQL認証やMicrosoft Entra認証が必要な環境は、組織の認証方式に合わせて別途設計します。接続文字列をログへ丸ごと記録することも避けます。
完成例が想定するテーブルと動作
SQL Serverの dbo.Employees に、EmployeeID（varchar(20)、一意・非NULL）、Name（nvarchar(100)）、Age（int）、HireDate（date）、Sales（decimal(12,2)）がある想定です。後ろの4列はNULLを許可します。既存の業務テーブルを作り直すコードは掲載していません。検証用テーブルまたは読み取り用ビューを管理者が用意してください。
コード内の DataTypeCompatibility=80 はADO向けの互換設定です。特に日付などの戻り値の型を想定し、今回のHireDateはSQLでISO文字列へ変換しています。
コードは指定年齢以上を抽出し、最大10,000件を新規ブックへ出力します。既存のSheet1やアクティブシートは消去しません。新規ブックは自動保存・自動共有しないので、内容と保存権限を確認して利用者が保存してください。氏名やIDは文字列として扱い、数式のような値を勝手に実行させないようにします。
Option Explicit
Public Sub ExportEmployeesToNewWorkbook()
' Windows Excel. Reference: Microsoft ActiveX Data Objects x.x Library.
' Install Microsoft OLE DB Driver 19 for SQL Server.
Const SERVER_NAME As String = "sqlserver.example.local"
Const DATABASE_NAME As String = "YourDatabase"
Const MAX_ROWS As Long = 10000
Dim minimumAge As Long
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim outputBook As Workbook
Dim ws As Worksheet
Dim buffer() As Variant
Dim outputValues() As Variant
Dim headers As Variant
Dim rawValue As Variant
Dim rowCount As Long
Dim r As Long
Dim c As Long
Dim lastRow As Long
Dim previousScreenUpdating As Boolean
Dim previousEnableEvents As Boolean
Dim applicationStateSaved As Boolean
Dim completed As Boolean
Dim errorNumber As Long
Dim stage As String
Dim resultMessage As String
On Error GoTo Failed
minimumAge = 30
stage = "checking settings"
If minimumAge < 0 Or minimumAge > 150 Then
Err.Raise vbObjectError + 1000, , "Invalid minimum age."
End If
If MAX_ROWS < 1 Or MAX_ROWS > 10000 Then
Err.Raise vbObjectError + 1001, , "Invalid row limit."
End If
stage = "connecting to SQL Server"
Set cn = New ADODB.Connection
cn.ConnectionTimeout = 15
cn.ConnectionString = _
"Provider=MSOLEDBSQL19;" & _
"Data Source=""" & SERVER_NAME & """;" & _
"Initial Catalog=""" & DATABASE_NAME & """;" & _
"Integrated Security=SSPI;" & _
"Use Encryption for Data=Mandatory;" & _
"Trust Server Certificate=false;" & _
"Persist Security Info=false;" & _
"DataTypeCompatibility=80;"
cn.Open
stage = "running the parameterized query"
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cmd.CommandTimeout = 30
' MAX_ROWS is a validated code constant, not external SQL input.
' Date output is ISO text, avoiding locale-dependent date conversion.
cmd.CommandText = _
"SELECT TOP (" & CStr(MAX_ROWS) & ") " & _
"[EmployeeID], [Name], [Age], " & _
"CONVERT(char(10), [HireDate], 23) AS [HireDate], [Sales] " & _
"FROM [dbo].[Employees] " & _
"WHERE [Age] >= ? ORDER BY [Name] ASC, [EmployeeID] ASC;"
' Append in the same left-to-right order as the ? markers.
cmd.Parameters.Append _
cmd.CreateParameter("minimumAge", adInteger, adParamInput, , minimumAge)
Set rs = cmd.Execute
stage = "reading the result"
ReDim buffer(1 To MAX_ROWS, 1 To 5)
Do While Not rs.EOF
If rowCount >= MAX_ROWS Then Exit Do
rowCount = rowCount + 1
For c = 1 To 5
rawValue = rs.Fields(c - 1).Value
If IsNull(rawValue) Then
buffer(rowCount, c) = Empty
Else
Select Case c
Case 1, 2, 4
' Excel text prefix prevents formula interpretation.
' This also preserves leading zeros and long IDs.
buffer(rowCount, c) = "'" & CStr(rawValue)
Case 3
buffer(rowCount, c) = CLng(rawValue)
Case 5
' The example schema uses decimal(12,2).
buffer(rowCount, c) = CDbl(rawValue)
End Select
End If
Next c
rs.MoveNext
Loop
stage = "closing the database connection"
rs.Close
Set rs = Nothing
Set cmd = Nothing
cn.Close
Set cn = Nothing
stage = "creating a new workbook"
previousScreenUpdating = Application.ScreenUpdating
previousEnableEvents = Application.EnableEvents
applicationStateSaved = True
Application.ScreenUpdating = False
Application.EnableEvents = False
Set outputBook = Application.Workbooks.Add(xlWBATWorksheet)
Set ws = outputBook.Worksheets(1)
ws.Name = "Employees"
headers = Array("EmployeeID", "Name", "Age", "HireDate", "Sales")
For c = 1 To 5
ws.Cells(1, c).Value2 = headers(c - 1)
Next c
stage = "writing and formatting the result"
lastRow = rowCount + 1
If rowCount > 0 Then
ReDim outputValues(1 To rowCount, 1 To 5)
For r = 1 To rowCount
For c = 1 To 5
outputValues(r, c) = buffer(r, c)
Next c
Next r
' Apply text formats BEFORE assigning database values.
ws.Range("A2:B" & lastRow).NumberFormat = "@"
ws.Range("D2:D" & lastRow).NumberFormat = "@"
ws.Range("C2:C" & lastRow).NumberFormat = "0"
ws.Range("E2:E" & lastRow).NumberFormat = "#,##0.00"
ws.Range("A2").Resize(rowCount, 5).Value2 = outputValues
End If
With ws.Range("A1:E1")
.Font.Bold = True
.Interior.Color = RGB(221, 235, 247)
End With
ws.Range("A1:E" & lastRow).AutoFilter
ws.Columns("A:E").AutoFit
If ws.Columns("B").ColumnWidth > 40 Then
ws.Columns("B").ColumnWidth = 40
End If
ws.Columns("G").ColumnWidth = 70
ws.Range("G1").Value2 = "Export status"
ws.Range("G1").Font.Bold = True
ws.Range("G3").Value2 = "Minimum age: " & CStr(minimumAge)
ws.Range("G4").Value2 = "HireDate is ISO text (yyyy-mm-dd)."
ws.Range("G5").Value2 = "NULL values are blank cells."
If rowCount = MAX_ROWS Then
resultMessage = CStr(rowCount) & " rows exported. LIMIT REACHED: " & _
"the result may be incomplete. Narrow the filter."
ws.Range("G2").Font.Color = RGB(192, 0, 0)
ws.Range("G2").Font.Bold = True
ElseIf rowCount = 0 Then
resultMessage = "No matching rows. Headers only."
Else
resultMessage = CStr(rowCount) & " rows exported."
End If
ws.Range("G2").Value2 = resultMessage
ws.Range("G2").WrapText = True
ws.Rows(2).AutoFit
completed = True
GoTo CleanUp
Failed:
errorNumber = Err.Number
' Do not display connection strings, credentials, or provider error text.
Resume CleanUp
CleanUp:
On Error Resume Next
If Not rs Is Nothing Then
If rs.State <> adStateClosed Then rs.Close
End If
Set rs = Nothing
Set cmd = Nothing
If Not cn Is Nothing Then
If cn.State <> adStateClosed Then cn.Close
End If
Set cn = Nothing
If Not completed Then
If Not outputBook Is Nothing Then outputBook.Close SaveChanges:=False
End If
If applicationStateSaved Then
Application.EnableEvents = previousEnableEvents
Application.ScreenUpdating = previousScreenUpdating
End If
On Error GoTo 0
If completed Then
If rowCount = MAX_ROWS Then
MsgBox resultMessage, vbExclamation, "Employee export"
Else
MsgBox resultMessage, vbInformation, "Employee export"
End If
Else
MsgBox "Export failed while " & stage & "." & vbCrLf & _
"Error number: " & CStr(errorNumber) & vbCrLf & _
"Check the driver, ADO reference, permissions, and certificate.", _
vbExclamation, "Employee export"
End If
End Sub
コードの読み方と変更箇所
サーバー名とデータベース名を、自分の環境で許可された接続先へ置き換えます。年齢条件はコード内のminimumAgeに設定します。テーブル名や列名は固定したまま、値だけをパラメーターへ渡す構成です。セルから条件を受け取るように変える場合は、空欄・エラー値・数値範囲を検証してから渡してください。
? は値のパラメーターマーカーです。複数ある場合はSQL内の出現順とParametersへの追加順を一致させます。CreateParameter は作成だけなので、Parameters.Append で追加します。文字列のパラメーターには適切な型とSize、decimalにはPrecisionとNumericScaleの設計が必要です。SQLへ文字列を直接連結する実装へ戻さないでください。
テーブル名・列名・ORDER BYの方向は通常、値のパラメーターでは置き換えられません。選択式にしたい場合は、プログラム側で定義した許可リストから選びます。また、読み取り専用カーソルや接続のModeは、DB権限の代わりにはなりません。更新を防ぐ責任は、実行SQLとサーバー側の権限の両方にあります。
0件・NULL・件数制限・データ型を扱う
0件は接続失敗とは違います。RecordsetのEOFを確認して処理し、0件でもヘッダーと結果件数を確認できるようにします。ADOのRecordCountはカーソルによっては-1となるため、無条件に最終行の計算へ使いません。行番号や件数には、最大32,767のIntegerではなくLongを使います。
この例ではNULLを空欄として表示します。NULLと空文字を区別する必要がある業務では、別の状態列や明示的な表示ルールを用意してください。数値の0とNULLは別です。先頭ゼロや長いIDは文字列として保持し、この例のHireDateはSQL側でyyyy-mm-ddに変換し、地域設定による解釈違いを避けるためISO形式の文字列として出力します。Excelの日付計算が必要なら、年・月・日を明示してDateSerial等で変換する別処理を設計してください。日付書式を設定するだけで文字列が日付シリアルへ変わるわけではありません。金額は数値として扱います。Excelの数値精度は15桁なので、厳密な大桁整数や高精度小数を数値セルへ移すときは別設計が必要です。
10,000件の制限に達した結果は、条件に一致する全件を網羅していると判断しないでください。期間や部門を絞る、DB側で集計する、ページングや専用のレポート基盤を使うなどを検討します。Excelの最大行数まで取れるから安全・高速という意味ではありません。タイムアウト時間を長くするだけで重いSQLを本番へ流すのも避けます。
CopyFromRecordsetを使う場合の注意
Range.CopyFromRecordsetは結果セットをまとめてシートへ転送する方法です。フィールド名のヘッダーは別途書き、開始セルを明示し、最大行数・列数を確認します。コピー後はRecordsetがEOFに到達する点にも注意します。
単純な表では便利ですが、IDの文字列保持、NULL表現、数式として解釈させたくない外部テキスト、精密な数値などの要件を先に確認してください。本記事の完成例は列ごとの扱いを分かりやすくするため、一度配列へ読み取り、列の書式を設定してからまとめて値を出力します。大量データ向けの速度測定を実施したコードではありません。
書式・並べ替え・フィルターを追加する
次は完成例の出力を整えるときの考え方です。ws を出力ワークシート、lastRow をヘッダーを含む最終行として、同じプロシージャ内へ組み込む断片です。単独のマクロとして貼り付けるものではありません。すべてのCells・Range・Columnsを対象シートで修飾します。
Dim tableRange As Range
Dim salesRange As Range
Dim rule As FormatCondition
Set tableRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, 5))
If lastRow >= 2 Then
tableRange.Sort Key1:=ws.Range("B2"), Order1:=xlAscending, Header:=xlYes
tableRange.AutoFilter Field:=3, Criteria1:=">=30"
Set salesRange = ws.Range(ws.Cells(2, 5), ws.Cells(lastRow, 5))
Set rule = salesRange.FormatConditions.Add( _
Type:=xlCellValue, Operator:=xlGreater, Formula1:="1000")
rule.Interior.Color = RGB(255, 199, 206)
End IfHeader:=xlYesを指定する範囲には1行目のヘッダーを含めます。A2から始まるデータだけの範囲へxlYesを指定すると、最初のデータ行を見出しとして扱ってしまいます。日付はD列、売上はE列という今回の列構成に合わせ、以前の例や別のテーブルの列位置をそのまま流用しません。
オートフィルターはExcel上で行を非表示にする機能で、DBから取得する量を減らすものではありません。必要なデータだけを取る条件はSQLのWHEREへ渡します。複数条件はFieldごとに指定できますが、利用者が既に設定したフィルターを意図せず消さない設計も必要です。
応用：抽出結果からグラフを作る
棒グラフはカテゴリ比較、折れ線は時系列、円グラフは少数カテゴリの非負の構成比に向きます。従業員名と売上の表をそのまま「月別売上」と呼ばないでください。月別グラフが必要ならDB側で月単位へ集計した結果を別の表として用意します。
次は、出力シートwsのB列の氏名とE列の売上を使うグラフ断片です。0件なら作成せず、多数の人をそのまま並べずに事前集計や表示対象の選択を検討します。同じコードを繰り返すとグラフが増えるので、既存グラフを更新する場合は対象名と所有範囲を管理してください。
Dim chartObj As ChartObject
If lastRow >= 2 Then
Set chartObj = ws.ChartObjects.Add(Left:=420, Top:=20, Width:=480, Height:=280)
With chartObj.Chart
.ChartType = xlColumnClustered
With .SeriesCollection.NewSeries
.Name = "Sales"
.XValues = ws.Range(ws.Cells(2, 2), ws.Cells(lastRow, 2))
.Values = ws.Range(ws.Cells(2, 5), ws.Cells(lastRow, 5))
End With
.HasTitle = True
.ChartTitle.Text = "Employee sales"
End With
End Ifエラー処理とログ：原因を隠さず、秘密を残さない
成功時だけでなく失敗時にもRecordsetとConnectionを閉じます。On Error Resume Next を処理全体へ広げると、接続やSQLの失敗を無視したまま不完全な結果を正常扱いする危険があります。後始末の限定された区間以外では、エラーを明確に扱います。
記録する場合は日時、処理段階、エラー番号、管理者向けの追跡IDを中心にし、パスワード、トークン、接続文字列、取得した個人情報をそのまま保存しません。Err.DescriptionやADO Errorsにもサーバー名・SQL・データが含まれる可能性があるため、利用者画面や公開問い合わせへ丸ごと転載しないでください。ログの保存先もアクセス制御と保持期限を決めます。
未保存ブックではThisWorkbook.Pathが空で、OneDrive・SharePointなどの保存場所も通常のローカルフォルダーと同じとは限りません。そのため「ブックと同じ場所のErrorLog.txt」へ無条件に書く実装は避けます。ログ書き込み自体の失敗も扱い、共有ブックに機密エラーを蓄積しない設計にします。
よくあるエラーと確認順
「ユーザー定義型は定義されていません」：ADODBの参照設定と参照不可の有無を確認します。「プロバイダーが見つかりません」：Driver 19の導入・登録とExcelのbit数を確認します。ADO参照だけを追加してもドライバー不足は直りません。
ログイン失敗：Windows認証で接続している実行ユーザー、DBへのアクセス権、接続先を確認します。証明書エラー：接続先名と証明書の名前・有効期間・信頼チェーンを管理者が確認します。検証無効化を常用しません。タイムアウト：接続の問題とSQL実行時間を分け、ネットワーク・サーバー状態・実行計画を調べます。
テーブル・列が見つからない：Initial Catalog、dboなどのスキーマ、列名、SELECT権限を確認します。0件：条件とNULLを確認します。転記で失敗：保護、結合セル、型、セル文字数、ブック構造などを調べ、不完全な出力を利用しないでください。
演習：正常系だけでなく境界条件を確認する
検証用データには、年齢29・30・31・NULL、先頭ゼロのID、日本語名、数式のような文字列、NULLの日付と売上、既知の0円を含めます。30歳以上という条件なら29とNULLが除外され、30と31が含まれることを確認します。異なる同名レコードでもEmployeeIDで区別できることも確かめます。
例えばID=001・年齢29、ID=002・年齢30、ID=003・年齢31、ID=004・年齢NULLの4件なら、年齢30以上で抽出されるのは002と003です。003の氏名を =1+1 とした検証では、Excel上で2へ計算されず、その文字列のまま表示されることを確認します。
次に0件、ちょうど上限件数、上限超過、接続失敗、権限不足、途中の書き込み失敗を試します。既存ブックが変更されないこと、接続が解放されること、失敗した出力を成功と表示しないことを確認します。グラフは今回の列構成に合わせ、集計値・ラベル・並び順がSQL結果と一致するかを確認してください。
まとめ
VBAによるデータ抽出では、現在のドライバーと認証方式を選び、必要な列だけをパラメーター付きSELECTで読み、新規の出力先へ型を意識して転記します。EOFと実際の転記件数で処理し、NULL・件数制限・エラー時の後始末まで含めて検証することで、単なる接続例から実務で確認しやすい処理へ進められます。
コメント