# Excel VBAでシェルスクリプトの入出力ファイルを逆追跡するツールを作った話(試作版)


## 🧩 はじめに

業務で複数の `.sh`（シェルスクリプト）が連携してファイルを入出力しながら動作するシステムを扱っていて、

> 「この帳票（CSV）は、どの `.sh` を通ってここまで来たの？」

といった逆追跡のニーズがありました。

今回は、**帳票出力プログラム名**（またはその `.sh`）を起点にして、  
**Input最大6つ／Output最大3つ**の情報を含めた実行フローを**Excel VBAで自動出力**するツールを作成したので紹介します。

---

## 📌 解決したい課題

- `.sh`ファイルが多く、**どの処理がどのファイルを作っているか把握しづらい**
- 手作業での調査では**ミスや漏れが起きやすい**
- **ファイル依存の全体像**（フロー図など）を明らかにしたい

---


## VBAコード全文（Input1〜6／Output1〜3対応）

```vba
Sub TrackFromRFCprogram_Recursive()
    Const folderPath As String = "C:\Script"
    Dim targetProgram As String
    targetProgram = Sheets("入力").Range("B3").Value

    If targetProgram = "" Then
        MsgBox "プログラムIDを入力してください"
        Exit Sub
    End If

    Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
    Dim folder As Object: Set folder = fso.GetFolder(folderPath)
    Dim file As Object

    Dim visited As Object: Set visited = CreateObject("Scripting.Dictionary")
    Dim traceList As Collection: Set traceList = New Collection
    Dim searchQueue As Collection: Set searchQueue = New Collection

    Dim txt As String, prog As String, shFile As String
    Dim inputs As Variant, outputs As Variant
    Dim targetFile As String, i As Integer, key As String

    ' 起点探索
    For Each file In folder.Files
        If LCase(file.Name) Like "*.sh" Then
            txt = ReadAllTextUTF8(file.Path)
            prog = GetRFCprogramid(txt)
            If prog = targetProgram Then
                shFile = file.Name
                inputs = GetFileInputs(txt)
                outputs = GetFileOutputs(txt)
                traceList.Add Array(shFile, prog, inputs, outputs)

                If IsArray(inputs) Then
                    For i = LBound(inputs) To UBound(inputs)
                        key = inputs(i)
                        If key <> "" And Not visited.Exists(key) Then
                            searchQueue.Add key
                            visited.Add key, True
                        End If
                    Next i
                End If
                Exit For
            End If
        End If
    Next

    ' 逆探索ループ
    Do While searchQueue.Count > 0
        targetFile = searchQueue(1)
        searchQueue.Remove 1

        For Each file In folder.Files
            If LCase(file.Name) Like "*.sh" Then
                txt = ReadAllTextUTF8(file.Path)
                outputs = GetFileOutputs(txt)
                If IsArray(outputs) Then
                    For i = LBound(outputs) To UBound(outputs)
                        If Trim(LCase(outputs(i))) = Trim(LCase(targetFile)) Then
                            prog = GetRFCprogramid(txt)
                            shFile = file.Name
                            inputs = GetFileInputs(txt)
                            traceList.Add Array(shFile, prog, inputs, outputs)

                            If IsArray(inputs) Then
                                Dim k As Integer
                                For k = LBound(inputs) To UBound(inputs)
                                    key = inputs(k)
                                    If key <> "" And Not visited.Exists(key) Then
                                        searchQueue.Add key
                                        visited.Add key, True
                                    End If
                                Next k
                            End If
                            Exit For
                        End If
                    Next i
                End If
            End If
        Next
    Loop

    ' 出力
    Dim ws As Worksheet: Set ws = Sheets.Add
    ws.Name = "逆追跡"
    Dim stepCol As Long: stepCol = 1
    For i = 1 To traceList.Count
        Dim row As Variant: row = traceList(i)
        ws.Cells(1, stepCol).Value = "Step " & i
        ws.Cells(2, stepCol).Value = "シェルファイル": ws.Cells(2, stepCol + 1).Value = row(0)
        ws.Cells(3, stepCol).Value = "プログラムID": ws.Cells(3, stepCol + 1).Value = row(1)

        Dim j As Integer, outputRowOffset As Integer
        If IsArray(row(2)) Then
            For j = 0 To UBound(row(2))
                ws.Cells(4 + j, stepCol).Value = "Input" & j + 1
                ws.Cells(4 + j, stepCol + 1).Value = row(2)(j)
            Next j
        End If

        If IsArray(row(3)) Then
            outputRowOffset = 4 + IIf(IsArray(row(2)), UBound(row(2)) + 1, 0)
            For j = 0 To UBound(row(3))
                ws.Cells(outputRowOffset + j, stepCol).Value = "Output" & j + 1
                ws.Cells(outputRowOffset + j, stepCol + 1).Value = row(3)(j)
            Next j
        End If
        stepCol = stepCol + 3
    Next i

    MsgBox "出力完了"
End Sub

' UTF-8読み込み
Function ReadAllTextUTF8(filePath As String) As String
    Dim stream As Object: Set stream = CreateObject("ADODB.Stream")
    With stream
        .Charset = "utf-8"
        .Open
        .LoadFromFile filePath
        ReadAllTextUTF8 = .ReadText
        .Close
    End With
    Set stream = Nothing
End Function

' RFCprogramidを抽出
Function GetRFCprogramid(txt As String) As String
    Dim regex As Object: Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "RFCprogramid\s*=\s*""([^""]+)"""
    regex.IgnoreCase = True: regex.Global = False
    If regex.Test(txt) Then
        GetRFCprogramid = regex.Execute(txt)(0).SubMatches(0)
    Else
        GetRFCprogramid = ""
    End If
End Function

' INファイルを抽出
Function GetFileInputs(txt As String) As Variant
    Dim regex As Object: Set regex = CreateObject("VBScript.RegExp")
    Dim matches As Object, m As Object
    regex.Pattern = "FILE_NAM\[\d+\]\s*=\s*""([^\s""]+)[^""]*""\s*#\s*IN"
    regex.IgnoreCase = True: regex.Global = True
    Set matches = regex.Execute(txt)

    Dim results() As String
    If matches.Count > 0 Then
        ReDim results(0 To matches.Count - 1)
        Dim i As Integer: i = 0
        For Each m In matches
            results(i) = m.SubMatches(0): i = i + 1
        Next
    Else
        ReDim results(0 To 0): results(0) = ""
    End If
    GetFileInputs = results
End Function

' OUTファイルを抽出
Function GetFileOutputs(txt As String) As Variant
    Dim regex As Object: Set regex = CreateObject("VBScript.RegExp")
    Dim matches As Object, m As Object
    regex.Pattern = "FILE_NAM\[\d+\]\s*=\s*""([^\s""]+)[^""]*""\s*#\s*OUT"
    regex.IgnoreCase = True: regex.Global = True
    Set matches = regex.Execute(txt)

    Dim results() As String
    If matches.Count > 0 Then
        ReDim results(0 To matches.Count - 1)
        Dim i As Integer: i = 0
        For Each m In matches
            results(i) = m.SubMatches(0): i = i + 1
        Next
    Else
        ReDim results(0 To 0): results(0) = ""
    End If
    GetFileOutputs = results
End Function

```