VBA_08_Dictionary_Class_Collection_FileSystemObject_Sample

Keyword
---
Class
Collection
Dictionary
FileSystemObject
File
Variant
Dynimac Array

Sample code
---
```vb:人ごとに月の勤務時間合計を計算する
Public Sub MySample20220529_2()

    Dim myPathStr As String
    Dim myFSO As New FileSystemObject: Dim myFile As File
    Dim myTargetBook As Workbook: Dim myTargetSheet As Worksheet
    Dim i As Long, j As Long
    Dim myFileNmList As Dictionary: Dim myShtNmList As Dictionary
    Dim myWorkTimeList As Collection: Dim myWorkTime As Variant
    Dim myClassWorkTime As Class_WorkTime
    Dim mySheetData() As Variant
    Dim myTotalWorkTimeList As New Dictionary
    
    myPathStr = "H:\2022\Input"
    
    Set myFSO = New FileSystemObject
    
    For Each myFile In myFSO.GetFolder(myPathStr).Files
        
        Set myTargetBook = Workbooks.Open(myFile, , True)
        
        If myTargetBook Is Nothing Then
            MsgBox "ブックが存在しません。"
            Exit Sub
        End If
        
        Set myFileNmList = New Dictionary
        
        If Not myFileNmList.Exists(myFile.Name) Then
            myFileNmList.Add myFile.Name, New Dictionary
        End If
        
        Set myShtNmList = myFileNmList.Item(myFile.Name)
        
        For i = 7 To 9
        
            Set myTargetSheet = myTargetBook.Worksheets(i & "月")
            
            If Not myTargetSheet Is Nothing Then
        
                If Not myShtNmList.Exists(i & "月") Then
                
                    myShtNmList.Add i & "月", New Collection
                    
                    Set myWorkTimeList = myShtNmList.Item(i & "月")
                    
                    mySheetData = myTargetSheet.UsedRange
                    
                    For j = LBound(mySheetData, 1) To UBound(mySheetData, 1)
                    
                        If mySheetData(j, 2) <> "" Then
                            Set myClassWorkTime = New Class_WorkTime
                            
                            myClassWorkTime.myDate = mySheetData(j, 1)
                            myClassWorkTime.myDayOfWeek = mySheetData(j, 2)
                            myClassWorkTime.myWorkTime = mySheetData(j, 4)
                            
                            myWorkTimeList.Add myClassWorkTime
                        End If
                        
                    Next j
                    
                    For Each myWorkTime In myWorkTimeList
                        Set myClassWorkTime = myWorkTime
                        
                        If Not myTotalWorkTimeList.Exists(myFile.Name) Then
                            myTotalWorkTimeList.Add myFile.Name, myClassWorkTime.myWorkTime
                        Else
                            myTotalWorkTimeList.Item(myFile.Name) = _
                                myTotalWorkTimeList.Item(myFile.Name) + myClassWorkTime.myWorkTime
                        End If
                        
                    Next
                    
                    Debug.Print myFile.Name, _
                        myTargetSheet.Name, _
                        myTotalWorkTimeList.Item(myFile.Name)
                    
                End If
            
            End If
            
        Next i
        
        myTargetBook.Close False
        
    Next

End Sub
```
Sample data
---
![無題8_1.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2672288/7e6ac35a-b142-6f35-e4a5-599fed4acd93.jpeg)
![無題8_2.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2672288/42173ef4-aef3-8c66-041c-d326a09e83a4.jpeg)
![無題8_3.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2672288/2da5e369-05fb-bf94-827c-e467c90dff20.jpeg)