Excelの全シートから特定の文字を検索し、そのシート名とセルの位置を表示するVBA関数

業務でExcelファイルの複数のシートを調べて、特定の文字列がどこに含まれているかを調べる必要があることがあります。
VBAを使うことで、Excelを開かずにその内容を全シート・全セルから検索し、検索結果としてシート名とセル位置を表示できます。

この記事では、Excelブック内のすべてのシートを調べ、指定した文字列がどのセルに含まれているかを特定するVBAコードを紹介します。

◆ 実現方法：VBAで全シートを検索

このVBA関数では、ADOを使ってExcelを開かずにシートデータを取得し、全セルをループして特定の文字列を検索します。見つかった場合、そのシート名と**セル位置（行番号と列番号）**を出力します。

◆ 特徴

  • Excelを開かずに全シートを検索できる
  • 特定の文字列を全セルで検索し、そのシート名とセル位置を取得
  • 見つからなかった場合は「文字列は見つかりませんでした」と表示

◆ 関数コード（コメント付き）

以下のVBAコードでは、指定された文字列をすべてのシートのすべてのセルに対して検索し、その結果を表示します。

' 特定の文字列をすべてのシートデータから検索し、そのシート名・セルの位置を出力する関数
Function SearchTextInSheets(filePath As String, searchText As String) As String
    Dim result As Object
    Dim sheet As Variant
    Dim data As Variant
    Dim r As Long, c As Long
    Dim foundText As Boolean
    Dim output As String

    output = ""
    foundText = False
    
    ' シートデータを読み取る
    Set result = ReadAllSheetsFromExcelADO(filePath)
    
    ' データが取得できた場合
    If Not result Is Nothing Then
        ' すべてのシートをループ
        For Each sheet In result.Keys
            data = result(sheet)
            
            ' シート内のすべてのセルをループ
            For r = 1 To UBound(data, 1)
                For c = 1 To UBound(data, 2)
                    ' セルの内容が検索文字列と一致する場合
                    If InStr(1, CStr(data(r, c)), searchText, vbTextCompare) > 0 Then
                        ' 見つかった場合、シート名とセル位置を出力
                        output = output & "シート名: " & sheet & "、セル位置: (" & r & ", " & c & ")" & vbCrLf
                        foundText = True
                    End If
                Next c
            Next r
        Next sheet
    End If
    
    ' 見つかった結果を返す
    If foundText Then
        SearchTextInSheets = output
    Else
        SearchTextInSheets = "指定した文字列は見つかりませんでした。"
    End If
End Function

◆ 使用方法

この関数を実行するには、以下の手順を試してください。

  1. SearchTextInSheets関数に、検索したいExcelファイルのパスと、検索する文字列を渡します。
  2. 結果として、シート名とセルの位置（行番号・列番号）が表示されます。
Sub TestSearchText()
    Dim result As String
    result = SearchTextInSheets("C:\path\to\your.xlsx", "検索したい文字列")

    ' 結果を表示
    MsgBox result
End Sub

このサンプルコードを実行すると、指定した文字列がどのシートのどのセルに含まれているかが表示されます。

◆ 説明

1. InStr 関数:

  • InStrを使って、各セル内に指定した文字列が含まれているかを検索します。
  • 部分一致の検索を行っていますので、セル内の一部に文字列が含まれていればヒットします。

2. CStr 関数:

  • セル内のデータが数値や日付の場合でも文字列として比較するために、CStrを使用しています。

3. 検索結果:

  • 見つかった場合、シート名とセル位置（行番号、列番号）を**output**という文字列変数に保存し、最後にその結果を返します。
  • もし検索結果が見つからなければ、「指定した文字列は見つかりませんでした。」というメッセージを返します。

◆ まとめ

このVBA関数を使用することで、Excelファイルを開かずにすべてのシートを検索し、特定の文字列がどのシートのどのセルにあるかを確認できます。
大量のデータがある場合でも、手軽に検索して必要な情報を抽出できるので、業務の効率化にもつながります。

◆ 追加情報

  • 部分一致で検索しているため、文字列が完全に一致しなくても検索できます。
  • 必要に応じて完全一致検索に変更することもできます（InStr を = に変更）。
  • 大文字小文字を区別しない検索を行っているので、細かい検索条件に合わせて変更可能です。

コメント

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