[ 엑셀 VBA ] 간단한 파일 관리 및 분류 프로그램 만들기-첫번째

파일을 분류할 때 이런 경우가 있습니다.

어떤 파일은 A팀 자료도 있고 B팀 자료도 있지만,

A팀에서 만들었기 때문에 A팀 폴더에 분류하는 경우

 

웹 스크래핑 코드이면서, 부동산 수집 데이터인 경우와 같이

여러 가지 성질을 가지고 있지만,

폴더 시스템 구조 상 여러 개로 분리하여 관리하기가 쉽지 않아서

간단한 파일 관리 및 분류 프로그램을 

VBA 코드 공부할 겸 제작한 것입니다.

 

정리하면 주요 기능은

하나의 파일이 여러 분야에 속할 수 있는 경우 분류를 하는 것입니다.

 

 기본 구성

 

 

파일 불러오기를 누르면 파일을 선택하고 파일을 분류합니다.

위 파일은 workspace라는 json 파일을 파이썬과 VBA로 분류한 예시입니다.

 

셀을 더블클릭하면 아래 그림과 같이 바로가기와 실행하기 창이 열립니다.

 

바로가기를 누르면 해당 파일이 있는 폴더로 이동하고

실행하기를 누르면 해당 파일이 실행됩니다.

 

셀을 선택하거나 셀 범위를 선택 후 마우스 오른쪽 버튼을 누르면

해당 행의 데이터를 삭제할 수 있습니다.

단 8행이하만 가능하고, 1행~7행은 삭제가 불가합니다.

8행부터 마지막 행까지가 데이터가 입력되는 곳으로

시트 보호를 해 놓았기 때문에 데이터를 직접 조작하기는 어렵습니다.

 

다만, 암호를 별도로 걸지 않았기 때문에

필요할 때 시트 보호를 해제하면 셀의 직접 조작도 가능합니다.

 

확장자에 따라 유형을 분류합니다.

 

이 유형은 sheet3에 표로 사용자가 임의로 수정이 가능합니다.

 

검색은 7행에 필터를 걸어 검색하고,

엑셀 종료시 필터가 해제되어 전체 데이터가 모두 나타난 상태로 종료됩니다.

엑셀 시작시 7행에 다시 필터가 걸려

사용자는 항상 필터가 걸려있는 것처럼 보입니다.

 

반응형

 화면 구성하기

 

먼저 sheet1의 화면입니다.

 

파일 불러오기와 새로고침 버튼이 있습니다.

파일을 불러왔을 때 파일이 어떤 역할을 하는지 알아볼 수 있는 것이

파일명, 확장자, 용량, 그리고 파일 경로라고 생각해 

해당 데이터를 표시할 수 있게 만들었습니다.

 

분류는 해당 파일이 여러 분야에 동시에 포함될 수 있기 때문에

해당되는 분야를 모두 알 수 있도록 분류 할 수 있게 해 놓은 것입니다.

 

유형은 파일 확장자를 기준으로

한글파일인지, 그림파일인지, 엑셀파일인지 확인할 수 있게 만든 것입니다.

hwp나 hwpx 확장자 모두 한글 파일이고,

jpeg, png 등은 모두 그림파일이니 별도 확장자로만 분류하면

나중에 필터로 검색하기 쉽지 않을 것 같아 다시 분류된 데이터를 표시합니다.

 

728x90

 파일 불러오기

 

파일 불러오기 버튼의 기능은 다음과 같습니다.

 

먼저 파일 선택 창을 열어 파일을 선택합니다.

파일은 여러개를 동시에 선택할 수도 있습니다.

 

선택된 파일을 Collection에 담아 저장해 둡니다.

 

UserForm1을 열어, UserForm1의 ListBox에 파일 이름을 출력합니다.

 

파일 불러오기의 전체 코드입니다.

Public selectedFiles As Collection

Sub OpenFiles()
    Dim fd As FileDialog
    Dim selectedItems As FileDialogSelectedItems
    Dim filePath As Variant
    Dim fileName As String
    
    Set selectedFiles = New Collection
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = True
        .Title = "파일을 선택하세요"
        
        If .Show = -1 Then
            Set selectedItems = .selectedItems
            
            UserForm1.ListBox1.Clear
            
            For Each filePath In selectedItems
                fileName = Mid(filePath, InStrRev(filePath, "\") + 1)

                UserForm1.ListBox1.AddItem fileName

                selectedFiles.Add filePath
            Next filePath

            UserForm1.Show
        End If
    End With
    
    Set fd = Nothing
End Sub

 

 파일 분류하기

 

파일 불러오기 버튼을 누르면 

OpenFiles() 함수가 실행되고,

UserForm1의 ListBox에 파일 이름을 출력한 다음

UserForm1을 보여줍니다.

 

UserForm1은 다음과 같습니다.

 

위 그림에서 빨간색 네모의 ListBox는 단순히 파일의 이름을 나열하기만 합니다.

파란색 네모의 TextBox는 파일을 분류합니다.

위 그림의 경우 4개의 파일을 모두 VBA와 파이썬과 연관 있다고 분류하겠다는 의미입니다.

 

여기서 확인을 누르면 다음과 같이 됩니다.

 

csv 확장자를 갖는 파일은 sheet3에 있는 표에 분류되지 않았기 때문에

유형이 따로 표기되지 않습니다.

 

이 경우 sheet3의 표에 데이터 작성 후 

새로고침 버튼을 누르면 유형이 분류됩니다.

 

UserForm1의 확인 버튼을 눌렀을 때 실행되는 코드입니다.

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim ws3 As Worksheet
    Dim lookupRange As Range
    Dim row As Long
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim filePath As Variant
    Dim fileName As String
    Dim fileExt As String
    Dim fileSize As Double
    Dim fileType As Variant
    Dim textBoxes(1 To 6) As String

    Set ws = ThisWorkbook.ActiveSheet
    Set ws3 = ThisWorkbook.Sheets("Sheet3")
    
    If ws.ProtectContents Then
        ws.Unprotect
    End If

    Set lookupRange = ws3.ListObjects("표2").Range

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row
    If lastRow < 8 Then lastRow = 7
    
    row = lastRow + 1

    textBoxes(1) = TextBox1.Text
    textBoxes(2) = TextBox2.Text
    textBoxes(3) = TextBox3.Text
    textBoxes(4) = TextBox4.Text
    textBoxes(5) = TextBox5.Text
    textBoxes(6) = TextBox6.Text

    For i = 1 To selectedFiles.Count
        filePath = selectedFiles(i)

        For j = 1 To 6
            If textBoxes(j) <> "" Then
                ws.Cells(row, "G").Value = filePath

                fileName = Mid(filePath, InStrRev(filePath, "\") + 1)
                fileName = Left(fileName, InStrRev(fileName, ".") - 1)
                ws.Cells(row, "B").Value = fileName
                
                fileExt = Mid(filePath, InStrRev(filePath, ".") + 1)
                ws.Cells(row, "E").Value = fileExt

                fileSize = FileLen(filePath) / 1024
                ws.Cells(row, "F").Value = Round(fileSize, 2)

                fileType = ""
                On Error Resume Next
                fileType = Application.WorksheetFunction.VLookup(LCase(fileExt), lookupRange, 2, False)
                If Err.Number <> 0 Then
                    fileType = ""
                    Err.Clear
                End If
                On Error GoTo 0
                ws.Cells(row, "D").Value = fileType

                ws.Cells(row, "C").Value = textBoxes(j)
                
                row = row + 1
            End If
        Next j
    Next i
    ws.Range("A8:G1048576").Locked = True
    ws.Protect Password:="", _
              UserInterfaceOnly:=True, _
              AllowFiltering:=True, _
              AllowSorting:=True
    Unload Me
End Sub

 

먼저 시트보호를 해제합니다.

시트보호를 해제하지 않으면, 데이터가 입력이 되지 않습니다.

    If ws.ProtectContents Then
        ws.Unprotect
    End If

 

파일 유형을 확인할 sheet3의 표 범위를 가지고 옵니다.

표2는 해당 표의 이름입니다.

    Set lookupRange = ws3.ListObjects("표2").Range

 

데이터를 추가하기 위해 Sheet1의 A열을 기준으로

마지막 행을 찾습니다.

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row
    If lastRow < 8 Then lastRow = 7
    
    row = lastRow + 1

 

텍스트 박스의 값을 저장합니다.

    textBoxes(1) = TextBox1.Text
    textBoxes(2) = TextBox2.Text
    textBoxes(3) = TextBox3.Text
    textBoxes(4) = TextBox4.Text
    textBoxes(5) = TextBox5.Text
    textBoxes(6) = TextBox6.Text

 

선택된 파일들을 처리합니다.

OpenFile() 함수를 불러올 때 Collection에 저장한 파일 경로를 가지고 옵니다.

파일 경로를 하나씩 불러와

경로, 파일명, 확장자, 파일크기를 추출하고,

확장자를 통해 Sheet3과 비교하여 파일 유형을 분류합니다.

그리고 텍스트 박스의 값을 불러와 표시합니다.

텍스트 박스가 비어있으면 무시합니다.

    For i = 1 To selectedFiles.Count
        filePath = selectedFiles(i)

        For j = 1 To 6
            If textBoxes(j) <> "" Then
                ws.Cells(row, "G").Value = filePath

                fileName = Mid(filePath, InStrRev(filePath, "\") + 1)
                fileName = Left(fileName, InStrRev(fileName, ".") - 1)
                ws.Cells(row, "B").Value = fileName

                fileExt = Mid(filePath, InStrRev(filePath, ".") + 1)
                ws.Cells(row, "E").Value = fileExt

                fileSize = FileLen(filePath) / 1024
                ws.Cells(row, "F").Value = Round(fileSize, 2)

                fileType = ""
                On Error Resume Next
                fileType = Application.WorksheetFunction.VLookup(LCase(fileExt), lookupRange, 2, False)
                If Err.Number <> 0 Then
                    fileType = ""
                    Err.Clear
                End If
                On Error GoTo 0
                ws.Cells(row, "D").Value = fileType

                ws.Cells(row, "C").Value = textBoxes(j)
                
                row = row + 1
            End If
        Next j
    Next i

 

마지막으로 데이터가 들어있거나 앞으로 들어갈 

A8 ~ G1048576셀을 잠가줍니다.

그리고 UserForm을 닫습니다.

    ws.Range("A8:G1048576").Locked = True
    ws.Protect Password:="", _
              UserInterfaceOnly:=True, _
              AllowFiltering:=True, _
              AllowSorting:=True
    Unload Me

 

이 정도만 해도 주요 기능은 거의 구현이 되었습니다.

다음 블로그에서는 나머지 기능에 대해 

하나씩 다루어 보겠습니다.

여기를 방문하시면 더 많은 엑셀 관련 자료를 확인할 수 있습니다.

엑셀 공부하기
VBA 공부하기