[vba] 하위폴더 내의 모든 파일 정보 가져오기

일을 하다 보면 가끔 업무를 진행하던 경로 하위에 있는 데이들의 리스트를 만들어야 하는 경우가 있습니다. 하나의 폴더라면 어떻게 해보겠는데 그 폴더가 하위 뎁스가 연속해서 있고 저장되어 있는 파일이 불특정으로 다수 있는 경우 이를 리스트로 만드는 일은 정말 끔찍한 일이 아닐 수 없습니다.

이런 경우 손쉽게 리스트로 작성하는 스크립트를 만들어 보았습니다.

폴더 및 하위 폴더의 파일 명칭은 물론 저장된 경로, 파일의 만든날짜, 수정한 날짜 등의 속성을 표시할 수 있도록 하고 파일의 용량과 타입까지 자동으로 리스트로 만들어 보려 합니다.

이러한 코드를 작성하기 위하여 개발을 할 내용을 준비해봅니다.

1. 엑셀 vba 에서 사용 가능한 File 및 Directory 관련 개체를 알아보자.
2. 지정한 폴더내에 파일이 있는 경우 파일의 이름과 속성등을 가져와 기록해주자
3. 파일이 바뀔때마다 줄을 바꾸어 주자
4. 폴더를 만나게 되면 해당 폴더 하위의 파일을 찾아주자.
5. 2 ~ 4 번은 하위 폴더를 만날때 마다 계속해서 반복해주어야 하므로 재귀 함수로 동작하도록 구성하자.
   * 재귀함수란 자신이 자신을 호출하는 함수를 말합니다.
6. 연속해서 위치를 이동시켜야 하므로 range.offset(y,x) 를 이용하되 x, y 는 전역 변수 (public)로 설정해주자

요 정도면 원하던 기능의 구현이 가능할 것 같습니다.

제가 작성한 코드를 아래에 올려보겠습니다.

보시면서 위에 설명드린 동작이 어떤 과정으로 일어나는지 확인해 보세요.

Public fso As Object
Public offsetY As Integer
Public offsetX As Integer
Public stRng As Range

Sub getSubFileList()

    Dim fsoFolder As Object
    Dim aSht As Worksheet
    Dim rootpath As String
    
    rootpath = "C:\Users\mariine\Pictures\excel_test\"
    
    offsetX = 0
    offsetY = 0
    
    Set aSht = ActiveSheet
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFolder = fso.GetFolder(rootpath)

    Set stRng = aSht.Range("B2")

    getDataRecursive fsoFolder
    
End Sub

Sub getDataRecursive(ByVal baseFolder As Object)

    Dim tmpSubFolders As Object
    Dim tmpFiles As Object
    Dim tmpRng As Range
    
    offsetX = offsetX + 1
    
    Set tmpSubFolders = baseFolder.subFolders
    
    Set tmpFiles = baseFolder.Files
    
    For Each c In tmpFiles
        
        stRng.Offset(offsetY, offsetX).Value = c.Name                       ''파일명
        stRng.Offset(offsetY, offsetX - 2).Value = offsetY + 1              ''인덱스
        stRng.Offset(offsetY, offsetX - 1).Value = c.ParentFolder.Path      ''경로명
        stRng.Offset(offsetY, offsetX + 1).Value = c.Size / 1000# & "kb"    ''파일용량
        stRng.Offset(offsetY, offsetX + 2).Value = c.DateCreated            ''만든날짜
        stRng.Offset(offsetY, offsetX + 3).Value = c.DateLastModified       ''수정한날짜
        stRng.Offset(offsetY, offsetX + 4).Value = c.Type                   ''파일타입
        
        offsetY = offsetY + 1
    Next c
    
    offsetX = offsetX - 1
    
    For Each d In tmpSubFolders
        
        Dim tmpSub As Object
        Set tmpSubs = fso.GetFolder(d)

        getDataRecursive tmpSubs
        
    Next d     
    
End Sub

 

제일 상단의 4줄은 이번 코드에서 사용할 전역 변수를 설정해주는 모습입니다.

전역변수를 설정해주면 하위의 어떤 함수에서건 해당 값을 읽고 쓸 수 있게 됩니다. A 함수에서 변경한 전역 변수의 값을 B 함수에서도 그대로 사용이 가능하게 됩니다.

그 다음 만나는 Sub getSubFileList() 함수가 동작을 설정하는 함수가 되겠고요. FileSystemObject 라는 파일 및 디렉토리 작업이 가능한 COM 오브젝트를 선언하여 서브폴더나 폴더, 파일 등에 접근이 가능하도록 하고 있습니다.

그리고 그뒤로 이어지는 재귀 함수인 Sub getDataRecursive(ByVal baseFolder As Object) 를 호출하는 것으로 동작이 시작됩니다. 

그렇게 시작된 재귀함수는 입력받은 폴더내에 파일이 있으면 파일의 이름 및 기타 속성들을 줄을 바꾸어 가며 기록을 진행하고 모든 파일에 대하여 기록이 끝나면 그아래 해당 폴더에 하위 폴더가 있는지 검사한뒤 하위 폴더가 있는 경우 또다시 자신 (폴더내의 파일을 찾아 기록하는 함수) 을 호출하여 주게 되는 겁니다.

이렇게 해주면 하나의 재귀함수로 아무리 깊고 많은 뎁스의 폴더안의 파일들도 모두 탐색이 가능해 지게 되겠습니다.

이렇게 만들어진 리스트는 아래와 같습니다.

 

만약 특정 확장자만 검색하고 싶으시면 실제 파일 속성을 기록하는 For 구문의 바로 첫 행에 IF 문을 이용하여 파일 이름내에 필요한 확장자가 있는지 검사를 진행하면 되겠습니다.

예를 들어 xlsx 파일만 리스트로 만들고 싶다면 해당 For 구문을 아래와 같이 변경하면 되겠습니다.

    For Each c In tmpFiles
        
        If InStr(c.Name, ".xlsx") Then
            stRng.Offset(offsetY, offsetX).Value = c.Name                       '파일명
            stRng.Offset(offsetY, offsetX - 2).Value = offsetY + 1              '인덱스
            stRng.Offset(offsetY, offsetX - 1).Value = c.ParentFolder.Path      '경로명
            stRng.Offset(offsetY, offsetX + 1).Value = c.Size / 1000# & "kb"    '파일용량
            stRng.Offset(offsetY, offsetX + 2).Value = c.DateCreated            '만든날짜
            stRng.Offset(offsetY, offsetX + 3).Value = c.DateLastModified       '수정한날짜
            stRng.Offset(offsetY, offsetX + 4).Value = c.Type                   '파일타입
            offsetY = offsetY + 1
        End If
        
    Next c

어렵지 않죠?

만약 있어서는 안되는 확장자가 들어있다면 눈에 띄게 표시를 하는것도 어렵지 않습니다.

예를 들어 폴더내에 hwp 파일만 있어야 한다고 했을때 해당 폴더에 .doc 파일처럼 또 다른 있는 경우 빨간색으로 셀을 표시할 수 있습니다.

For Each c In tmpFiles
        
        stRng.Offset(offsetY, offsetX).Value = c.Name                       '파일명
        stRng.Offset(offsetY, offsetX - 2).Value = offsetY + 1              '인덱스
        stRng.Offset(offsetY, offsetX - 1).Value = c.ParentFolder.Path      '경로명
        stRng.Offset(offsetY, offsetX + 1).Value = c.Size / 1000# & "kb"    '파일용량
        stRng.Offset(offsetY, offsetX + 2).Value = c.DateCreated            '만든날짜
        stRng.Offset(offsetY, offsetX + 3).Value = c.DateLastModified       '수정한날짜
        stRng.Offset(offsetY, offsetX + 4).Value = c.Type                   '파일타입
        
        If Not InStr(c.Name, ".hwp") Then
            stRng.Offset(offsetY, offsetX).Interior.Color = RGB(255, 0, 0)
        End If
        
        offsetY = offsetY + 1
        
    Next c

참 쉽죠?

 

이렇게 해서 폴더내의 모든 파일을 하위폴더를 포함하여 가져오는 스크립트를 알아보았습니다. 이런식의 작업은 엑셀의 워크시트 함수만을 이용하여 작업하기는 어렵기 때문에 vba를 좀 할줄 알면 아주 많은 시간을 절약하는 것이 가능합니다. 

 

오늘은 여기까지 하도록 하겠습니다.

관련하여 사전 지식이 부족하신 분은 아래 관련 포스트를 링크해 드리니 참고하시면 되겠습니다.

2019/06/01 - [DEV/VBA] - 엑셀 VBA 시작하기

엑셀 VBA 시작하기

2019/08/30 - [DEV/VBA] - [VBA]RGB 색상 값이 들어있는 셀에 셀 색상 지정하기

[VBA]RGB 색상 값이 들어있는 셀에 셀 색상 지정하기

2019/07/12 - [DEV/VBA] - [vba] For 구문 이용하기와 offset 사용 해 보기

[vba] For 구문 이용하기와 offset 사용 해 보기

2019/07/12 - [DEV/VBA] - [vba] 자동화를 위하여 엑셀의 영역 설정하는 방법

[vba] 자동화를 위하여 엑셀의 영역 설정하는 방법

 

감사합니다.

 

공감은 블로그 포스팅을 진행하는 블로거에게 큰 힘이 됩니다~