業務で別のExcelブックの中身を確認したいとき、そのファイルを開かずにデータを取得できると便利ですよね。
この記事では、VBAを使って「Excelを開かずに全てのシートと全セルの内容を取得する方法」を紹介します。
さらに、パスワード保護されているファイルには注意メッセージを表示して中断する処理も追加しています。
開ける保護ブックはそのまま読み取りOKです。
◆ 実現方法：ADOを使ったExcel読み取り
このVBA関数では「ADO（ActiveX Data Objects）」というデータアクセス技術を使います。
これにより、Excelをデータベースのように扱って中身をSELECT文で取得することができます。
◆ 特徴
- Excelを開かずに全シート・全セルを読み取れる
- 開かれていても問題なし（読み取り専用で接続）
- パスワード保護されたブックはメッセージを表示して終了
- 各シートごとに2次元配列で取得し、Dictionaryで返却
◆ 関数コード（コメント付き）
' ADOを使ってExcelファイルのすべてのシート・すべてのセルを読み取る関数
Function ReadAllSheetsFromExcelADO(filePath As String) As Object
Dim conn As Object ' ADO接続オブジェクト
Dim rs As Object ' レコードセット（各シートデータ格納）
Dim sheets As Object ' 結果を格納するDictionary（シート名→2次元配列）
Dim sheetList As Collection ' シート名一覧
Dim tableName As String
Dim schemaRS As Object ' シート一覧を取得するスキーマ
Dim rowData As Variant ' 1行分のデータ
Dim rowList As Collection ' 各行のデータを保持
Dim rowCount As Long, colCount As Long
Dim r As Long, c As Long
Set sheets = CreateObject("Scripting.Dictionary")
Set sheetList = New Collection
On Error GoTo ErrHandler
' ADO接続（読み取り専用）
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & filePath & ";" & _
"Mode=Read;" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"
' シート一覧の取得
Set schemaRS = conn.OpenSchema(20) ' adSchemaTables = 20
Do While Not schemaRS.EOF
tableName = schemaRS.Fields("TABLE_NAME").Value
If Right(tableName, 1) = "$" Or Right(tableName, 2) = "$'" Then
sheetList.Add tableName
End If
schemaRS.MoveNext
Loop
schemaRS.Close
' 各シートを1つずつ処理
For Each tableName In sheetList
Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM [" & tableName & "]", conn, 1, 1
' 行データをCollectionで取得
Set rowList = New Collection
Do Until rs.EOF
ReDim rowData(0 To rs.Fields.Count - 1)
For c = 0 To rs.Fields.Count - 1
rowData(c) = rs.Fields(c).Value
Next
rowList.Add rowData
rs.MoveNext
Loop
' Collectionから2次元配列に変換
rowCount = rowList.Count
If rowCount > 0 Then
colCount = UBound(rowList(1))
ReDim allData(1 To rowCount, 1 To colCount + 1)
For r = 1 To rowCount
For c = 0 To colCount
allData(r, c + 1) = rowList(r)(c)
Next
Next
' Dictionaryに格納（キー：シート名、値：2次元配列）
sheets(CleanSheetName(tableName)) = allData
End If
rs.Close
Next
conn.Close
Set ReadAllSheetsFromExcelADO = sheets
Exit Function
' パスワード保護やその他のエラー処理
ErrHandler:
If Err.Number = -2147217865 Or Err.Description Like "*password*" Then
MsgBox "このブックはパスワード保護されているため読み取れません。", vbExclamation
Else
MsgBox "エラー発生: " & Err.Description, vbCritical
End If
Set ReadAllSheetsFromExcelADO = Nothing
If Not rs Is Nothing Then If rs.State = 1 Then rs.Close
If Not conn Is Nothing Then If conn.State = 1 Then conn.Close
End Function
' シート名から '$' や "'" を除去
Private Function CleanSheetName(raw As String) As String
raw = Replace(raw, "'", "")
CleanSheetName = Replace(raw, "$", "")
End Function
◆ 使用例：読み取ったシートとデータを確認
Sub TestRead()
Dim result As Object
Dim sheet As Variant
Dim data As Variant
Dim r As Long, c As Long
Set result = ReadAllSheetsFromExcelADO("C:\Users\YourName\Documents\sample.xlsx")
If Not result Is Nothing Then
For Each sheet In result.Keys
Debug.Print "【" & sheet & "】"
data = result(sheet)
For r = 1 To UBound(data, 1)
For c = 1 To UBound(data, 2)
Debug.Print data(r, c) & vbTab;
Next
Debug.Print
Next
Next
End If
End Sub
◆ まとめ
このVBA関数を使えば、Excelブックを開くことなく中身を取得できるため、業務の自動化やバッチ処理に役立ちます。
- 読み取り専用で安全に読み取れる
- パスワード保護の検出も可能
- データ構造は柔軟な
Dictionaryで扱いやすい
必要に応じて「特定のシートだけ」「セルに特定の文字がある行だけ」などの絞り込みにも対応可能です。
コメント