엑셀 VBA 자동화 (26편): 파편화된 외부 파일들, 버튼 하나로 개별 시트에 똑똑하게 일괄 취합하기

엑셀 VBA 자동화 (26편): 파편화된 외부 파일들, 버튼 하나로 개별 시트에 똑똑하게 일괄 취합하기

사내 각 부서에서 취합된 수십 개의 지출결의서나 인사 고과 서류를 하나의 파일로 모아야 할 때가 있습니다. 데이터를 단순히 수직으로 길게 이어 붙이는 대신, 각 파일의 원본 형태와 독립성을 유지하면서 마스터 워크북 내에 '각각의 독립된 시트'로 분리 적재하고 시트 이름까지 파일명으로 자동 매칭하는 일괄 시트 제어 자동화 프로그램을 구현해 보겠습니다.

EXCEL VBA AUTOMATION

행정 조직이나 재무 부서에서 정기 정산이나 전사 취합 업무를 진행할 때, 분산된 파일들을 정리하는 작업은 매우 번거롭습니다. 한 시트에 모든 데이터를 합치면 서식이 무너지거나 데이터가 혼선될 우려가 있는 문서(예: 부서별 고유 예산 집행 내역서 등)는 마스터 엑셀 파일 안에 부서별 시트로 각각 깔끔하게 나누어 담는 것이 올바른 구조화 방식입니다. 이 작업을 사람이 손으로 직접 수행하려면 파일을 열고, 시트를 복사하고, 이름을 바꾸고 닫는 과정을 수십 번 반복해야 하지만, VBA의 Sheets.Move 및 Dir 함수 메커니즘을 활용하면 휴먼 에러 없이 완벽하게 전산화할 수 있습니다.

1. 자동화 프로그램을 탑재하기 위한 개발 환경 빌드 단계
취합본 시트들이 차곡차곡 쌓이게 될 메인 마스터 엑셀 파일을 하나 새로 개설합니다. 그 상태에서 키보드의 Alt + F11을 동시에 눌러 Visual Basic for Applications 가상 편집 창을 호출합니다. 편집기 상단 메뉴 레이어에서 [삽입(I)] - [모듈(M)]을 마우스 왼쪽 버튼으로 클릭하여 깨끗한 코드 입력 공간을 개설합니다. 새로 활성화된 하얀 창에 아래의 일괄 시트 정제 통합 코드를 오타 없이 그대로 복사하여 붙여넣어 줍니다.

Sub ImportFilesAsSheets()
    Dim folderPath As String, fileName As String
    Dim targetWB As Workbook, masterWB As Workbook
    Dim ws As Worksheet
    Dim sheetName As String

    Set masterWB = ThisWorkbook
    ' 1. 매크로 파일과 동일한 경로 내 '부서별자료' 폴더 지정 (끝에 \ 필수)
    folderPath = masterWB.Path & "\부서별자료\"

    ' 2. 폴더 내부의 첫 번째 일반 엑셀 확장자 파일 검색
    fileName = Dir(folderPath & "*.xlsx")

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' 3. 폴더 내에 읽어올 파일이 없을 때까지 순회 루프 가동
    Do While fileName <> ""
        ' 대상 외부 파일 개설 및 가상 로드
        Set targetWB = Workbooks.Open(folderPath & fileName)
        Set ws = targetWB.Sheets(1)

        ' 파일명에서 '.xlsx' 확장자를 제외한 순수 이름만 추출하여 시트명으로 정의
        sheetName = Left(fileName, Len(fileName) - 5)

        ' 대상 시트를 마스터 파일의 가장 맨 뒤 시트 이후로 복사 이적
        ws.Copy After:=masterWB.Sheets(masterWB.Sheets.Count)
        masterWB.Sheets(masterWB.Sheets.Count).Name = sheetName

        ' 가동된 외부 파일 저장 없이 닫기 및 다음 대상 파일 호출
        targetWB.Close SaveChanges:=False
        fileName = Dir()
    Loop

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "모든 부서별 파일이 개별 시트로 취합 완료되었습니다!", vbInformation
End Sub

2. 일괄 복사 및 시트 네이밍 알고리즘 해석
이 프로그램의 제어 핵심 구문은 ws.Copy After:=...에 있습니다. 엑셀 워크북 제어 문법에서 특정 워크시트 객체 뒤에 .Copy 명령을 내리고 목적지 좌표(After)를 지정하면, 엑셀은 파일을 물리적으로 복사하는 대신 해당 시트의 데이터 체계와 셀 스타일, 테두리 레이아웃을 그대로 유지한 채 마스터 파일의 마지막 슬롯으로 안전하게 이적시킵니다. 정산 파일명이 '경영지원팀_지출서.xlsx'인 경우 Left 함수와 Len 함수를 정밀 연산하여 뒤쪽 우측의 5글자(.xlsx)를 영구 절단하고, 순수한 파일 식별 명칭인 '경영지원팀_지출서'만 추출하여 새로 안착한 시트의 탭 이름(.Name)으로 대입 바인딩하는 스마트 순환 자동화 메커니즘입니다.

💡 실수를 방지하는 핵심 팁 및 주의사항

• 중복 시트 이름 오류 방지: 엑셀은 한 워크북 내에 완벽히 똑같은 이름의 시트 탭이 공존하는 것을 불허합니다. 만약 동일한 이름의 파일이 폴더 내에 이중 적재되어 있다면 매크로 가동 중 에러가 발생하므로, 사전에 파일명이 부서명이나 날짜 코드로 명확히 분리되어 있는지 검증이 필요합니다.
• DisplayAlerts 속성 제어: 소스코드 내부에서 DisplayAlerts = False 처리를 진행해 둠으로써, 시트가 복사되어 넘어올 때 윈도우 창에 나타날 수 있는 수많은 시스템 경고문이나 참조 알림창을 완전 차단하여 프로그램의 가동 연속성을 보장했습니다.
• 물리적 폴더 실재 여부 확인: 코드 8행의 masterWB.Path & "\부서별자료\"는 이 매크로 엑셀 파일이 저장된 동일한 폴더 공간 내부에 반드시 '부서별자료'라는 이름의 하위 폴더가 실제로 존재하고 있어야 정상 구동됩니다. 빈 문서 상태에서는 경로 인식이 안 되므로 가동 전 마스터 파일을 하드디스크에 먼저 저장해야 합니다.

수집 대상 외부 파일 구조 및 마스터 시트 전환 메커니즘

'부서별자료' 폴더 내 원본 파일 목록	대상 파일 내부 구조 (1번 장)	매크로 가동 시 이적 프로세스	마스터 통합 파일 최종 변환 형태
인사기획팀_정산서.xlsx	A1:F50 (급여 대장 테이블)	뒤쪽 .xlsx 제거 후 시트 복사	[인사기획팀_정산서] 독립 시트 생성
재무회계팀_정산서.xlsx	A1:G120 (지출 증빙 리스트)	마스터 최후방 시트 뒤로 토스	[재무회계팀_정산서] 독립 시트 생성
총무행정팀_정산서.xlsx	A1:D35 (자산 실사 목록)	디자인/글꼴 서식 원본 유지 이적	[총무행정팀_정산서] 독립 시트 생성

각 팀에서 개별적으로 전송해 온 수십 개의 정산 데이터를 하나의 마스터 대장으로 통합하는 작업은 행정 관리자에게 무척 지루하고 고된 단순 반복 공정입니다. 오늘 구현한 외부 파일 일괄 순회 시트 복사 매크로 기술을 실무 템플릿에 연결해 두면, 파일 개수에 상관없이 컴퓨터가 단 몇 초 만에 완벽하게 분리 정돈된 통합 요약 서류를 완성해 줍니다. 수작업 노가다 프로세스를 자동화 시스템에 완벽히 위임하고, 확보된 시간으로 데이터 분석의 가치를 높여보시기 바랍니다.