【VBAリファレンス】VBAサンプル集ADOでテキストデータを集計する

概要

本稿では、Excel VBAにおいてActiveX Data Objects (ADO) を活用し、外部テキストファイルからデータを読み込み、集計処理を行うための実践的なサンプルコードと解説を提供します。ADOは、データベースへのアクセスだけでなく、テキストファイルなどの非データベースソースに対しても強力なデータ操作機能を提供します。この技術を習得することで、Excelの標準機能だけでは困難な、大量のテキストデータからの効率的な集計や分析が可能になります。

特に、CSVファイルやタブ区切りファイルなどの構造化されたテキストデータは、ビジネスシーンにおいて頻繁に登場します。これらのデータをExcelにインポートしてからVBAで処理することも可能ですが、ADOを用いることで、より直接的かつ効率的にデータにアクセスし、集計ロジックを記述できます。これにより、VBAコードの記述量を削減し、処理速度を向上させることが期待できます。

本記事では、以下の内容に焦点を当てます。

* ADOの基本的な概念とテキストファイルへの適用方法
* テキストファイルを開き、レコードセットとして扱う方法
* レコードセット内のデータを操作し、集計を行うVBAコード例
* エラーハンドリングとリソース解放の重要性
* 実務で役立つ応用的なテクニックと注意点

ADOを使いこなすことで、Excel VBAによるデータ処理の可能性は大きく広がります。ぜひ、本稿で紹介するサンプルコードを参考に、ご自身の業務に活用してみてください。

詳細解説

ADOとは

ActiveX Data Objects (ADO) は、Microsoftが提供するデータアクセス技術の集合体です。COM (Component Object Model) インターフェースを通じて、様々なデータソース（データベース、テキストファイル、Excelファイルなど）にアクセスし、データの取得、操作、更新を行うことができます。ADOは、以前のDAO (Data Access Objects) と比較して、より柔軟で強力な機能を提供し、特にODBC (Open Database Connectivity) やOLE DB (Object Linking and Embedding Database) を介したデータアクセスに優れています。

テキストファイルへのADO適用

ADOは、OLE DB Provider for Microsoft Text (Microsoft.Jet.OLEDB.4.0 または Microsoft.ACE.OLEDB.12.0) を使用することで、テキストファイルをあたかもデータベーステーブルのように扱うことができます。このプロバイダーは、区切り記号（カンマ、タブなど）や固定長レコードのフォーマットを解釈し、テキストファイルを構造化されたデータとして認識します。

テキストファイルをADOで扱うためには、以下の要素が必要です。

1. **Connectionオブジェクト**: データソースへの接続を確立します。テキストファイルの場合は、接続文字列でファイルパスやフォーマット情報を指定します。
2. **Recordsetオブジェクト**: 接続されたデータソースから取得したデータを格納し、操作するためのオブジェクトです。レコードセットは、テーブル、クエリ結果、または一時的なデータセットを表します。
3. **Commandオブジェクト (オプション)**: SQL文を実行してデータを取得する場合に使用します。テキストファイルに対してもSQLクエリを実行できます。

テキストファイル接続文字列の構成

テキストファイルをADOで開く際の接続文字列は、そのフォーマットによって記述方法が異なります。

* **CSVファイルの場合**:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<ディレクトリパス>;Extended Properties=”text;HDR=YES;FMT=Delimited(,)”;

* `Provider`: テキストファイル用のOLE DBプロバイダーを指定します。
* `Data Source`: テキストファイルが存在するディレクトリのパスを指定します。
* `Extended Properties`: テキストファイルのフォーマットを指定します。
* `text`: テキストファイルであることを示します。
* `HDR=YES`: ファイルの1行目がヘッダー行（フィールド名）であることを示します。`NO`の場合はヘッダーがないとみなされ、`F1`, `F2`のようなデフォルト名が付けられます。
* `FMT=Delimited(,)`: 区切り文字がカンマであることを示します。タブ区切りの場合は `FMT=TabDelimited`、セミコロン区切りの場合は `FMT=Delimited(;)` のように指定します。

* **タブ区切りファイルの場合**:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<ディレクトリパス>;Extended Properties=”text;HDR=YES;FMT=TabDelimited”;

* **固定長ファイルの場合**:
固定長ファイルの場合は、`Schema.ini` ファイルを作成し、その中で各フィールドの開始位置と長さを定義する必要があります。
`Schema.ini` ファイルの例:

[your_text_file.txt]
Format=Fixed
Col1=1,10,CustomerID Char
Col2=11,20,ProductName Char
Col3=31,5,Quantity Long

接続文字列:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<ディレクトリパス>;Extended Properties=”text;HDR=NO;FMT=Fixed”;

この場合、`Schema.ini` ファイルは、対象のテキストファイルと同じディレクトリに配置する必要があります。

Recordsetオブジェクトによるデータ操作

Recordsetオブジェクトを利用することで、テキストファイルから読み込んだデータを、データベースのテーブルのように扱うことができます。

* **レコードセットのオープン**:
`Recordset.Open Source, ActiveConnection, CursorType, LockType, Options`
* `Source`: SQLクエリ、またはRecordsetオブジェクトを指す文字列（テーブル名など）。テキストファイルの場合は、ファイル名を指定します。
* `ActiveConnection`: Connectionオブジェクト。
* `CursorType`: カーソルの種類を指定します（例: `adOpenStatic`, `adOpenDynamic`, `adOpenKeyset`）。`adOpenStatic` は、データソースのスナップショットを取得し、ローカルで操作します。
* `LockType`: レコードのロック方法を指定します（例: `adLockReadOnly`, `adLockOptimistic`）。
* `Options`: Recordset.Open メソッドの追加オプションを指定します。テキストファイルの場合は、`adCmdText` を使用してSQLクエリを実行したり、`adCmdFile` を使用してファイル名を指定したりします。

* **レコードセットからのデータ取得**:
Recordsetオブジェクトは、`Fields` コレクションを通じて各フィールドの値にアクセスできます。
`Recordset.Fields(“フィールド名”).Value` または `Recordset.Fields(インデックス).Value`

* **レコードセットの移動**:
`Recordset.MoveFirst`, `Recordset.MoveNext`, `Recordset.MoveLast`, `Recordset.MovePrevious` などのメソッドでレコード間を移動できます。

* **集計処理**:
Recordsetオブジェクトをループ処理し、各レコードのフィールド値を集計変数に加算していくことで、様々な集計（合計、平均、件数など）が可能です。

SQLクエリによる集計

ADOの強力な点の一つは、テキストファイルに対してもSQLクエリを実行できることです。これにより、Excel VBAでループ処理を記述するよりも簡潔かつ高速に集計処理を行うことができます。

例えば、特定の条件を満たすレコードの合計値を求める場合、以下のようなSQLクエリを使用できます。

SELECT SUM(Quantity)
FROM your_text_file.txt
WHERE Category = ‘Electronics’;

このSQLクエリをRecordsetオブジェクトで実行し、結果を取得することで、目的の集計値を簡単に得ることができます。

サンプルコード

ここでは、CSV形式のテキストファイルからデータを読み込み、特定の列の合計値を計算するVBAサンプルコードを紹介します。

**前提:**
* `C:\Data\SalesData.csv` という名前のCSVファイルが存在する。
* CSVファイルの内容は以下の通り（1行目はヘッダー）。

ProductID,ProductName,Category,Quantity,Price
101,Laptop,Electronics,5,1200
102,Keyboard,Electronics,10,75
201,Desk Chair,Furniture,3,150
103,Mouse,Electronics,15,25
301,Notebook,Stationery,50,3
202,Table,Furniture,2,300

* `Category` 列が “Electronics” であるレコードの `Quantity` 列の合計値を求めたい。

**VBAコード:**

Option Explicit

Sub AggregateTextFileWithADO()

Dim cn As Object ‘ ADODB.Connection
Dim rs As Object ‘ ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim strFilePath As String
Dim strDirectory As String
Dim strFileName As String
Dim totalQuantity As Long
Dim categoryFilter As String
Dim schemaFilePath As String ‘ Schema.ini のパス

‘ — 設定 —
strFilePath = “C:\Data\SalesData.csv” ‘ 対象のテキストファイルパス
categoryFilter = “Electronics” ‘ 集計対象のカテゴリ
‘ —————-

‘ ファイルパスからディレクトリとファイル名を分割
strDirectory = Left(strFilePath, InStrRev(strFilePath, “\”))
strFileName = Mid(strFilePath, InStrRev(strFilePath, “\”) + 1)

‘ 接続文字列の作成 (CSVファイル、ヘッダーあり、カンマ区切り)
‘ Microsoft.ACE.OLEDB.12.0 は Office 2007 以降で推奨
‘ Microsoft.Jet.OLEDB.4.0 は Office 2003 以前のバージョンで使用
‘ 環境に合わせて適切なプロバイダーを選択してください。
‘ ‘HDR=YES’ はヘッダー行がある場合、’FMT=Delimited(,)’ はカンマ区切りを指定
strConn = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & strDirectory & “;Extended Properties=””text;HDR=YES;FMT=Delimited(,)””;”

‘ Schema.ini ファイルが不要な場合は、この部分をコメントアウトまたは削除してください。
‘ 固定長ファイルや、より詳細なフォーマット定義が必要な場合にSchema.iniを使用します。
‘ Schema.ini ファイルを作成・配置し、そのパスを指定します。
‘ schemaFilePath = strDirectory & “Schema.ini”
‘ If Dir(schemaFilePath) <> “” Then
‘ ‘ Schema.ini が存在する場合の処理（必要に応じて）
‘ End If

‘ SQLクエリの作成
‘ “*” は全てのフィールドを意味しますが、ここでは集計対象のQuantityのみをSELECTしています。
‘ WHERE句でカテゴリをフィルタリングします。
strSQL = “SELECT SUM(Quantity) AS TotalQuantity FROM [” & strFileName & “] WHERE Category = ‘” & categoryFilter & “‘”

‘ エラーハンドリングの設定
On Error GoTo ErrorHandler

‘ ADOオブジェクトの作成
Set cn = CreateObject(“ADODB.Connection”)
Set rs = CreateObject(“ADODB.Recordset”)

‘ データベースへの接続
cn.Open strConn

‘ SQLクエリの実行とレコードセットのオープン
‘ adCmdText は SQL クエリを実行することを示します。
rs.Open strSQL, cn, 3, 1 ‘ CursorType=adOpenStatic, LockType=adLockReadOnly

‘ 集計結果の取得
If Not rs.EOF Then
totalQuantity = rs.Fields(“TotalQuantity”).Value
MsgBox “カテゴリ ‘” & categoryFilter & “‘ の数量合計は: ” & totalQuantity, vbInformation
Else
MsgBox “指定されたカテゴリのデータが見つかりませんでした。”, vbInformation
End If

‘ 後処理: リソースの解放
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Exit Sub

ErrorHandler:
‘ エラーメッセージの表示
MsgBox “エラーが発生しました。” & vbCrLf & _
“エラー番号: ” & Err.Number & vbCrLf & _
“エラー内容: ” & Err.Description, vbCritical

‘ エラー発生時のリソース解放
If Not rs Is Nothing Then
If rs.State = 1 Then rs.Close ‘ adStateOpen
Set rs = Nothing
End If
End If
If Not cn Is Nothing Then
If cn.State = 1 Then cn.Close ‘ adStateOpen
Set cn = Nothing
End If
End If

End Sub

Sub ReadAndLoopTextFileWithADO()

Dim cn As Object ‘ ADODB.Connection
Dim rs As Object ‘ ADODB.Recordset
Dim strConn As String
Dim strFilePath As String
Dim strDirectory As String
Dim strFileName As String
Dim currentQuantity As Long
Dim totalQuantity As Long
Dim categoryFilter As String
Dim rowCount As Long

‘ — 設定 —
strFilePath = “C:\Data\SalesData.csv” ‘ 対象のテキストファイルパス
categoryFilter = “Electronics” ‘ 集計対象のカテゴリ
‘ —————-

‘ 接続文字列の作成
strConn = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & strDirectory & “;Extended Properties=””text;HDR=YES;FMT=Delimited(,)””;”

‘ エラーハンドリングの設定
On Error GoTo ErrorHandler

‘ ADOオブジェクトの作成
Set cn = CreateObject(“ADODB.Connection”)
Set rs = CreateObject(“ADODB.Recordset”)

‘ データベースへの接続
cn.Open strConn

‘ ファイル全体をレコードセットとしてオープン
‘ adCmdFile はファイル名を直接指定して開くことを示します。
‘ CursorType=adOpenStatic, LockType=adLockReadOnly
rs.Open strFileName, cn, 3, 1

‘ レコードセットをループ処理して集計
rowCount = 0
totalQuantity = 0
Do While Not rs.EOF
rowCount = rowCount + 1
‘ 指定されたカテゴリに一致する場合のみ数量を集計
If rs.Fields(“Category”).Value = categoryFilter Then
currentQuantity = rs.Fields(“Quantity”).Value
‘ 値が数値であることを確認してから加算
If IsNumeric(currentQuantity) Then
totalQuantity = totalQuantity + CLng(currentQuantity)
End If
End If
rs.MoveNext
Loop

‘ 結果の表示
If rowCount > 0 Then
MsgBox “ファイル ‘” & strFileName & “‘ の ” & rowCount & ” レコードを処理しました。” & vbCrLf & _
“カテゴリ ‘” & categoryFilter & “‘ の数量合計は: ” & totalQuantity, vbInformation
Else
MsgBox “ファイルにデータが見つかりませんでした。”, vbInformation
End If

‘ 後処理: リソースの解放
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Exit Sub

End Sub

**コード解説:**

1. **`Option Explicit`**: 変数の宣言を強制し、タイプミスによるエラーを防ぎます。
2. **オブジェクト変数の宣言**: `ADODB.Connection` と `ADODB.Recordset` オブジェクト用の変数を `Object` 型で宣言しています。これは、COMオブジェクトへの参照を保持するためです。
3. **設定**: `strFilePath` に処理対象のテキストファイルのフルパス、`categoryFilter` に集計したいカテゴリ名を指定します。
4. **パスの分割**: `strDirectory` と `strFileName` に分割することで、接続文字列とレコードセットのオープン時に適切にパスを使用できるようにします。
5. **接続文字列 (`strConn`)**:
* `Provider=Microsoft.ACE.OLEDB.12.0;`: 最新のOfficeバージョンで推奨されるOLE DBプロバイダーを指定します。古いバージョンでは `Microsoft.Jet.OLEDB.4.0` を使用します。
* `Data Source=<ディレクトリパス>;`: テキストファイルが存在するディレクトリを指定します。
* `Extended Properties=”…”`: テキストファイルのフォーマット詳細を指定します。
* `text`: テキストファイルであることを示します。
* `HDR=YES`: 1行目がヘッダー行（フィールド名）であることを示します。
* `FMT=Delimited(,)`: 区切り文字がカンマであることを示します。タブ区切りの場合は `FMT=TabDelimited` を使用します。
6. **SQLクエリ (`strSQL`)**:
* `SELECT SUM(Quantity) AS TotalQuantity`: `Quantity` 列の合計を `TotalQuantity` という名前で取得します。
* `FROM [your_text_file.txt]`: 対象のファイル名を指定します。ファイル名にスペースが含まれる場合は角括弧 `[]` で囲む必要があります。
* `WHERE Category = ‘Electronics