폴더 안 30개 엑셀 파일 자동 통합 — VBA Dir + Workbooks.Open 풀 가이드

지점 파일을 합칠 때 먼저 정해야 할 것은 파일 개수가 아니라 헤더와 실패 처리입니다. 이 예제는 Data 시트의 Branch, Sales 두 열을 가진 XLSX만 읽고, 헤더를 한 번만 쓰는 새 결과 통합 문서를 만듭니다. 중간 파일에서 계약이 어긋나면 부분 결과를 저장하지 않고 중단합니다. 원본 파일은 읽기 전용으로 엽니다.

제목의 30개 파일 업무를 처음부터 실제 파일로 시험하지 마세요. 첨부한 합성 파일 두 개로 데이터 4행·매출 합계 1,000을 먼저 확인한 뒤 같은 구조의 파일로 범위를 넓힙니다. 이 글에서 30개 파일을 Excel에서 실행하거나 소요 시간을 측정한 것은 아닙니다.

두 입력 파일에서 무엇을 읽는가

Windows 데스크톱 Excel의 VBA 편집기를 기준으로 합니다. 웹용 Excel에서 이 VBA 코드를 실행할 수 있다는 뜻은 아닙니다. 첨부 ZIP의 source 폴더에는 다음 두 파일만 있습니다. 각 파일의 시트 이름은 Data, 헤더는 A1=Branch, B1=Sales, 입력 범위는 A2:B3입니다.

숫자는 실제 지점 실적이 아닌 검산용 값입니다. 출력은 Merged 시트의 A1:B5이며 데이터는 A2:B5의 4행입니다. 헤더까지 세면 5행입니다. 파일 열거 순서는 합격 조건에 넣지 않고 A=100, B=200, C=300, D=400이 각각 한 번 나타나는지 확인합니다.

VBA를 넣고 폴더를 선택하기

1. ZIP을 새 연습 폴더에 풀고 source 폴더를 열어 두 XLSX 파일을 확인합니다. 원본 업무 파일을 이 source 폴더에 섞지 않습니다.

2. 빈 통합 문서의 다른 이름으로 저장 창에서 매크로 사용 형식 .xlsm을 선택합니다. .xlsx는 VBA 코드를 보존하는 용도가 아닙니다.

3. Alt+F11 → 삽입 → 모듈에서 아래 보완된 전체 코드를 붙여넣습니다. ZIP의 MergePractice.bas도 현재 본문과 같은 코드입니다. 이전 ZIP의 BAS를 이미 가져왔다면 그 모듈의 코드 전체를 아래 내용으로 교체하세요. 같은 이름의 매크로를 두 번 추가하지 않습니다.

4. MergeFolder를 실행하고 ZIP의 source 폴더를 선택합니다. 결과는 저장되지 않은 새 통합 문서로 열립니다.

5. 아래 검산이 모두 맞은 뒤 별도 파일명으로 저장합니다. 실패 시 저장된 결과 파일은 생기지 않아야 합니다.

헤더와 숫자를 확인한 뒤 값을 이어 붙이는 전체 코드

Dir로 파일 목록을 먼저 수집하므로 파일을 여는 중 다른 코드가 검색 상태에 영향을 주는 문제를 줄입니다. *.xlsx와 일치하는 선택한 폴더 파일만 읽으며 하위 폴더는 순회하지 않습니다. ~$ 임시 파일과 실행 중인 통합 문서는 제외합니다.

Option Explicit

Public Sub MergeFolder()
    Dim picker As FileDialog, folder As String, name As String, item As Variant
    Dim files As New Collection, source As Workbook, result As Workbook, openBook As Workbook
    Dim src As Worksheet, dst As Worksheet, lastRow As Long, nextRow As Long, r As Long
    Dim security As Long, updating As Boolean, events As Boolean
    Dim failure As String, closeFailure As Boolean
    Set picker = Application.FileDialog(msoFileDialogFolderPicker)
    If picker.Show <> -1 Then Exit Sub
    folder = picker.SelectedItems(1) & Application.PathSeparator
    name = Dir$(folder & "*.xlsx")
    Do While Len(name) > 0
        If Left$(name, 2) <> "~$" And folder & name <> ThisWorkbook.FullName Then files.Add name
        name = Dir$()
    Loop
    If files.Count = 0 Then
        MsgBox "No XLSX input files. Nothing created.": Exit Sub
    End If
    security = Application.AutomationSecurity
    updating = Application.ScreenUpdating
    events = Application.EnableEvents
    On Error GoTo Failed
    Application.AutomationSecurity = 3
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Set result = Workbooks.Add(xlWBATWorksheet)
    Set dst = result.Worksheets(1)
    dst.Name = "Merged"
    dst.Cells(1, 1).Value2 = "Branch"
    dst.Cells(1, 2).Value2 = "Sales"
    nextRow = 2
    For Each item In files
        Set src = Nothing
        Set source = Nothing
        For Each openBook In Application.Workbooks
            If StrComp(openBook.FullName, folder & CStr(item), vbTextCompare) = 0 Then
                Err.Raise vbObjectError + 656, "MergeFolder", "Input is already open. Close it yourself before retrying."
            End If
        Next openBook
        Set source = Workbooks.Open(folder & CStr(item), UpdateLinks:=0, ReadOnly:=True)
        Set src = source.Worksheets("Data")
        If CStr(src.Cells(1, 1).Value2) <> "Branch" Or CStr(src.Cells(1, 2).Value2) <> "Sales" Then
            Err.Raise vbObjectError + 650, "MergeFolder", "Expected Branch/Sales headers."
        End If
        lastRow = src.UsedRange.Row + src.UsedRange.Rows.Count - 1
        If lastRow < 2 Then Err.Raise vbObjectError + 651, "MergeFolder", "Input has no data rows."
        For r = 2 To lastRow
            If IsError(src.Cells(r, 1).Value2) Or IsError(src.Cells(r, 2).Value2) Then
                Err.Raise vbObjectError + 652, "MergeFolder", "An input cell contains an error."
            End If
            If Len(Trim$(CStr(src.Cells(r, 1).Value2))) = 0 Then
                Err.Raise vbObjectError + 653, "MergeFolder", "Branch is required on every row."
            End If
            If VarType(src.Cells(r, 2).Value2) = vbString _
                Or VarType(src.Cells(r, 2).Value2) = vbBoolean _
                Or IsEmpty(src.Cells(r, 2).Value2) _
                Or Not IsNumeric(src.Cells(r, 2).Value2) Then
                Err.Raise vbObjectError + 654, "MergeFolder", "Sales must be numeric."
            End If
        Next r
        If nextRow + lastRow - 2 > dst.Rows.Count Then Err.Raise vbObjectError + 655, "MergeFolder", "Output exceeds the row limit."
        dst.Cells(nextRow, 1).Resize(lastRow - 1, 2).Value2 = src.Range("A2:B" & lastRow).Value2
        nextRow = nextRow + lastRow - 1
        source.Close SaveChanges:=False
        Set source = Nothing
    Next item
    MsgBox CStr(nextRow - 2) & " data rows. Review the unsaved Merged workbook."
CleanUp:
    On Error Resume Next
    Err.Clear
    If Not source Is Nothing Then source.Close SaveChanges:=False
    If Err.Number <> 0 Then closeFailure = True
    Err.Clear
    If Len(failure) > 0 And Not result Is Nothing Then result.Close SaveChanges:=False
    If Err.Number <> 0 Then closeFailure = True
    Application.AutomationSecurity = security
    Application.ScreenUpdating = updating
    Application.EnableEvents = events
    On Error GoTo 0
    If Len(failure) > 0 Then MsgBox "Stopped at " & CStr(item) & ": " & failure, vbExclamation
    If closeFailure Then MsgBox "A practice workbook could not close. Inspect open workbooks; do not save partial output.", vbExclamation
    Exit Sub
Failed:
    failure = CStr(Err.Number) & ": " & Err.Description
    Resume CleanUp
End Sub

UsedRange의 마지막 행까지 두 열을 검사합니다. 사용했던 서식 때문에 빈 행이 남아 있으면 Branch is required로 중단할 수 있습니다. 이때 빈 행을 무조건 삭제하는 대신 복사본에서 실제 입력 범위를 확인하세요. 원본의 수식, 서식, 추가 열을 복제하는 코드가 아니라 A:B의 현재 값을 옮기는 코드입니다.

숫자 문자열과 이미 열려 있는 입력 파일을 거르는 이유

다운로드 ZIP에 현재 본문과 맞춘 코드를 담았습니다. 첨부 MergePractice.bas와 입력 XLSX 두 개로 먼저 연습하세요. 이미 BAS를 가져온 사본이라면 해당 모듈의 이전 코드 전체를 교체합니다. 같은 이름의 MergeFolder를 두 군데에 추가하지 않습니다. Microsoft Excel에서 VBA를 컴파일하거나 실행한 검증은 아직 하지 않았습니다.

IsNumeric("100")은 참이므로 IsNumeric만 검사하면 텍스트 100도 통과할 수 있습니다. 그런데 셀 범위를 더하는 SUM은 텍스트 값을 제외합니다. 위 코드는 기존 오류 셀 검사를 먼저 유지하고, VarType으로 문자열·논리값을 거른 뒤 실제 빈칸과 비숫자를 검사합니다. 숫자처럼 보이는 문자열을 임의로 숫자로 바꾸어 통합하지 않습니다. 이는 Microsoft IsNumeric 설명과 SUM의 텍스트 처리를 함께 고려한 입력 계약입니다.

COUNT와 SUM의 위 범위는 결과 Merged!B2:B5입니다. 숫자 셀 개수 4와 지점 A·B·C·D별 값을 함께 확인하세요. 숫자 문자열이 결과에 남으면 행 수가 네 개여도 숫자 개수가 세 개가 되거나 합계가 달라질 수 있으므로 행 수만으로 성공을 판정하지 않습니다.

열린 파일 검사는 Workbooks.Open 전에 현재 Excel 인스턴스의 FullName을 대소문자 구분 없이 비교합니다. 네트워크 드라이브와 UNC 경로처럼 같은 파일을 다른 문자열로 가리키는 경우, 다른 Excel 인스턴스, 파일 선택 후 다른 프로그램이 여는 경쟁 상태까지 탐지하는 잠금 장치는 아닙니다. 실습 입력은 미리 저장하고 닫아 두는 것이 기본 전제입니다.

숫자 검사는 음수와 소수도 허용하며 날짜처럼 표시된 셀의 숫자 일련번호도 금액과 구분하지 못합니다. Value2가 날짜·통화 전용 자료형을 사용하지 않는 점을 고려하여 원본 열의 의미도 확인해야 합니다. 이번 변경은 정적 코드 검토와 가상 입력의 숫자·문자 합계 대조를 했으며, Microsoft Excel에서 VBA를 컴파일하거나 실행한 검증은 아닙니다.

4행과 합계 1,000을 따로 검산하기

합계만 같아도 서로 다른 행이 상쇄될 수 있으므로 개별 Branch도 확인합니다. 헤더 중복을 세거나 ID가 연속인지 검사하는 수식만으로 누락·중복이 없다고 결론 내리지 않습니다. 첨부의 XLSX 구조와 위 입력 합계는 Python으로 읽어 검산했습니다. Excel/VBA에서 매크로를 실행한 결과는 아직 확인하지 않았으므로 표는 실행 후 대조할 기대값입니다.

파일 하나가 잘못됐을 때의 중단과 복구

Data 시트 없음, 다른 헤더, 빈 Branch, 비숫자 Sales는 해당 파일에서 중단합니다. 성공한 파일만 모아 완성된 보고서처럼 남기지 않도록 부분 출력도 저장하지 않고 닫습니다. 닫기 자체가 실패하면 안내가 나오므로 열린 연습 통합 문서를 확인하고 부분 결과를 저장하지 않습니다.

복구는 오류가 표시된 입력 파일의 복사본에서 시트명·헤더·행을 고친 뒤 처음부터 다시 실행하는 순서입니다. 입력 파일을 저장하거나 삭제하는 자동 복구는 없습니다. 정리 구간에서는 실행 전의 화면 갱신·이벤트·매크로 보안 값으로 되돌리려 시도합니다. 이 코드는 설정 복원 오류를 별도로 알리지 않으므로 오류 후에는 각 상태를 직접 확인하세요.

두 열 병합의 범위와 30개 파일로 늘릴 때의 한계

암호·접근 권한·외부 연결·수식 재계산이 있는 업무 파일은 이 단순 입력 계약으로 검증되지 않았습니다. 숨긴 행도 검사 대상이며 필터에서 보이는 행만 합치는 기능은 없습니다. 30개로 늘리기 전에는 각 파일의 행 수와 예상 합계를 별도 표에 기록하여 전체 대조 기준을 만들어야 합니다.

이 예제의 근거와 다음 확인

Microsoft API 참고 문서: Dir 함수, Workbooks.Open. 이 문서들은 API 동작의 근거이며 첨부 매크로의 실행 인증은 아닙니다. 파일 오류 처리 분기는 On Error 가이드에서 별도 연습할 수 있습니다.

Dir는 파일 패턴에 맞는 이름을 조회하고 인수 없이 다시 호출하여 다음 파일을 읽습니다. Workbooks.Open의 ReadOnly 인수는 읽기 전용으로 여는 옵션입니다.

출처 원문 수집일: S1 2026-09-23; S2 2026-09-23.

이어서 연습할 글

통합 뒤 거래처별 출력으로 나누는 과정은 원본 행 번호까지 대조하는 거래처별 시트 분리 예제에서 이어집니다.

작성자 소개

최지원

엑셀·VBA·파이썬·AI 업무 자동화의 사용 절차와 실습 자료를 정리합니다.

WP 게시물 작성자의 글 보기