VBA – 여러 Excel 파일을 하나의 통합 문서로 합치기

이 튜토리얼에서는 VBA에서 여러 Excel 파일을 하나의 통합 문서로 합치는 방법을 보여 줍니다.

VBA를 사용하여 여러 통합 문서들을 하나의 통합 문서로 만드려면 다음과 같은 여러 단계를 따라야 합니다.

• 소스 데이터가 포함된 통합 문서, 즉 소스 파일드(들)을 선택해야 합니다.
• 데이터를 넣을 통합 문서(대상 파일)를 선택하거나 만들어야 합니다.
• 소스 파일에서 필요한 시트를 선택해야 합니다.
• 대상 파일에서 데이터를 어디에 배치할지 코드에 정의해야 합니다.

열려 있는 모든 통합 문서의 모든 시트를 새 통합 문서의 개별 시트에 합치기

아래 코드에서는 Excel이 열려 있는 파일을 반복하여 데이터를 새 통합 문서로 복사하므로 대상 파일들이 모두 열려 있어야 합니다. 이 코드는 개인용 매크로 통합 문서에 위치합니다.

이 파일들은 열려 있어야 하는 유일한 Excel 파일입니다.

Sub CombineMultipleFiles()
On Error GoTo eh
'필요한 객체들을 저장할 변수를 선언합니다.
   Dim wbDestination As Workbook
   Dim wbSource As Workbook
   Dim wsSource As Worksheet
   Dim wb As Workbook
   Dim sh As Worksheet
   Dim strSheetName As String
   Dim strDestName As String
'속도를 높이기 위해 화면 업데이트를 끕니다.
   Application.ScreenUpdating = False
'먼저 새 대상 통합 문서를 만듭니다.
   Set wbDestination = Workbooks.Add
'새 통합 문서의 이름을 가져와서 반복문에서 제외시킵니다.
   strDestName = wbDestination.Name
'이제 열려 있는 각 통합 문서를 반복하여 데이터를 가져옵니다 새 통합 문서와 개인용 매크로 통합 문서는 제외합니다.
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         Set wbSource = wb
         For Each sh In wbSource.Worksheets
            sh.Copy After:=Workbooks(strDestName).Sheets(1)
         Next sh
      End If
   Next wb
'이제 새 파일과 Personal 매크로 통합 문서를 제외한 열려 있는 모든 파일을 닫습니다.
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         wb.Close False
      End If
   Next wb

'대상 통합 문서에서 Sheet1 제거
   Application.DisplayAlerts = False
   Sheets("Sheet1").Delete
   Application.DisplayAlerts = True
'객체를 정리하여 메모리 비우기
   Set wbDestination = Nothing
   Set wbSource = Nothing
   Set wsSource = Nothing
   Set wb = Nothing
'화면 업데이트 켜기'
   Application.ScreenUpdating = False
Exit Sub
eh:
   MsgBox Err.Description
End Sub

매크로 보기를 클릭하여 Excel 화면에서 프로시저를 실행합니다.

이제 합쳐진 파일이 표시됩니다.

이 코드는 각 파일을 반복하여 새 파일에 시트를 복사했습니다. 파일에 두 개 이상의 시트가 있는 경우 비어있는 시트를 포함하여 모든 시트가 복사됩니다!

열려 있는 모든 통합 문서의 모든 시트를 새 통합 문서의 워크시트에 합치기

아래 프로시저는 열려 있는 모든 통합 문서에 있는 모든 시트의 데이터를 새 통합 문서에서 단일 워크시트에 합칩니다.

각 시트의 데이터는 워크시트에서 대상 시트의 마지막 행에 붙여 넣습니다

Sub CombineMultipleSheets()
On Error GoTo eh
'필요한 객체를 저장할 변수를 선언합니다.
   Dim wbDestination As Workbook
   Dim wbSource As Workbook
   Dim wsDestination As Worksheet
   Dim wb As Workbook
   Dim sh As Worksheet
   Dim strSheetName As String
   Dim strDestName As String
   Dim iRws As Integer
   Dim iCols As Integer
   Dim totRws As Integer
   Dim strEndRng As String
   Dim rngSource As Range
'속도를 높이기 위해 화면 업데이트를 끕니다.
   Application.ScreenUpdating = False
'먼저 데이터를 합칠 새 통합 문서를 만듭니다.
   Set wbDestination = Workbooks.Add
'아래 반복문에서 제외하기위해 새 통합문서의 이름을 가져옵니다.
   strDestName = wbDestination.Name
'이제 데이터를 가져오기 위해 열려 있는 각 통합 문서를 반복합니다.
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         Set wbSource = wb
         For Each sh In wbSource.Worksheets
'시트의 행과 열 수를 가져옵니다.
            sh.Activate
            ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
            iRws = ActiveCell.Row
            iCols = ActiveCell.Column
'시트에서 마지막 셀의 주소를 설정합니다.
            strEndRng = sh.Cells(iRws, iCols).Address
'복사할 소스의 범위를 설정합니다
            Set rngSource = sh.Range("A1:" & strEndRng)
'대상 시트에서 마지막 행을 찾습니다.
           wbDestination.Activate
           Set wsDestination = ActiveSheet
           wsDestination.Cells.SpecialCells(xlCellTypeLastCell).Select
           totRws = ActiveCell.Row
'데이터를 붙여넣기에 충분한 행이 남아 있는지 확인합니다.
           If totRws + rngSource.Rows.Count > wsDestination.Rows.Count Then
               MsgBox "There are not enough rows to place the data in the Consolidation worksheet."
               GoTo eh
           End If
'대상 시트의 마지막 행에서 1을 더해줍니다
           If totRws <> 1 Then totRws = totRws + 1
           rngSource.Copy Destination:=wsDestination.Range("A" & totRws)
      Next sh
   End If
   Next wb
'대상 파일과 개인용 매크로 통합문서를 제외한 나머지 열려있는 파일들을 닫습니다.
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         wb.Close False
      End If
   Next wb
'객체를 정리하여 메모리를 초기화 합니다.
   Set wbDestination = Nothing
   Set wbSource = Nothing
   Set wsDestination = Nothing
   Set rngSource = Nothing
   Set wb = Nothing
'프로시저가 완료되면 화면 업데이트를 켭니다
   Application.ScreenUpdating = False
Exit Sub
eh:
MsgBox Err.Description
End Sub

열려 있는 모든 통합 문서의 모든 시트를 활성 통합 문서의 워크시트에 합치기

열려 있는 다른 모든 통합 문서의 데이터를 현재 작업 중인 통합 문서로 가져오려면 아래 코드를 사용하면 됩니다.

Sub CombineMultipleSheetsToExisting()
   On Error GoTo eh
'필요한 객체를 저장할 변수를 선언합니다.
   Dim wbDestination As Workbook
   Dim wbSource As Workbook
   Dim wsDestination As Worksheet
   Dim wb As Workbook
   Dim sh As Worksheet
   Dim strSheetName As String
   Dim strDestName As String
   Dim iRws As Integer
   Dim iCols As Integer
   Dim totRws As Integer
   Dim rngEnd As String
   Dim rngSource As Range
'활성 통합 문서를 대상 통합 문서 객체로 설정합니다
   Set wbDestination = ActiveWorkbook
'활성 통합 문서의 이름을 가져옵니다.
   strDestName = wbDestination.Name
'속도를 높이기 위해 화면 업데이트를 끕니다.
   Application.ScreenUpdating = False
'먼저 활성 통합 문서에 대상 워크시트로 사용할 새로운 워크시트를 만듭니다.
   Application.DisplayAlerts = False
'시트가 존재하지 않아서 오류가 발생할 경우 다음 코드를 실행합니다
   On Error Resume Next
   ActiveWorkbook.Sheets("Consolidation").Delete
'오류처리를 재설정하여 오류 발생시 코드의 마지막부분으로 이동하도록 설정합니다
   On Error GoTo eh
   Application.DisplayAlerts = True
'통합문서에 새로운 시트를 추가합니다
   With ActiveWorkbook
      Set wsDestination = .Sheets.Add(After:=.Sheets(.Sheets.Count))
      wsDestination.Name = "Consolidation"
   End With
'이제 열려 있는 각 통합 문서를 반복하여 데이터를 가져옵니다.
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         Set wbSource = wb
            For Each sh In wbSource.Worksheets
'시트의 행 수를 가져옵니다.
               sh.Activate
               ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
               iRws = ActiveCell.Row
               iCols = ActiveCell.Column
               rngEnd = sh.Cells(iRws, iCols).Address
               Set rngSource = sh.Range("A1:" & rngEnd)
'대상 시트에서 마지막 행을 찾습니다
               wbDestination.Activate
               Set wsDestination = ActiveSheet
               wsDestination.Cells.SpecialCells(xlCellTypeLastCell).Select
               totRws = ActiveCell.Row
'데이터를 붙여넣기에 충분한 행이 있는지 확인합니다.
               If totRws + rngSource.Rows.Count > wsDestination.Rows.Count Then
                  MsgBox "There are not enough rows to place the data in the Consolidation worksheet."
                  GoTo eh
               End If
'대상 시트의 마지막 행이 1이 아니라면, 복사할 위치를 정의하기 위해 마지막 행에 1을 더해줍니다.
               If totRws <> 1 Then totRws = totRws + 1
               rngSource.Copy Destination:=wsDestination.Range("A" & totRws)
           Next sh
         End If
   Next wb
'대상 통합문서와 개인용 매크로 통합문서를 제외한 나머지 열려있는 파일들을 닫습니다.
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         wb.Close False
      End If
   Next wb

'객체를 정리하여 메모리를 초기화합니다.
   Set wbDestination = Nothing
   Set wbSource = Nothing
   Set wsDestination = Nothing
   Set rngSource = Nothing
   Set wb = Nothing
'프로시저가 완료되면 화면 업데이트를 켭니다
   Application.ScreenUpdating = False
Exit Sub
eh:
MsgBox Err.Description
End Sub