雪ん子パースペクティヴ

読むとちょっとタメになるエントリー。コメントあると嬉しいです。

【VBA】フォルダにあるファイル名を取得し、Excelのファイル名リストと照合を取る。

vba

vbaだからこんなに苦労したと思う。

 

はろー、yukiです。

 

今回のマクロは、例えば提出物の確認を取る時などに有効です。

特定のフォルダに提出物が出され、提出したファイルをExcelにリストで集計する時など。

フォルダにあるファイル名と、Excelにあるファイル名の整合性を取る必要があります。

フォルダにはあるがExcelには記載されていなかったり、Excelに記載したのに実際のファイルはなかったり、、。

 

そんな状況を避けるために使用するマクロになります。

手順は多いですが、下記のような感じになります。

※ここから「フォルダ」を「ディレクトリ」に言い換えます。その方が書きやすいので。

 

ーーーーーーーーーーーーーー

  1. ディレクトリにあるファイル名を収集
  2. 収集したファイル名を一つずつ、Excelにあるファイル名リストと照合
  3. 照合した結果、どちらもあるものを「OK」とする
  4. Excelにしかないものを「ファイルなし」とする
  5. ディレクトリにしかない場合、そのファイル名を別の列に入力
  6. Excelのファイル名リストに空行がある場合、「リストが空セル」とする

※処理の開始前に、実行選択を問う

※処理が終了後、完了のメッセージを表示する

ーーーーーーーーーーーーーー

 

まずは、実行の流れを画像でみましょう。

マクロと対象のfileディレクトリをenvディレクトリ内に用意します。

f:id:yuki_sasano:20190914141715p:plain

 

fileディレクトリ内には下記のようなファイルがあると想定します。

※[123456789.txt]はディレクトリのみにあるファイル

f:id:yuki_sasano:20190914141738p:plain

 

マクロのあるExcelには、下記のようなリストが用意されています。

※[987654321.txt]はExcelのみにあるファイル

f:id:yuki_sasano:20190914142729p:plain

 

マクロの開始ボタンを押下し、処理を開始します。

f:id:yuki_sasano:20190914142834p:plain

 

処理が完了すると、メッセージが表示されます。

f:id:yuki_sasano:20190914142843p:plain

 

結果は下記のようになります。

f:id:yuki_sasano:20190914142858p:plain

 

実行の結果、「OK、空セル、Excelのみ、ディレクトリのみ」の4種類を判別しています。

 

 

コードは下記になります。

時間のない方は、envディレクトリを作成し、このマクロとfileディレクトリを作成してください。

確認する対象のファイルはfileディレクトリに入れてください。

 

Sub fileConfirm()
' ディレクトリにあるファイルとExcelにあるファイルの照合を取る

Dim startProcess, endProcess
Dim s_message, s_type, s_title
Dim e_message, e_type, e_title
Dim listInDir, listInElx, listOnlyDir
Dim item()
Dim forNumFiles
Dim numFiles
Dim endList, endChk, endOnlyDir
Dim numNG, numValue
Dim chkOnlyDir, chkOnlyElx
Dim i, j, n, m ' for statement

' 処理の開始前にメッセージを表示
s_message = "リストにあるファイルとフォルダにあるファイルの照合確認をしますか？"
s_type = vbOKCancel
s_title = "処理の確認"
startProcess = MsgBox(s_message, s_type, s_title)

If (startProcess = 1) Then' 処理がOKされた場合

    ' 対象のディレクトリ（envディレクトリより前も絶対参照しても良い）
    Path = CurDir + "/env/file/."
    
    ' ディレクトリにあるファイル数を取得
    forNumFiles = Dir(Path, vbNormal)
    numFiles = 0
    
    Do While Len(forNumFiles) > 0
        numFiles = numFiles + 1
        forNumFiles = Dir()
    Loop
    
    ' 繰り返し処理のためデータを格納する配列の再定義
    ReDim Preserve item(numFiles)
    
    ' ディレクトリにあるファイル名を取得
    listInDir = Dir(Path, vbNormal)
    
    ' ディレクトリのファイル名を配列に格納
    For i = 1 To numFiles
        item(i) = listInDir
        listInDir = Dir()
    Next
    
    ' Excelにあるファイル名リストで値のある最後のセル位置を取得
    endList = Cells(Rows.Count, 2).End(xlUp).Row
    
    ' ディレクトリあるファイル名とExcelにあるファイル名を比較
    For Each fname In item
        ' 空白セルの判定
        If Not (fname = "") Then
            numValue = 0 ' ファイル名が値としてあるセルをカウント（値がある時は1以上）
            numNG = 0 ' ファイル名が一致しないセル数をカウント（一致しない時は1以上）
            
            For n = 3 To endList ' セル"B3"
                ' Excelにあるファイル名リストを取得
                listInElx = Cells(n, 2).Value ' B列
            
                ' 空白セルの判定
                If Not (listInElx = "") Then
                
                    numValue = numValue + 1
                
                    ' ディレクトリのファイル名を一つずつExcelのファイル名リストと比較
                    If (fname = listInElx) Then
                        Cells(n, 2).Offset(0, 1).Value = "OK"
                    Else
                        numNG = numNG + 1 ' ファイル名が一致しない場合
                    End If
                Else
                    Cells(n, 2).Offset(0, 1).Value = "リストが空セル"
                End If
            Next
            
            ' ディレクトリのみにあるファイル名の場合
            If (numValue = numNG) Then
                
                endOnlyDir = Cells(Rows.Count, 4).End(xlUp).Row
                
                'ファイル名がすでにある場合（この処理が一度されたファイル名の場合）
                If IsEmpty(Cells(3, 4).Value) Then
                    Cells(3, 4).Value = fname
                Else
                    For m = 3 To endOnlyDir
                        listOnlyDir = Cells(m, 4).Value ' D列
    
                        If (fname = listOnlyDir) Then
                            chkOnlyDir = chkOnlyDir + 1
                        End If
                    Next
                End If
                
　　　　　　　　'ディレクトリのみにあるファイル名を最初に入力する場合
                If (chkOnlyDir = 0) Then
                    Cells(endOnlyDir + 1, 4).Value = fname
                End If
            End If
        End If
    Next
    
    ' Excelのみにあるファイル名の場合
    endChk = Cells(Rows.Count, 3).End(xlUp).Row
    
    For j = 3 To endChk
        chkOnlyElx = Cells(j, 3).Value ' C列
        
        If IsEmpty(chkOnlyElx) Then
            Cells(j, 3).Value = "ファイルなし"
        End If
    Next
    
    ' 処理の終了後にメッセージを表示
    e_message = "全てのファイルの確認が終わりました。"
    e_type = vbOKOnly
    e_title = "確認メッセージ"
    MsgBox e_message, e_type, e_title

Else '処理がキャンセルされた場合
    MsgBox ("ファイルの照合確認を中止します。")
End If

End Sub

 


それでは、順番にみていきましょう。

 

1. 変数の定義

下記のコメントを参考にしてください。

Dim startProcess, endProcess ' 処理の開始前/終了後にメッセージを表示
Dim s_message, s_type, s_title ' 開始前メッセージボックスの値
Dim e_message, e_type, e_title ' 終了後メッセージボックスの値
Dim listInDir, listInElx, listOnlyDir ' 各リストのファイル名を格納
Dim item() ' ディレクトリにあるファイル名を配列で格納
Dim forNumFiles ' ディレクトリにあるファイル数取得用の繰り返し処理で使用
Dim numFiles ' ディレクトリにあるファイル数を格納
Dim endList, endChk, endOnlyDir ' Excelで値のある最後のセル位置を取得
Dim numNG, numValue ' Excelのみに記載されているファイル名取得
Dim chkOnlyDir, chkOnlyElx ' ディレクトリのみに記載されているファイル名取得
Dim i, j, n, m ' for文で使用

 

 

2. 処理前の確認

s_message = "リストにあるファイルとフォルダにあるファイルの照合確認をしますか？"
s_type = vbOKCancel
s_title = "処理の確認"
startProcess = MsgBox(s_message, s_type, s_title)

If (startProcess = 1) Then
'
' この中に処理を記述
'
Else 
    MsgBox ("ファイルの照合確認を中止します。")
End If

ユーザが処理を実行する前に、メッセージボックスで確認を取ります。

 

 

3. ディレクトリにあるファイル名取得のため、ファイル数を取得

Path = CurDir + "/env/file/."
    
forNumFiles = Dir(Path, vbNormal)
numFiles = 0
    
Do While Len(forNumFiles) > 0
    numFiles = numFiles + 1
    forNumFiles = Dir()
Loop

CurDir関数で、このExcelのあるディレクトリ位置を取得します。

そこに追加で、対象のファイルが格納されているディレクトリを指定します。

Dir関数で指定ディレクトリにあるファイル名を取得します。

vbNormalは既定値のオプションですが、ここでは明示化しています（なくても良い）。

変数forNumFilesにはファイル名が格納されているので、Stringの長さが0以上の場合、繰り返し処理をします。

変数numFilesで繰り返しされた処理の合計回数、すなわちファイル数を取得します。

 

 

4. ディレクトリにあるファイル名を取得

ReDim Preserve item(numFiles)
    
listInDir = Dir(Path, vbNormal)
    
For i = 1 To numFiles
    item(i) = listInDir
    listInDir = Dir()
Next

ReDimにより変数itemを、Preserveオプションを付して再定義します。

Preserveを付すことで、変数itemは繰り返し処理時、値を保持し続けることができます。

変数listInDirにディレクトリにあるファイル名を格納します。

For文により、変数itemにファイル名を配列で格納します。

 

Redimステートメントは下記を参照。

 

 

5. Excelにあるファイル名リストの値がある最後のセル位置を取得

endList = Cells(Rows.Count, 2).End(xlUp).Row

僕のサンプルでは、B列を基準としています。

A列の場合、Cells(Rows.Count, 1)になります。

 

 

6. 変数itemに配列の値がある分だけ処理を繰り返す

For Each fname In item
'
' この中に処理を記述
'
Next

変数itemにある値は、一つずつ変数fnameに格納されます。

 

 

7. セルの値が空白の場合

If Not (fname = "") Then
'
' この中に処理を記述
'
End If

変数fnameの空白は、ディレクトリから空白を取得してしまった場合のための処理です。

※Dir関数で取得すると、最初の値は空白になると思います。

 

 

8. ディレクトリにあるファイル名がExcelのリストにない場合、ディレクトリにあるファイル名とExcelにあるファイル名が一致する場合、Excelのリストに空セルがある場合

numValue = 0
numNG = 0
   
For n = 3 To endList
    listInElx = Cells(n, 2).Value
            
    If Not (listInElx = "") Then
                
        numValue = numValue + 1
                
        If (fname = listInElx) Then
            Cells(n, 2).Offset(0, 1).Value = "OK"
        Else
            numNG = numNG + 1
        End If
    Else
        Cells(n, 2).Offset(0, 1).Value = "リストが空セル"
    End If
Next
            
If (numValue = numNG) Then
                
    endOnlyDir = Cells(Rows.Count, 4).End(xlUp).Row
                
    If IsEmpty(Cells(3, 4).Value) Then
        Cells(3, 4).Value = fname
    Else
        For m = 3 To endOnlyDir
            listOnlyDir = Cells(m, 4).Value
    
            If (fname = listOnlyDir) Then
                chkOnlyDir = chkOnlyDir + 1
            End If
        Next
    End If
                
    If (chkOnlyDir = 0) Then
        Cells(endOnlyDir + 1, 4).Value = fname
    End If
End If 

変数numValueとnumNGは、ディレクトリにはあるが、Excelにはないファイル名の取得に使用します。

繰り返し処理は、Excelのリストにあるファイル名の分だけ実行されます。

Excelのセルの空白を判定後、ファイル名が（値として）ある場合、変数numValueが1足されます。

セルが空白の場合、「リストが空セル」と入力します。

ディレクトリとExcelでファイル名が一致していれば、Excelのリストにあるファイル名の右横に「OK」と入力します。

一致しない場合、変数numNGが1足されます。

変数numValueとnumNGの値が同じ場合は、ディレクトリにあるファイル名がExcelにあるファイル名と比較された回数とマッチしなかった回数が同じ、つまり、そのファイル名がディレクトリのみにあることを意味します。

ディレクトリのみにあるファイル名は、Excelのリストで新たな列に入力されます。

ファイル名の追記は、リストに設けた項目名（例えば「ディレクトリのみ」など）を避け、既に入力された値のあるセルの下へ追記されます。

変数chkOnlyDirの役割は、インクリメント処理のあるIf文で使用され、すでにファイル名が追記されているかを確認するために用意されています。

 

 

9. Excelにのみファイル名がある場合（ディレクトリに該当ファイルがない場合）

endChk = Cells(Rows.Count, 3).End(xlUp).Row
    
For j = 3 To endChk
    chkOnlyElx = Cells(j, 3).Value
        
    If IsEmpty(chkOnlyElx) Then
        Cells(j, 3).Value = "ファイルなし"
    End If
Next

ディレクトリとExcelでのファイル名を照合後、空白のセルに「ファイルなし」と入力します。

つまり、ファイル名が一致した結果や、空セル以外の値なので、Excelのみにあるファイル名ということになります。

 

 

10. 処理後の確認 

e_message = "全てのファイルの確認が終わりました。"
e_type = vbOKOnly
e_title = "確認メッセージ"
MsgBox e_message, e_type, e_title

 

処理を終えた後、ユーザへ完了を報告します。

 

 

 

疲れました。大変疲れました。

単純な処理とはいえ、コードが長くなってしまいました（もしかして100行を越えてる？）。

あと、変数をたくさん使用してしまいましたね。

変数をフラグとしても採用しました（分かる人は分かる）。

 

これほど長いマクロは参考にならないかもしれません。

でも、あると便利だと思うんですよね。

分からないことがあれば、下のコメント欄にメッセージをください。

 

 

以上。

 

 

Googleアドセンス