여러 엑셀 파일 하나로 통합하기

Step 1: 어떤 질문이었을까요?

1년치 자료로 엑셀 파일이 360개 정도 있고, 각 파일에는 시트가 하나씩, 그 시트에는 300줄 정도의 자료가 들어 있습니다. 이 파일들을 하나의 파일, 하나의 시트에 이어 붙여 모으는 방법이 없겠느냐는 질문이었습니다.

비슷한 질문으로, 엑셀 파일이 100여 개인데 피벗 테이블로 모으면 힘들지 않겠느냐, 매크로로 파일마다 =[Book1.xls]대구!R4C8 같은 링크 수식을 일일이 넣는 것은 번거로우니 다른 방법이 없겠느냐는 질문도 있었습니다.

Step 2: 여러 파일을 한 시트로 통합하기

파일이 100여 개나 되는데 이것을 일일이 '복사-붙여 넣기'해서 하나의 파일로 만든다거나 또는 위와 같이 일일이 하드 코딩을 해 주어야 한다면 날밤 다 새겠지요?

일단 폴더를 하나 만들고 병합하고자 하는 파일들을 옮겨 놓으신 다음 아래의 버튼을 클릭해 보세요. 대화 상자에서 Ctrl 키나 Shift 키를 누른 채 파일을 선택하면 여러 개의 파일을 동시에 선택할 수 있습니다.

몇 개의 파일이든 새로운 시트에 통합이 되지요?

Sub ConsolWorkbook()

    Dim varFileName As Variant
    Dim varTemp As Variant
    Dim shtSheet As Worksheet
    Dim wrkBook As Workbook
        Dim i As Long
    Dim rngTarget As Range
    Application.ScreenUpdating = False
    varFileName = Application.GetOpenFilename(filefilter:="Excel Files(*.xls),*.xls", Title:="작업파일을 모두 선택하세요", MultiSelect:=True)
    ' GetOpenFilename 메서드를 이용하여 열기 대화 상자를 표시하고 사용자가 선택한
    ' 파일 이름을 얻을 준비를 합니다.

    If TypeName(varFileName) = "Boolean" Then
        Application.ScreenUpdating = True
        Exit Sub
    End If
    Set shtSheet = Worksheets.Add
    shtSheet.Move after:=Sheets(Sheets.Count)
    ' 워크시트를 한장 삽입하고 워크북의 맨 마지막으로 이동시킵니다.

    For Each varTemp In varFileName
    ' 선택된 파일(들)에 대해 순환문을 실행합니다.

        Set wrkBook = Workbooks.Open(varTemp)
        Set rngTarget = shtSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        ' 선택된 파일을 열고, 현재 워크북의 마지막 시트(shtSheet) 중에서 데이터가 들어있는
        ' A열의 마지막 셀 바로 아래 Range를 rngTarget Range 오브젝트 변수로 지정합니다.
        ' 그래야 데이터를 계속 붙여 나갈 수 있겠지요?

        wrkBook.Worksheets(1).UsedRange.Copy rngTarget
        ' 편의상 선택된 파일의 첫번째 워크시트 내용만 복사를 하는 것으로 만들었습니다.
        ' 여러 시트의 내용을 복사하시려면 이 부분에 순환문을 한번 더 중첩하면 되겠지요?
        ' 이것은 여러분이 직접 해 보시기 바랍니다.

        Application.CutCopyMode = False
        wrkBook.Close savechanges:=False
        i = i + 1
    Next varTemp
    If i = 0 Then
        MsgBox "오류가 발생하였습니다"
        Application.ScreenUpdating = True
        GoTo ET
        ' 만약 비정상적인 형태의 파일이 선택되었다면 오류 메시지를 띄우고 에러 트래핑 구문을
        ' 실행합니다.

    End If
    Application.ScreenUpdating = True
    MsgBox "파일 통합 작업을 완료하였습니다", , "www.iExceller.com"
ET:

End Sub

GetOpenFilename 메서드에 MultiSelect:=True를 지정하면 여러 파일을 한꺼번에 선택할 수 있습니다. 선택된 파일을 하나씩 열어 데이터가 들어 있는 영역을 통합용 시트의 A열 마지막 데이터 아래에 복사하고 파일을 닫는 과정을 반복합니다. 편의상 각 파일의 첫 번째 워크시트만 복사하도록 만들었으며, 여러 시트를 복사하려면 순환문을 한 번 더 중첩하면 됩니다.

코드는 그다지 길지 않지만 조금만 응용력을 발휘하신다면 편리하게 사용하실 수 있지 않을까 싶습니다.

다음 시간에…

정리 — 파일 통합 핵심 코드

자주 묻는 질문 (FAQ)

마치며

코드는 길지 않지만 응용하기에 따라 반복적인 파일 취합 업무를 크게 줄일 수 있습니다.