マイナー・マイナー

隠れた名作の発掘が生きがい。

【VBA】エクセルファイルの内容を検索する


スポンサードリンク

あるフォルダ配下にエクセルファイルが複数あって、それらのファイルの内容に特定の文字が含まれているかどうかを調べたい時がありました。そんなわけで、

1.あるフォルダ配下のファイルを再帰的に検索して、ファイル一覧を取得する。
2.取得したファイルを開いて特定の文字が含まれるかどうかをチェックする。

そんな流れのサンプルマクロを作成しました。


サンプルマクロ

Option Explicit

Sub SearchKeyword()
    ' 検索対象ディレクトリ
    Dim inputRootFolder As String
    inputRootFolder = "C:\work"
   
    ' 検索キーワード
    Dim keyword As String
    keyword = "hoge"
   
    ' ファイル一覧の取得
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
   
    Dim fileList As Collection
    Set fileList = New Collection
    Dim folderList As Collection
    Set folderList = New Collection
   
    ' ファイルとフォルダのリストを取得
    Call GetFileAndFolderNameList(fso.GetFolder(inputRootFolder), fileList, folderList)
   
    ' 検索対象ファイルの正規表現
    Dim reFile As RegExp
    Set reFile = New RegExp
    reFile.Pattern = "\.(xls|xlsx)$"
   
    ' 検索対象のbook
    Dim inputBook As Workbook
   
    ' キーワード検索実行
    Application.ScreenUpdating = False

    Dim i As Integer
    On Error Resume Next
    For i = 1 To fileList.Count
       
        ' 検索対象ファイルかどうかをチェック
        If reFile.Test(fileList(i)) Then
           
            ' ファイルオープン
            Set inputBook = Workbooks.Open(fileList(i), ReadOnly:=True)
           
            Dim j As Integer
            For j = 1 To inputBook.Worksheets.Count
                Dim result As Range
                ' コメントを検索。値を検索する場合はLookIn:=xlValues、数式を検索する場合はLookIn:=xlFormulasとする。
                ' また、部分一致で検索。完全一致で検索する場合はLookAt:=xlWhole
                Set result = inputBook.Worksheets(j).Cells.Find(What:=keyword, LookIn:=xlComments, LookAt:=xlPart)
                If Not result Is Nothing Then
                    Debug.Print (fileList(i))
                    inputBook.Close SaveChanges:=False
                    Exit For
                End If
            Next j
           
            ' ファイルクローズ
            inputBook.Close SaveChanges:=False
        End If
    Next i
   
    Application.ScreenUpdating = True
   
    Set inputBook = Nothing
    Set reFile = Nothing
    Set fileList = Nothing
    Set folderList = Nothing
    Set fso = Nothing
   
End Sub


inputRootFolderが検索したいフォルダ、keywordが検索したい文字です。まず、inputRootFolder配下のファイル一覧を再帰的に取得してfileListに保持します。この処理は【VBA】ファイルとフォルダのリストを再帰的に取得する - マイナー・マイナーに書いたものを流用しました。


次にファイルを開いて、そのファイルの内容にkeywordが含まれているかどうかを調べる処理です。ファイルのオープン、クローズ処理が重たいので、"Application.ScreenUpdating"で画面更新を停止しています。


また、パスワード付きのファイルを開こうとした時など、ファイルオープンに失敗して処理が止まるケースがありました。なので、ファイルを処理するfor文の前に"On Error Resume Next"を入れて処理が止まらないようにしています。


文字検索はFindメソッドを利用します。いろいろなオプションがありますが、いったんは下記の二つをおさえました。

  • LookIn:検索対象をどうするかの引数。コメント、値、数式を指定します。
  • LookAt:文字を部分一致で検索するか、完全一致で検索するかを指定します。


これで、基本的な文字検索ができるようになりました。