EXCEL VBA

Excel-VBA GREP、AND/ORの複数条件を指定して検索

EXCEL VBA

こんにちは! 健史です。

Grep検索をVBAで作成しました。

検索キーワードは、複数指定したOR検索、AND検索に対応しています。

だたし
・VBAでは文字コードを判別できないないこともあり
・現バージョンでは、検索対象ファイルの文字コードを指定
します。

すわなち、検索対象フォルダ内の文字コードは同一である前提です。

ファイルの文字コードは、メモ帳やサクラエディタなどで確認できます。

スポンサーリンク

処理概要

・設定シート

設定項目	設定内容	備考		許容文字コード	'1'を入力
検索フォルダ	C:\TEMP01\01.IN	必須		SJIS	
検索レベル	S	S、M  ※1		Shift_JIS	
文字コード	UTF-8	⇒より選択		UTF-7	
検索条件	AND	AND,OR		UTF-8	1
検索文字	拒否	必須		UTF-16	
		以降は省略可		Unicode	
		足らなければ		EUC-JP	
		行追加して		Latin1	
					↑ 1つ選択
※1検索レベル					
 S:検索フォルダのみ、M:下位サブフォルダまで					

ドラッグ&コピー後にExcelに貼り付けるとき、[形式を選択して貼り付ける]-[テキスト]で貼り付けます。(セル[A1]で[右クリック]、[形式を選択して貼り付ける]-[テキスト])を指定します。

・フォルダとファイル

・file10.txt

SQL*Loader: Release ~
・・・省略・・・
制御ファイル:    E:\学校.ctl
データファイルE:\学校.csv
・・・省略・・・
表学校:
  11行のロードに成功しました。
  12行はデータ・エラーのためロードされませんでした。
  13行はWHEN句のエラーのためロードされませんでした。
  14行はすべてのフィールドがNULLのためロードされませんでした。
・・・省略・・・
スキップされた論理レコードの合計:           5
読み込まれた論理レコードの合計:             6
拒否された論理レコードの合計:               7
廃棄された論理レコードの合計:        8
・・・省略・・・
制御ファイル:    E:\学生.ctl
データファイルE:\学生.csv
・・・省略・・・
表学生:
  18行のロードに成功しました。
  17行はデータ・エラーのためロードされませんでした。
  16行はWHEN句のエラーのためロードされませんでした。
  15行はすべてのフィールドがNULLのためロードされませんでした。
・・・省略・・・
スキップされた論理レコードの合計:           4
読み込まれた論理レコードの合計:             3
拒否された論理レコードの合計:               2
廃棄された論理レコードの合計:        1
・・・省略・・・

※[file20.txt]は省略 参考:Excel-VBA 実例 テキストの文字列を検索し値を抽出にあります

・中間シート

・処理結果

↓ D列以降を拡大(A列~C列は中間シートに同じ)

項目の見出しを付けていないこともあり、補足致します。
D列:検索文字
E列:対象ファイルの行番号
F列:検索文字の行内容

プログラム

処理は[ディレクトリリスト:シート2に取得 Sub SUB99_DIRLIST(ByVal Path As String)]を除き単純です。

’ディレクトリリスト:シート2に取得'部分は「そういうもの」と思っており、深く考えず単に「シート2の対象ファイルのディレクトリ情報を取得する処理」程度の理解で良いと考えています。

内容を解読されたい場合、変数定義は後追いでスキップし、初期処理から読んでいって頂ければと思います。

'----- 変数定義 -----
    Const cnsDIR = "\*.*"
    Const cnsFROM = 6  '検索文字開始位置を定義、行の追加削除時に変更する
    Dim ix1, ix1_max, ix2, ix2_max, ix3 As Long
    Dim ix12, ix12_max, tmpCnt As Long
    Dim pos_st, pos_end, pos_len As Long
    Dim cntLine, cntCond, cntAnd As Long
    Dim strPath, strFilename, strFindlvl, strCode, strAnd, strTxt As String
'----- レベル:0 -----
'メイン処理
Sub MAIN00()
'初期処理
    ix1_max = Sheets(1).Range("B1").End(xlDown).Row
    cntCond = ix1_max - cnsFROM + 1  '設定シートの検索文字の個数を求めておく
    Sheets(2).Cells.Clear
    Sheets(3).Cells.Clear
    strPath = Sheets(1).Cells(2, 2)
    strFindlvl = Sheets(1).Cells(3, 2)
'入力項目チェック
    Call SUB01_INPUT_CHECK
'フォルダからフィル名をシート2に取得
    ix2 = 0
    Call SUB99_DIRLIST(strPath)
    ix2_max = ix2
'文字列検索してシート3に取得、AND,ORの検索条件により処理が異なる
    ix3 = 0
    If Sheets(1).Cells(cnsFROM - 1, 2) = "AND" Then
      Call SUB01_AND
    Else
      Call SUB01_OR
    End If
    Sheets(3).Activate
    MsgBox "処理終了!"
End Sub
'----- レベル:1 -----
'設定シート項目チェック
Sub SUB01_INPUT_CHECK()
    If Dir(strPath, vbDirectory) = "" Then
        MsgBox "入力フォルダがありません!"
        End
    End If
'
    Select Case strFindlvl
    Case "S", "M"
    Case Else
         MsgBox "検索レベルが間違っています!"
         End
    End Select
'
    Select Case Sheets(1).Cells(cnsFROM - 1, 2)
    Case "AND", "OR"  'OKなので何もせず
    Case Else
        MsgBox "検索条件が指定されていません!" & vbCrLf & vbCrLf _
        & "AND、OR のいづれかを指定して下さい"
        End
    End Select
'
    If Sheets(1).Cells(cnsFROM, 2) = "" Then
        MsgBox "検索文字が1つも指定されていません!" & vbCrLf & vbCrLf _
        & "注意:空白のセルを跨いでの設定はできません"
        End
    End If
'
    ix12_max = Sheets(1).Cells(1, 5).End(xlDown).Row
    tmpCnt = 0
    For ix12 = 2 To ix12_max
        If Sheets(1).Cells(ix12, 6) = 1 Then
            tmpCnt = tmpCnt + 1
            strCode = Sheets(1).Cells(ix12, 5)
        End If
    Next
    If tmpCnt = 1 Then
    Else
        MsgBox "文字コードの選択が1つ選択されていないか、複数選択されている!"
        End
    End If
    Sheets(1).Cells(4, 2) = strCode
    ThisWorkbook.Save
End Sub
'AND検索
Sub SUB01_AND()
'検索する文字が複数ある場合に[,]で結合する。検索結果シートのD列に置くための処理
    strAnd = ""
    For ix1 = cnsFROM To ix1_max
      strAnd = strAnd & Sheets(1).Cells(ix1, 2)
      If ix1 <> ix1_max Then
        strAnd = strAnd & ","
      End If
    Next
'
    For ix2 = 1 To ix2_max
        With CreateObject("ADODB.Stream")
            .Charset = strCode
            .Open
            .LineSeparator = 10 '種類;-1:CrLf(既定値),10:Lf,13:Cr
            .LoadFromFile Sheets(2).Cells(ix2, 3)
            On Error GoTo AND920
            cntLine = 0
            Do Until .EOS
                strTxt = .ReadText(-2)
                On Error GoTo AND930
                Call SUB02_AND_SEARCH
            Loop
            .Close
        End With
    Next
    Exit Sub
AND920:
    MsgBox "ロード不能なファイルが含まれています!" & vbCrLf & _
        Sheets(2).Cells(ix2, 3)
    Sheets(3).Cells.Clear
    End
AND930:
    MsgBox "読み込み不能なファイルが含まれています!" & vbCrLf & _
        Sheets(2).Cells(ix2, 3)
    Sheets(3).Cells.Clear
    End
End Sub
'OR検索
Sub SUB01_OR()
    For ix2 = 1 To ix2_max
        With CreateObject("ADODB.Stream")
            .Charset = strCode
            .Open
            .LineSeparator = 10 '種類;-1:CrLf(既定値),10:Lf,13:Cr
            .LoadFromFile Sheets(2).Cells(ix2, 3)
            On Error GoTo OR920
            cntLine = 0
            Do Until .EOS
                strTxt = .ReadText(-2)
                On Error GoTo OR930
                Call SUB02_OR_SEARCH
            Loop
            .Close
        End With
    Next
    Exit Sub
OR920:
    MsgBox "ロード不能なファイルが含まれています!" & vbCrLf & _
        Sheets(2).Cells(ix2, 3)
    Sheets(3).Cells.Clear
    End
OR930:
    MsgBox "読み込み不能なファイルが含まれています!" & vbCrLf & _
        Sheets(2).Cells(ix2, 3)
    Sheets(3).Cells.Clear
    End
End Sub
Sub SUB02_AND_SEARCH()
    cntLine = cntLine + 1
    cntAnd = 0
    For ix1 = cnsFROM To ix1_max
        pos_st = InStr(1, strTxt, Sheets(1).Cells(ix1, 2))
        If pos_st > 0 Then
            cntAnd = cntAnd + 1
        End If
    Next
    If cntAnd = cntCond Then
        Call SUB02_OUTPUT_SET
        Sheets(3).Cells(ix3, 4) = strAnd
    End If
End Sub
Sub SUB02_OR_SEARCH()
    cntLine = cntLine + 1
    For ix1 = cnsFROM To ix1_max
        pos_st = InStr(1, strTxt, Sheets(1).Cells(ix1, 2))
        If pos_st > 0 Then
            Call SUB02_OUTPUT_SET
            Sheets(3).Cells(ix3, 4) = Sheets(1).Cells(ix1, 2)
        End If
    Next
End Sub
'----- レベル:2 -----
'AND検索、OR検索における共通項目セット
Sub SUB02_OUTPUT_SET()
    ix3 = ix3 + 1
    Sheets(3).Cells(ix3, 1) = Sheets(2).Cells(ix2, 1)
    Sheets(3).Cells(ix3, 2) = Sheets(2).Cells(ix2, 2)
    Sheets(3).Cells(ix3, 3) = Sheets(2).Cells(ix2, 3)
    Sheets(3).Cells(ix3, 5) = cntLine
    Sheets(3).Cells(ix3, 6) = strTxt
End Sub
'----- レベル:1 -----
'ディレクトリリスト:シート2に取得
Sub SUB99_DIRLIST(ByVal Path As String)
    Dim objFile As Object
    strFilename = Dir(Path & cnsDIR)
    Do While strFilename <> ""
        ix2 = ix2 + 1
        Sheets(2).Cells(ix2, 1) = strPath
        Sheets(2).Cells(ix2, 2) = strFilename
        Sheets(2).Cells(ix2, 3) = strPath & "\" & strFilename
        strFilename = Dir()
    Loop
'検索レベルがシングルのときは、取得終了
    Select Case strFindlvl
    Case "S"
        Exit Sub
    End Select
'検索レベルがマルチのとき、サブフォルダまで検索
    With CreateObject("Scripting.FileSystemObject")
        For Each objFile In .GetFolder(Path).SubFolders
            Call SUB99_DIRLIST(objFile.Path)
        Next objFile
    End With
End Sub

補足説明

プログラムや設定シートなどについて補足いたします。

◆AND条件の成立を判定する

①.検索文字の対象個数を求めておきます。

②.Instr関数で1行ごとに検索文字を検索し、存在すればヒットした件数をアップ(+1)します。

③.1行ごとに検索文字を全て検索し終えた後、①と②の値が一致すれば「AND条件の成立」です。

◆AND条件のときの「D列:検索文字」

検索文字が複数あるため、[,]で連結しています。

◆コーディング上の工夫
検索条件である[AND,OR]の判定は、[IF]ではなく[SELECT CASE]にしました。

[IF]について、COBOLでは①、②ともOKですが、VBAでは①はエラーになります。

①.×:IF Sheets(1).Cells(cnsFROM - 1, 2) = "AND" Or "OR" THEN

②.〇:IF Sheets(1).Cells(cnsFROM - 1, 2) = "AND" Or Sheets(1).Cells(cnsFROM - 1, 2) = "OR" THEN

VBA[SELECT CASE]であれば判定内容を[,]で繋いでいけば良いので、すっきりと見やすいです。

◆設定シートについて
イエロー部分を設定します。文字コードを指定するF列はどれか1つに'1'を入力します。

検索レベル
指定したフォルダ内直下のファイルのみか[S]、フォルダ内のサブフォルダ全て[M]を対象にするかを指定します。(S:シングルレベル、M:マルチレベル)

検索ファイルは、全てが対象なりますので、拡張子が[sql]、[txt]、[xml]などメモ帳や各種エディタで開いて閲覧できるファイルのみです。

1つのファイルを対象にしたい場合は、検索フォルダに対象ファイルだけを置いて、[S]シングルレベルを指定します。

検索文字
1つは必須です。2つ目以降は任意で入力できる分だけ指定できますが、検査結果の上限を想定し指定頂ければと思います。

◆文字コードの選択
ここでの「許容文字コード」は、著者が検証した内容です。

検証した文字コードは、メモ帳とサクラエディタで指定できる文字コードです。

マーキングしてある[SJIS,Shift-JIS]と[UTF16,Unicode]は、それぞれ「どちらを指定しても同じ」でした。

文字コード:Latin1では、日本語が表現できないようで、保存して再び開くと日本語部分が[?]になっていました。ですから、検索可能できるのは日本語以外の文字だけでした。


繰り返しますが、ここは1つだけを選択してF列に'1'を入力します。

注意点

サクラエディタなどにある詳細な検索指定はできません。

例えば・・・

英大文字と小文字を区別せず同じ文字として検索対象にすることはできず、[A]と指定すれば[a]も検索対象にすることはできません。

機能追加すればできますが、当バージョンでは対応しておりませんん。

その他も同様です。

最後に

Grepはプログラムなどを変更する際に「影響範囲を調査する」ために使用されることが多いでしょう。

プログラムや各種定義情報を退避・管理しているファイルの文字コードが同じである場合には、ご利用頂けると思います。

検索結果を少し加工すれば、チェックリストとして使用できます。

ご活用頂ければと思います。

EXCEL VBA
スポンサーリンク
- 面白かったらシェアお願いします! -
健史をフォローする
自分で改善

コメント