[VBA] Layout 작업하기

What to do?

 

여러개의 Layout 파일이 있다고 가정하자.

각 Layout 파일의 첫번째 시트에는 A열에는 "담보" 라는 단어와 함께 담보순번이 적혀있다.

Layout1.xlsx

 

매크로 파일의 List 시트에는 각 파일에 보여주고 싶은 담보순번만 다음과 같은 양식으로 정리하였다. 

test.xlsm  Layout시트

 

List 시트에 있는 각각의 Layout 파일을 열어서 특정 담보번호만 남기고 나머지는 행 숨기기를 하고 싶다.

---

전체 코드

Option Explicit
    Public BoolArrDict As Object
    Public fllePathCollection As Collection
       
Sub setBoolArrDict()
    Dim ii, seq As Long
    Dim boolArr(3000) As Boolean
    Dim fileName As String
    
    Set BoolArrDict = CreateObject("Scripting.Dictionary")
    
    With ThisWorkbook.Worksheets("List")
        
        For ii = 2 To .Range("a1", Range("a1").End(xlDown)).Rows.Count
                
            fileName = .Cells(ii, 1)            ' 파일명
            seq = .Cells(ii, 2)                 ' 담보순번
            
            boolArr(seq) = True
            
            If fileName <> .Cells(ii + 1, 1) Then
                BoolArrDict.Add fileName, boolArr
                Erase boolArr
            End If
            
                        
        Next ii
    End With

End Sub


Sub setFilePathCollection()

    Dim dialog As fileDialog
    Dim filePath As Variant
    
    Set fllePathCollection = New Collection
    
       
    Set dialog = Application.fileDialog(msoFileDialogFilePicker)
    dialog.Show

    For Each filePath In dialog.SelectedItems
        fllePathCollection.Add filePath
    Next filePath
       
End Sub

Function getFileNameFromFilePath(filePath)

    Dim arr As Variant
    arr = Split(filePath, "\")
    getFileNameFromFilePath = arr(UBound(arr))

End Function


Sub main()
    Dim fileName As String
    Dim filePath, boolArr As Variant
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim ii, seq, start_idx As Long
    Call setBoolArrDict
    Call setFilePathCollection

    For Each filePath In fllePathCollection
        fileName = getFileNameFromFilePath(filePath)
        boolArr = BoolArrDict(fileName)
        
        Set wb = Workbooks.Open(filePath)
        Set ws = wb.Worksheets(1)
        
        ws.Rows("1:3000").Hidden = False
        
        With ws
            
            For ii = 1 To 3000
                If .Cells(ii, 1) = "담보" Then
                    start_idx = ii
                    Exit For
                End If
            Next ii
        
            For ii = start_idx + 1 To 3000
                
                If .Cells(ii, 1) = "" Then
                    Exit For
                End If
                
                If (IsNumeric(.Cells(ii, 1))) Then
                    seq = .Cells(ii, 1)
                End If
                
                If seq > 0 Then .Rows(ii & ":" & ii).Hidden = Not (boolArr(seq))
                
            Next ii
        End With
        
        wb.Close SaveChanges:=True
        
    Next filePath
End Sub

 

test.xlsm

0.02MB

Layout1.xlsx

0.01MB

Layout2.xlsx

0.01MB

 

---

코드설명 

 

 변수선언

Option Explicit
    Public BoolArrDict As Object
    Public fllePathCollection As Collection

• BoolArrDict
  • 키 : 파일명
  • 값 : Boolean Array
    • 담보순번 1번이 리스트에 존재 → arr(1) = true
    • 담보순번 1번이 리스트에 존재하지 않음  → arr(1) = false
• filePathCollection
  • Layout 파일 경로를 모아놓은 collection

 

---

BoolArrDict 세팅

Sub setBoolArrDict()
    Dim ii, seq As Long
    Dim boolArr(3000) As Boolean
    Dim fileName As String
    
    Set BoolArrDict = CreateObject("Scripting.Dictionary")
    
    With ThisWorkbook.Worksheets("List")
        
        For ii = 2 To .Range("a1", Range("a1").End(xlDown)).Rows.Count
                
            fileName = .Cells(ii, 1)            ' 파일명
            seq = .Cells(ii, 2)                 ' 담보순번
            
            boolArr(seq) = True
            
            If fileName <> .Cells(ii + 1, 1) Then
                BoolArrDict.Add fileName, boolArr
                Erase boolArr
            End If
            
                        
        Next ii
    End With

End Sub

• 매크로 파일의 List시트를 읽어서 BoolArrDict를 세팅
• 담보순번은 최대 3000을 넘지 않을 것이라고 생각해서 변수 선언시에 Dim boolArr(3000) As Boolean 와 같이 선언

---

filePathCollection 세팅

Sub setFilePathCollection()

    Dim dialog As fileDialog
    Dim filePath As Variant
    
    Set fllePathCollection = New Collection
    
       
    Set dialog = Application.fileDialog(msoFileDialogFilePicker)
    dialog.Show

    For Each filePath In dialog.SelectedItems
        fllePathCollection.Add filePath
    Next filePath
       
End Sub

• 파일 선택기 화면을 열고, 선택한 파일 경로를 fileCollection에 넣음

---

파일경로에서 파일명 추출하기

Function getFileNameFromFilePath(filePath)

    Dim arr As Variant
    arr = Split(filePath, "\")
    getFileNameFromFilePath = arr(UBound(arr))

End Function

•  (파일경로) C:\Users\N\Desktop\test\Layout1.xlsx → (파일명) Layout1.xlsx

---

main 함수

Sub main()
    Dim fileName As String
    Dim filePath, boolArr As Variant
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim ii, seq, start_idx As Long
    
    ' ⓐ 매크로 파일 List 시트를 읽어서 BoolArrDict를 세팅함
    Call setBoolArrDict
    
    ' ⓑ 파일 선택창을 열어서 filePathCollection을 세팅함
    Call setFilePathCollection
    
    ' ⓒ filePathCollection에 있는 파일을 순회
    For Each filePath In fllePathCollection
    
        fileName = getFileNameFromFilePath(filePath)
        boolArr = BoolArrDict(fileName)
        
        ' ⓓ Layout 파일 열기
        Set wb = Workbooks.Open(filePath)
        Set ws = wb.Worksheets(1)
        
        ' ⓔ 1:3000행 행숨기기 풀기
        ws.Rows("1:3000").Hidden = False
        
        With ws
            
            ' ⓕ A열에서 '담보'라는 글자가 나오는 셀 찾기
            For ii = 1 To 3000
                If .Cells(ii, 1) = "담보" Then
                    start_idx = ii
                    Exit For
                End If
            Next ii
        
            ' ⓖ A열에서 '담보'라는 셀 다음부터
            For ii = start_idx + 1 To 3000
                
                ' ⓗ 빈 셀이 나올 때까지 반복문 돌기
                If .Cells(ii, 1) = "" Then
                    Exit For
                End If
                
                ' ⓘ 담보순번으로 행 숨기기 or 숨기기 취소
                If (IsNumeric(.Cells(ii, 1))) Then
                    seq = .Cells(ii, 1)
                End If
                
                If seq > 0 Then .Rows(ii & ":" & ii).Hidden = Not (boolArr(seq))
                
            Next ii
        End With
        
        ' ⓙ 저장하고 닫기
        wb.Close SaveChanges:=True
        
    Next filePath
End Sub