【Excel】VBAでファイルを開かずにデータ取得する方法!ExcelのADO接続で外部ブックを読み込む

VBAを使って外部Excelブックからデータを取得したい場面があります。しかし、外部ブックを開いたままでは作業が重くなったり、意図しない変更を加えてしまうリスクがあります。ファイルを開かずに安全にデータを取得できれば、業務効率が格段に向上します。この記事では、VBAとADO接続を使い、外部Excelファイルを開かずにデータを取得する具体的な手順を解説します。

Excel VBAで外部ファイルからデータを読み込む際、通常はWorkbooks.Openメソッドを使用してファイルを開きます。しかし、大量のファイルを処理する場合や、他のユーザーがファイルを編集中の場合に、ファイルを開く操作は時間がかかったり、エラーの原因になったりすることがあります。また、ファイルを開くことで、意図せずブックの内容を変更してしまう可能性も否定できません。このような問題を回避し、安全かつ効率的に外部データを取得する方法として、ADO(ActiveX Data Objects)接続が有効です。ADOを利用することで、Excelファイルを開かずに、まるでデータベースのように外部ブック内のシートやテーブルからデータを直接取得できます。

ADO接続で外部Excelファイルを開かずにデータを取得できる仕組み

ADOは、データベースへのアクセスを抽象化するオブジェクトモデルです。Excelファイルも、ADOにとってはOLE DBプロバイダーを介してアクセスできるデータソースの一つとみなされます。具体的には、「Microsoft.ACE.OLEDB.12.0」や「Microsoft.Jet.OLEDB.4.0」といったOLE DBプロバイダーを利用することで、Excelファイルのシートや名前付き範囲をテーブルのように扱えます。これにより、VBAからSQL文を発行して、ファイルを開くことなくデータを参照・取得することが可能になります。この仕組みにより、実行速度の向上や、ファイルを開くことによるリスクの回避が実現されます。

VBAでADO接続を設定し、外部ブックからデータを取得する手順

1. VBAエディタでライブラリを参照設定する
   ADO機能を利用するには、まずVBAエディタで必要なライブラリを参照設定する必要があります。
   1. VBAエディタ(Alt + F11)を開きます。
   2. 「ツール」メニューから「参照設定」を選択します。
   3. 一覧から「Microsoft ActiveX Data Objects 6.1 Library」(または6.0、2.8など、利用可能なバージョン)を探してチェックを入れ、「OK」をクリックします。
2. ADO接続オブジェクトを宣言する
   VBAコード内でADO接続とレコードセットを操作するための変数を宣言します。
   “`vba
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim strConn As String
   Dim strSQL As String
   Dim strFilePath As String
   Dim wsName As String
   “`
3. 接続文字列を作成する
   読み込みたいExcelファイルのパスを指定した接続文字列を作成します。Excelのバージョンによって使用するプロバイダーが異なります。
   ・Excel 2007以降(.xlsx, .xlsmなど)の場合:
   “`vba
   strFilePath = “C:\Data\Source.xlsx” ‘ 読み込みたいファイルのフルパスを指定
   wsName = “Sheet1” ‘ 読み込みたいシート名を指定
   strConn = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & strFilePath & “;Extended Properties=’Excel 12.0 Xml;HDR=YES’;”
   “`
   ・Excel 97-2003(.xls)の場合:
   “`vba
   ‘ strFilePath = “C:\Data\Source.xls” ‘ 読み込みたいファイルのフルパスを指定
   ‘ wsName = “Sheet1” ‘ 読み込みたいシート名を指定
   ‘ strConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & strFilePath & “;Extended Properties=’Excel 8.0’;”
   “`
   補足: HDR=YESは、シートの1行目をヘッダー(列名)として扱うことを意味します。ヘッダーがない場合はHDR=NOを指定します。
4. SQLクエリを作成する
   取得したいデータを指定するSQLクエリ文を作成します。シート名をテーブル名のように扱います。
   “`vba
   strSQL = “SELECT * FROM [” & wsName & “$]”
   ‘ 特定の列だけ取得したい場合:
   ‘ strSQL = “SELECT Column1, Column2 FROM [” & wsName & “$] WHERE Column3 = ‘特定の値'”
   “`
   注意: シート名にスペースが含まれる場合は、`[シート名$]`のように角括弧とドル記号で囲む必要があります。
5. ADO接続を開き、レコードセットを取得する
   作成した接続文字列とSQLクエリを使用して、ADO接続を開き、データをレコードセットとして取得します。
   “`vba
   Set cnn = New ADODB.Connection
   cnn.Open strConn

   Set rst = New ADODB.Recordset
   rst.Open strSQL, cnn, adOpenStatic, adLockReadOnly
   “`

6. 取得したデータをExcelシートに貼り付ける
   レコードセットには取得したデータが格納されているため、これをExcelシートにコピーします。
7. オブジェクトを解放する
   処理が終わったら、使用したADOオブジェクトを解放し、接続を閉じます。
   “`vba
   If rst.State = adStateOpen Then rst.Close
   Set rst = Nothing

   If cnn.State = adStateOpen Then cnn.Close
   Set cnn = Nothing
   “`

完全なVBAコード例

上記の手順をまとめたVBAコード例です。このコードは、指定した外部Excelファイルの特定のシートからデータを取得し、アクティブなシートのA1セルから貼り付けます。

【コード例】

“`vba
Sub GetDataFromExternalExcel()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim strFilePath As String
Dim wsName As String
Dim targetSheet As Worksheet
Dim targetCell As Range

‘ — 設定項目 —
strFilePath = “C:\Data\Source.xlsx” ‘ ★読み込みたいExcelファイルのフルパスを指定
wsName = “Sheet1” ‘ ★読み込みたいシート名を指定
Set targetSheet = ThisWorkbook.ActiveSheet ‘ ★データを貼り付けるシートを指定
Set targetCell = targetSheet.Range(“A1”) ‘ ★データを貼り付ける開始セルを指定
‘ —————-

‘ Excelのバージョンに応じてプロバイダーを選択
‘ .xlsx, .xlsmなどの場合は “Excel 12.0 Xml”
‘ .xlsの場合は “Excel 8.0”
strConn = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & strFilePath & “;Extended Properties=’Excel 12.0 Xml;HDR=YES’;”
‘ strConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & strFilePath & “;Extended Properties=’Excel 8.0’;” ‘ (.xlsファイル用)

‘ SQLクエリを作成 (シート名をテーブル名のように扱う)
strSQL = “SELECT * FROM [” & wsName & “$]”

On Error GoTo ErrorHandler

‘ 接続オブジェクトとレコードセットオブジェクトを作成
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

‘ 接続を開く
cnn.Open strConn

‘ レコードセットを開く (データを取得)
‘ adOpenStatic: 全てのレコードをメモリに読み込む
‘ adLockReadOnly: 読み取り専用でロックする
rst.Open strSQL, cnn, adOpenStatic, adLockReadOnly

‘ 取得したデータをExcelシートに貼り付ける
If Not rst.EOF Then ‘ データが存在する場合
‘ データを直接セル範囲に書き込む
targetCell.Resize(rst.RecordCount, rst.Fields.Count).Value = rst.GetRows
MsgBox “データの取得が完了しました。”, vbInformation
Else
MsgBox “指定されたシートにデータが見つかりませんでした。”, vbExclamation
End If

ExitHandler:
‘ オブジェクトを解放する
If rst.State = adStateOpen Then rst.Close
Set rst = Nothing
If cnn.State = adStateOpen Then cnn.Close
Set cnn = Nothing
Exit Sub

ErrorHandler:
MsgBox “エラーが発生しました。” & vbCrLf & _
“エラー番号: ” & Err.Number & vbCrLf & _
“エラー内容: ” & Err.Description,
vbCritical
Resume ExitHandler

End Sub
“`

ADO接続で外部ブックからデータを取得する際の注意点とよくある失敗

プロバイダーが存在しない、またはバージョンが合わない

「’Microsoft.ACE.OLEDB.12.0′ プロバイダーは登録されていません。」といったエラーが表示される場合、OLE DBプロバイダーがインストールされていないか、Excelのバージョンと合わないプロバイダーを指定している可能性があります。64bit版Officeと32bit版Officeでプロバイダーの互換性がない場合もあります。その場合は、Officeのbit数に合わせたプロバイダー(Microsoft Access Database Engine Redistributable)を別途インストールする必要があります。

パスやファイル名が間違っている

指定したファイルのパスやファイル名が間違っていると、「外部テーブル ‘…’ へのクエリは、正しく形式化されていません。」のようなエラーが発生します。ファイルのフルパスが正しいか、ファイル名に間違いがないか、大文字・小文字を含めて正確に確認してください。また、ファイルがパスワードで保護されている場合も、この方法では直接開けません。

シート名やテーブル名が正しくない

SQLクエリの `FROM` 句で指定するシート名が間違っている場合もエラーになります。シート名にスペースや特殊文字が含まれる場合は、必ず `[シート名$]` の形式で指定してください。名前付き範囲(テーブル)を読み込みたい場合は、`[名前付き範囲名]` のように指定します。

HDR=YES/NOの設定ミス

接続文字列の `Extended Properties` 内にある `HDR` オプションの設定が間違っていると、データが正しく取得できません。1行目をヘッダー(列名)として扱いたい場合は `HDR=YES`、1行目も含めてデータとして取得したい場合は `HDR=NO` を指定します。これが原因で、ヘッダー行がデータとして取り込まれたり、データが1行ずれたりすることがあります。

32bit版Excelと64bit版Excelの互換性問題

32bit版Officeで作成したVBAコードを64bit版Officeで実行したり、その逆を行ったりする場合、ADOのプロバイダーの互換性問題が発生することがあります。特に、`Microsoft.ACE.OLEDB.12.0` プロバイダーは、Officeのbit数と一致させる必要があります。64bit版Office環境で32bit版Office用のプロバイダー(`Microsoft.Jet.OLEDB.4.0`)を使おうとするとエラーになることがあります。確実なのは、Officeのbit数に合ったプロバイダーを使用することです。

ADO接続と他の方法(Workbooks.Open)の比較

項目	ADO接続	Workbooks.Open
ファイルを開くか	開かない	開く
処理速度	一般的に高速	ファイルサイズや内容による
VBAコードの複雑さ	やや複雑(参照設定、接続文字列、SQL)	比較的容易(Workbooks.Open, Range.Valueなど)
他のユーザーによる編集中の影響	影響を受けにくい	ロックされる、エラーが発生する可能性
パスワード保護されたファイル	直接は対応不可	パスワードを指定して開ける
利用シーン	大量データ取得、バックグラウンド処理、安全なデータ参照	ファイルを開いて確認・編集したい場合、パスワード保護されたファイル

ADO接続は、ファイルを開かずにデータを取得できるため、大量のデータを効率的に処理したい場合や、他のユーザーの作業を妨げたくない場合に非常に有効です。一方、Workbooks.Openメソッドは、ファイルを開いて内容を確認したり、パスワードで保護されているファイルを扱ったりする場合には適しています。それぞれの方法のメリット・デメリットを理解し、状況に応じて使い分けることが重要です。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】