【入門編】【上級者向け】添付ファイルとして送られてきたExcelファイルを、開かずにADOで直接読み込んで集計する – Outlook VBA解析バイブル

スポンサーリンク
スポンサーリンク

脱・マクロの記録。Excelを開かずにデータを支配する「ADO」という極意

こんにちは。現場の設計を司るチーフアーキテクトです。

「マクロの記録」を使って、なんとか業務を自動化できた。そんな喜びを感じているあなた、素晴らしい一歩を踏み出しましたね。でも、もしあなたが「大量の添付ファイルが届くたびに、Excelが何度も立ち上がって画面がチカチカする」「処理が重すぎて、PCが固まってしまう」という壁にぶつかっているなら……。

おめでとうございます。あなたは今、「自動化の真の価値」に気づき始めた、エンジニアとしての分岐点に立っています。

今日は、Outlook VBAを極めるための「禁断の、しかし最も美しい手法」を伝授しましょう。テーマは「Excelアプリケーションを起動せず、添付ファイルをデータベースとして直接読み込む」。

これを知れば、あなたの作るプログラムは「ただ動くもの」から「プロフェッショナルな道具」へと進化します。

1. なぜ「Excelを開く」のがいけないのか?

まず、多くの初学者が陥る「重いコード」の正体を知っておきましょう。一般的な自動化は、以下のプロセスを辿ります。

1. Outlookでメールを受信する
2. 添付ファイルを保存する
3. Excelアプリケーションを起動する(ここで巨大なメモリを消費)
4. ファイルを開く(ここでさらに時間がかかる)
5. データを読み取る
6. Excelを閉じる

このステップ3と4が、実は非常に「重い」のです。Excelという巨大なソフトウェアは、単なるデータ保持ツールではなく、膨大な描画エンジン、計算エンジン、UI(ユーザーインターフェース)を持った「巨大な工場」のようなものです。

「たった1行の数値を取り出すためだけに、巨大な工場をフル稼働させる」

これは、ネジ一本を取り出すために、巨大な自動車工場を建設して、全ラインを動かすようなものです。非効率ですよね?

そこで登場するのが、ADO (ActiveX Data Objects) です。

2. ADO:Excelを「データベース」として扱う魔法

ADOとは、一言で言えば「データに直接アクセスするための窓口」です。

ADOを使えば、Excelという「アプリケーション」を介さず、Excelファイルという「データ構造」に対して、SQL(データベース操作言語)という言葉で直接指示を出すことができます。

  • Excel Object Model: 「Excelさん、ファイルを開いて、セルA1の値を見てください」
  • ADO: 「このファイルの中にある『Sheet1』というテーブルから、値を持ってきてください」

後者の方が、圧倒的に速く、そしてスマートです。メモリ消費も最小限。これが、プロの設計思想です。

3. 実践:添付Excelを高速集計する極限コード

それでは、実際にOutlook VBAからADOを使って、添付ファイルを直接読み込むコードを書いてみましょう。

事前準備(ここが最重要!)

このコードを動かすには、VBAエディタ(VBE)で以下の設定が必要です。
1. VBEのメニューから [ツール] > [参照設定] を開く。
2. 「Microsoft ActiveX Data Objects 6.1 Library」(バージョンは環境により異なりますが、最新のもの)にチェックを入れてOKを押してください。

これを行わないと、「ADOなんて知らないよ」とエラーが出てしまいます。

Option Explicit

''' <summary>
''' Outlookで受信したメールの添付ファイルを、Excelを開かずにADOで読み取る
''' </summary>
Sub ProcessAttachmentViaADO()
    Dim olApp As Outlook.Application
    Dim olNamespace As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder
    Dim olItem As Object
    Dim olMail As Outlook.MailItem
    Dim olAttach As Outlook.Attachment
    
    Dim savePath As String
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim connString As String
    Dim sql As String
    
    ' --- 設定エリア ---
    ' 保存先のパス(一時フォルダを指定)
    savePath = Environ("TEMP") & "\temp_data.xlsx"
    ' ------------------

    Set olNamespace = Application.GetNamespace("MAPI")
    ' 受信トレイを対象にする
    Set olFolder = olNamespace.GetDefaultFolder(olFolderInbox)

    ' 受信トレイのアイテムをループ(今回は最新の1件を例にします)
    ' 本番では条件に合わせてフィルタリングしてください
    For Each olItem In olFolder.Items
        If TypeOf olItem Is MailItem Then
            Set olMail = olItem
            
            ' 添付ファイルがあるか確認
            If olMail.Attachments.Count > 0 Then
                For Each olAttach In olMail.Attachments
                    ' Excelファイル(.xlsx)のみを対象とする
                    If LCase(Right(olAttach.FileName, 5)) = ".xlsx" Then
                        
                        ' 1. ファイルを一度ローカルに保存する(ADOはファイルパスが必要なため)
                        olAttach.SaveAsFile savePath
                        Debug.Print "保存完了: " & savePath

                        ' 2. ADO接続の準備
                        ' Excelファイルをデータベースとして扱うための魔法の文字列(接続文字列)
                        ' HDR=YES は「1行目はヘッダー(項目名)である」という意味
                        connString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                     "Data Source=" & savePath & ";" & _
                                     "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

                        Set conn = New ADODB.Connection
                        Set rs = New ADODB.Recordset

                        On Error GoTo ErrorHandler
                        conn.Open connString

                        ' 3. SQLを実行してデータを抽出
                        ' [Sheet1$] のように、シート名の後ろに $ をつけるのがルールです
                        sql = "SELECT * FROM [Sheet1$] WHERE [売上金額] > 10000"
                        
                        rs.Open sql, conn

                        ' 4. 抽出したデータの処理
                        Do While Not rs.EOF
                            ' ここで集計やログ出力を行う
                            Debug.Print "顧客名: " & rs.Fields("顧客名").Value & _
                                        " / 金額: " & rs.Fields("売上金額").Value
                            rs.MoveNext
                        Loop

                        ' 5. 後始末(クローズは必ず行うこと!)
                        rs.Close
                        conn.Close
                        Debug.Print "処理完了"
                        
                        ' 一時ファイルの削除
                        Kill savePath
                    End If
                Next olAttach
            End If
        End If
    Next olItem

    MsgBox "すべての処理が完了しました。", vbInformation
    Exit Sub

ErrorHandler:
    MsgGame = "エラーが発生しました: " & Err.Description
    MsgBox ErrGame, vbCritical
    If Not rs Is Nothing Then If rs.State = adStateOpen Then rs.Close
    If Not conn Is Nothing Then If conn.State = adStateOpen Then conn.Close
End Sub

4. チーフアーキテクトの教え:陥りやすい「落とし穴」

このコードは強力ですが、扱いを間違えると牙を剥きます。現場で私が何度も目にしてきたミスを共有しておきましょう。

① シート名の指定ルール

SQL文の中でシートを指定するとき、必ず `[Sheet1$]` のように、シート名の末尾に `$` をつけ、全体を `[]` で囲ってください。これがないと、ADOは「そんなテーブル(シート)は存在しない」とへそを曲げます。

② 接続文字列(Connection String)のバージョン

コード内の `Provider=Microsoft.ACE.OLEDB.12.0` は、現代のExcel(.xlsx)を扱うための標準的なドライバです。もし非常に古い `.xls` ファイルを扱う場合は、ここを `Microsoft.Jet.OLEDB.4.0` に変える必要がありますが、基本的には `ACE` を使えば間違いありません。

③ オブジェクトのライフサイクル(解放)

ここが最も重要です。`conn.Close` や `rs.Close` を忘れないでください。
ADOは、接続を開いたままにすると、そのファイルを「使用中」としてロックし続けてしまいます。その結果、後続の処理でファイルを削除しようとしたり、上書きしようとしたりしたときに「ファイルが開けません」というエラーで泣くことになります。

「使い終わったら、必ず片付ける」。これはプログラマの鉄則です。

④ データの型

ADOは、Excelの列の最初の数行を見て、その列が「数値」か「文字列」かを判断します。もし、数値の列に突然「不明」といった文字列が混じっていると、読み込み時にエラーになったり、データが無視されたりすることがあります。データのクリーニングは、自動化における最大の課題の一つです。

最後に

いかがでしたか?
「Excelを開く」という当たり前の常識を疑い、「データとして直接扱う」という視点を持つ。これだけで、あなたのプログラムのパフォーマンスと信頼性は劇的に向上します。

最初はエラーが出るかもしれません。接続文字列が複雑に見えるかもしれません。でも、一つずつ紐解いていけば、必ず制御できるようになります。

ここをクリアすれば、あなたのOutlook VBAは、もはや単なるマクロではなく、「ビジネスを加速させるエンジン」へと昇華するはずです。

さあ、次はどんな自動化に挑戦しますか? 応援していますよ。

タイトルとURLをコピーしました