[엑셀/VBA] 폴더 내 엑셀파일 한번에 통합하기(엑셀파일병합, 자동화기본)

이번에는 엑셀 VBA 기능을 이용해서 폴더 내 파일을 통합해보도록 하겠습니다.

 

이미 이전 발행 글 중에서 '파워쿼리' 및 '파이썬'을 통해 폴더 내 엑셀파일 통합하는 방법을 다루어보았는데요

엑셀 VBA는 파이썬을 못하시는 직장 동료에게는 파이썬보다 훨씬 공유하기가 좋고, 파워쿼리가 못하는 부분도 다룰 수 있어서 배워두면 도움되실 것 같습니다.

 

▼ 이전발행글은 참고 해주세요 ▼

---

2022.05.09 - [엑셀(Excel)] - [파워쿼리 5강] 엑셀 파일 병합_자동화 기본편

파워쿼리는 별도의 코드 없이 마우스만으로 쉽게 자동화가 가능합니다 !! 파워쿼리로 자동화 강추 드립니다.

[파워쿼리 5강] 엑셀 파일 병합_자동화 기본편

2022.11.22 - [파이썬(Python)] - [파이썬/Python] 파이썬으로 폴더 내 엑셀파일 한번에 통합하기

파이썬을 이미 어느정도 아시는 분들은 파이썬 강추 드립니다

[파이썬/Python] 파이썬으로 폴더 내 엑셀파일 한번에 통합하기

---

 

1. 문제상황 및 해결그림

- 한 폴더 내에 동일한 형태의 매출현황 데이터가 월별로 정리되어 있습니다.

- 예시를 위하여 데이터의 크기는 5행 정도로 작게 잡았습니다.

- 엑셀 파일 한 곳에 1 ~ 9월 전체 데이터를 통합하여 보고 싶습니다.

 

2. 경로 불러오기

- 해당 폴더의 경로를 먼저 불러옵니다. 

- 경로 내에 파일을 읽어야 합니다. 

Sub Mergefile()
    '변수
    Dim strPath As String, fName As String
    
    '경로 불러오기
    strPath = "D:\test" '해당되는 경로 지정
    fName = Dir(strPath & "*.xls*") ' Dir은 엑셀폴더 반복해서 돌게 하는 역할
End Sub

 - strPath라는 변수 내에 해당되는 폴더의 경로를 넣어주시면 됩니다. 

 - 경로는 파일이름이 있는 곳에 위치해 있습니다.

폴더 경로 위치

 - 저 같은 경우에는 D드라이브 밑에 > 어딘가 밑에 > Blog_엑셀 > 엑셀_VBA 이런 형태로 경로인 것으로 보입니다.

 - "*.xls*"에서 *는 앞에 무슨 문자가 오던지 신경쓰지 않겠다 라는 뜻입니다.

    따라서, " ~~어떤 문자가 오던지.xlsx', " ~~어떤 문자가 오던지.xlsm', " ~~어떤 문자가 오던지.xls' 등 다양한 포맷의 형태를 부르겠다는 말입니다.

 - Dir의 경우 폴더를 반복해서 도는 명령어 입니다.

 

3. 경로 내 폴더 없을 경우 종료

 - 경로 내 폴더 없을 경우 종료되는 코드입니다. 

    If fName = "" Then
        MsgBox "폴더 내 엑셀파일이 존재하지 않습니다."
        Exit Sub
    End If

 - 메세지 박스로 "폴더 내 엑셀파일이 존재하지 않습니다."라는 창을 띄워준 후 VBA를 종료합니다.

 

4. 파일이 존재하면 빈 시트에 업데이트 해줍니다. (통합해주는 매크로)

 - Do While ~ Loop 문으로 무한반복 해줍니다.

    Do While fName <> "" '파일이 존재하는 동안 = 공백이 아닌 동안
        Set wb = Workbooks.Open(Filename:=strPath & fName, UpdateLinks:=0)
        'UpdateLinks:=0 다른 시트 파일 참조하는 경우, 확인창 뜨는 걸 막아두는 역할
        Set rngS = wb.Sheets(1).UsedRange
        Set rngS = rngS.Offset(1).Resize(rngS.Rows.Count - 1)
        
        '행의 개수
        cntRows = rngS.Rows.Count
        
        Sheet1.Cells(Rows.Count, "a").End(3)(2).Resize(cntRows, 2) = rngS.Value '2는 열의 갯수 고정
        Cells(Rows.Count, "c").End(xlUp).Offset(1) = fName
        
        wb.Close 'Workbook종료
        fName = Dir ' Dir로 파일 반복
    Loop

 - fName <> "" : 파일명이 빈칸이 아니라면 이라는 뜻으로 파일이 존재하면 ! 이라고 해석하고 사용하시면 됩니다. 

 - UpdateLinks:=0의 경우, 다른 시트 파일 참조하는 경우, 확인창 뜨는 걸 막아두는 역할을 합니다.

 - rngS라는 Range 변수를 잡은 다음 Sheet1에서 사용되는 범위를 잡습니다.

 - 저희는 머릿글 내용까지 들고올 필요가 없기 때문에 Resize로 범위를 수정해줍니다. 

Resize를 하는 이유 &rarr; 머릿글 삭제

 

5. [최종] 전체코드 통합

Sub MergeFile_InFolder()
    Dim strPath As String, fName As String
    Dim wb As Workbook
    Dim rngS As Range
    Dim cntRows As Long
    
    Application.ScreenUpdating = False '화면 업데이트 안보이게 해서 속도 향상(추천)
    Application.DisplayAlerts = False '저장하시겠습니까? 등의 창 안나타나게 하는 것
    
    Sheet1.UsedRange.Offset(1).Clear
    
    strPath = "개별경로지정"
    fName = Dir(strPath & "*.xls*") ' Dir은 엑셀폴더 반복해서 돌게 하는 역할
    
    If fName = "" Then
        MsgBox "폴더 내 엑셀파일이 존재하지 않습니다."
        Exit Sub
    End If
    
    Do While fName <> "" '파일이 존재하는 동안 = 공백이 아닌 동안
        Set wb = Workbooks.Open(Filename:=strPath & fName, UpdateLinks:=0)
        'UpdateLinks:=0 다른 시트 파일 참조하는 경우, 확인창 뜨는 걸 막아두는 역할
        Set rngS = wb.Sheets(1).UsedRange
        Set rngS = rngS.Offset(1).Resize(rngS.Rows.Count - 1)
        
        '행의 개수
        cntRows = rngS.Rows.Count
        
        Sheet1.Cells(Rows.Count, "a").End(3)(2).Resize(cntRows, 2) = rngS.Value '2는 열의 갯수 고정
        Cells(Rows.Count, "c").End(xlUp).Offset(1) = fName
        
        wb.Close 'Workbook종료
        fName = Dir ' Dir로 파일 반복
    Loop

    Sheet1.Range("a1").CurrentRegion.Borders.LineStyle = 1 '테두리 그려주는 옵션
    
    Application.ScreenUpdating = True '상단에 False명령어 원상복귀
    Application.DisplayAlerts = True '상단에 False명령어 원상복귀
End Sub

 - 변수 등은 별도로 설명드리지 않았습니다.

결과를 확인해보면 파일 통합이 정상적으로 진행됨을 알 수 있습니다.

 

파일병합.gif

 

많이 활용하시길 바랍니다.