엑셀 VBA 파일 합치기 모듈 :: 엑셀 파일병합 프로그램

엑셀 파일 합치기를 위한 VBA 마스터 코드의 동작원리와 파일병합 툴 제작을 위한 실전 응용법을 단계별로 알아봅니다.

홈페이지 » 엑셀 VBA 파일 합치기 모듈 :: 엑셀 파일병합 프로그램

엑셀 파일 합치기 프로그램 만들기 | 퀵 VBA 4강

예제파일E-Book 교재
첨부파일에 이상이 생겼을 경우, 1:1 문의하기로 연락주시면 신속히 해결해드리겠습니다. 

관련강의 살펴보기

엑셀 VBA 시트 합치기 시트 병합 프로그램 만들기 예제 Thmb
엑셀 VBA 시트 합치기 프로그램 :: 시트 병합 매크로 총정리
엑셀 시트 합치기 프로그램 목차 바로가기 강의 요약 예제파일 다운로드 영상강의 강의에 사용된 전체 명령문 사용자 정의 폼(유저폼) 추가하기 유저폼 ...

관련 기초내용 포스트 | by 오빠두엑셀

글을 발견하지 못했습니다.

1. 사용자 정의 폼 추가하기

사용자가 원하는 파일을 선택할 수 있도록 사용자정의폼을 추가합니다. 예제파일을 다운받은 후 단축키 ALT + F11을 눌러 매크로편집기를 실행합니다.

상단의 [삽입] – [사용자 정의 폼]을 클릭하여 아래와 같이 사용자정의폼을 생성합니다.

속성창이 안 보일 경우, 키보드 F4키를 누르거나 또는 상단의 [보기] – [속성 창]을 선택합니다. 도구상자가 안 보일 경우, 상단의 [보기] – [도구상자]를 선택하면 도구상자 창이 나타납니다.

엑셀 파일 합치기 사용자 정의폼 만들기
사용자에게 파일 합치기를 안내할 사용자정의폼을 생성합니다.

2. 파일선택창 모듈 추가하기

매크로 편집창에서 [삽입] – [모듈]을 클릭하여 새로운 모듈을 생성합니다. 모듈의 이름은 ‘sub_FileSelection’으로 변경합니다.

엑셀 VBA 모듈 이름 변경
새로운 모듈을 삽입한 뒤, 모듈의 이름을 변경합니다.

이전 퀵 VBA 1강에서 다루었던 파일선택창 마스터코드를 복사하여 붙여넣기합니다. 파일선택창 마스터코드에 대한 자세한 내용은 관련 포스트를 참고하세요.

엑셀 파일선택창 총정리 강의
엑셀 VBA 파일선택창 매크로 사용법 총정리 :: 엑셀 자동화 기초
이번 강의에서는 엑셀 VBA 자동화 구축에 꼭 필요한 파일선택창 모듈에 대해 알아봅니다. 파일선택창을 활용하면 특정 확장자를 가진 파일만 사용자가 직접 ...
Public Function Multiple_FileDialog(Optional Title As String = "파일을 선택하세요", Optional FilterName As String = "엑셀파일", _
Optional FilterExt As String = "*.xls; *.xlsx; *.xlsm", Optional InitialFolder As String = "", _
Optional InitialView As MsoFileDialogView = msoFileDialogViewList, Optional MultiSelection As Boolean = True) As String
 
Dim FDG As FileDialog
Dim Selected As Integer: Dim i As Integer
Dim ReturnStr As String
 
Set FDG = Application.FileDialog(msoFileDialogFilePicker)
 
With FDG
.Title = Title
.Filters.Add FilterName, FilterExt
.InitialView = InitialView
.InitialFileName = InitialFolder
.AllowMultiSelect = MultiSelection
Selected = .Show
 
If Selected = -1 Then
For i = 1 To FDG.SelectedItems.Count - 1
ReturnStr = ReturnStr & FDG.SelectedItems(i) & ", "
Next i
ReturnStr = ReturnStr & FDG.SelectedItems(.SelectedItems.Count)
 
Multiple_FileDialog = ReturnStr
ElseIf Selected = 0 Then
MsgBox "선택된 파일이 없으므로 프로그램을 종료합니다."
End
End If
 
End With
 
End Function

3. ‘병합할 파일 선택’ 버튼 명령문 작성

유저폼에서 ‘병합할 파일 선택’ 버튼을 더블클릭하여 클릭 이벤트 명령문을 작성합니다.

Private Sub btnSelect_Click()
 
Dim strFilePath As String
Dim varFilePaths As Variant: Dim varFilePath As Variant
 
strFilePath = Multiple_FileDialog
 
varFilePaths = Split(strFilePath, ", ")
 
Me.lstWB.Clear
 
For Each varFilePath In varFilePaths
    Me.lstWB.AddItem varFilePath
Next
 
End Sub

4. ‘선택된 파일 병합’ 버튼 명령문 작성

유저폼에서 ‘선택된 파일 병합’ 버튼을 더블클릭하여 클릭 이벤트 명령문을 작성합니다.

4-A. 변수설정

명령문에서 사용할 변수를 설정합니다. 이전 퀵VBA 3강, 시트합치기 명령문에서 사용했던 변수세트와 동일하게 변수를 생성합니다.

변수설정방법에 대한 자세한 내용은 기초강의에서 다룬 적이 있으니 관련 포스트를 참고하세요.

  • WB as Workbook : 병합할 엑셀파일입니다.
  • WS as Worksheet : 병합할 엑셀파일에 작성된 각각의 워크 시트입니다.
  • toWS as Worksheet : 각 WS의 내용을 취합할 활성화된 워크 시트입니다.
  • rng as Range : 각 WS에서 취합될 범위입니다.
  • i as Long : 리스트상자에 선택된 엑셀파일들의 순번입니다.
  • j as Long : 복사한 rng를 toWS에 붙여넣기 할 행 번호입니다.
  • endCol as Long : rng의 넓이를 계산하기 위한 마지막 열번호입니다.
  • endRow as Long : rng의 넓이를 계산하기 위한 마지막 행번호입니다.
  • strWS as String : 이전 시트합치기 명령문에서만 사용된 변수이므로 지워도 무방합니다.
엑셀 VBA 변수 알아보기 총정리 썸네일_R
엑셀 VBA 변수 알아보기 :: 자주 사용하는 7가지 변수 총정리
VBA 변수 총정리 목차 바로가기 예제파일 및 PPT 자료 다운로드 VBA 변수란 무엇인가요? VBA에서 변수는 꼭 지정해줘야 하나요? 변수 지정을 ...
4-B. 오류방지

유저폼의 리스트상자에 선택된 엑셀파일이 없이 ‘파일 병합’ 버튼을 눌러 명령문을 실행할 경우, 참조할 파일주소가 없으므로 오류가 발생하게 됩니다. 따라서 리스트상자에 선택된 항목이 없을 경우 ‘파일을 선택하세요’ 라는 안내 메시지를 띄워줍니다.

'// 오류방지
If Me.lstWB.ListCount = 0 Then
    MsgBox "병합할 파일을 선택하세요."
    Exit Sub
End If
4-C. 리스트상자에 선택된 엑셀파일을 하나씩 실행/종료하기

파일 선택하기 명령문을 통해 리스트상자에 입력된 각각의 엑셀파일들을 실행하고 종료하는 명령문을 작성합니다.

For i = 0 To Me.lstWB.ListCount - 1
    Set WB = Application.Workbooks.Open(Me.lstWB.List(i))
 
	‘// 각 엑셀파일에서 실행될 명령문 작성 
 
WB.Close
Next
4-D. 실행된 엑셀파일의 모든 워크시트의 이름 비교하기

실행된 엑셀파일의 모든 워크시트를 하나씩 돌아가며 이름을 비교합니다. IF함수와 LIKE 연산자를 이용하여 각 시트이름을 비교한 뒤, 만약 해당 워크시트의 이름이 우리가 원하는 조건을 만족할 경우 명령문을 실행하도록 코드를 작성합니다.

For Each WS In WB.Worksheets
        If WS.Name Like Me.txtFilter.Value & "*" Then
 
		‘// 해당 워크시트에서 실행될 명령문 작성
 
	  End If
Next
4-E. 선택된 시트 내용 합치기

이전 시트합치기 강의에서 사용했던 명령문을 그대로 사용하여 선택된 시트의 내용을 병합합니다.

With WS
    endCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    endRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set rng = .Range(.Cells(2, 1), .Cells(endRow, endCol))
    rng.Copy toWS.Cells(j, 1)
    j = j + rng.Rows.Count
End With
4-F. 안내메세지 출력

파일 병합이 완료되었다는 안내메세지를 띄운 후, 사용자 정의폼을 닫아줍니다.

'// 안내메세지
MsgBox "파일 병합이 완료 되었습니다."
Unload Me
4-G. 스크린 업데이트 중단/활성화 (매크로 동작속도 개선)

더욱 빠른 매크로 동작을 원할경우, Application.ScreenUpdating 속성을 활성/비활성화 하여 매크로 동작속도를 개선할 수 있습니다.

'// 스크린업데이트 중단
Application.ScreenUpdating = False
 
'// ..................
'// 실행할 명령문 작성
'// ..................
 
'//스크린 업데이트 활성화
Application.ScreenUpdating = True
4-H. 파일 병합하기 버튼에 사용된 전체 명령문
Private Sub btnMerge_Click()
 
Dim WB As Workbook
Dim WS As Worksheet: Dim toWS As Worksheet
Dim rng As Range
Dim i As Long: i = 0: Dim j As Long
Dim endCol As Long: Dim endRow As Long
Dim strWS As String
 
'// 스크린업데이트 중단
Application.ScreenUpdating = False
 
'// 오류방지
If Me.lstWB.ListCount = 0 Then
    MsgBox "병합할 파일을 선택하세요."
    Exit Sub
End If
 
'// 파일병합
Set toWS = ActiveSheet
j = toWS.Cells(toWS.Rows.Count, 1).End(xlUp).Row
 
For i = 0 To Me.lstWB.ListCount - 1
    Set WB = Application.Workbooks.Open(Me.lstWB.List(i))
    For Each WS In WB.Worksheets
        If WS.Name Like Me.txtFilter.Value & "*" Then
                With WS
                    endCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
                    endRow = .Cells(.Rows.Count, 1).End(xlUp).Row
                    Set rng = .Range(.Cells(2, 1), .Cells(endRow, endCol))
                    rng.Copy toWS.Cells(j, 1)
                    j = j + rng.Rows.Count
                End With
        End If
    Next
    WB.Close
Next
 
'// 안내메세지
MsgBox "파일 병합이 완료 되었습니다."
Unload Me
 
'//스크린 업데이트 활성화
Application.ScreenUpdating = True
 
End Sub

5. 유저폼 실행을 위한 단축키 생성

유저폼을 불러오기 위한 명령문을 작성합니다. 기존에 생성하였던 Module1 모듈에 아래의 명령문을 추가합니다.

Sub Merge_Workbook()
 
frmWBSelect.Show
 
End Sub

다시 엑셀 시트로 돌아온 뒤, 상단의 [개발도구] – [매크로] 를 선택합니다. 상단의 [개발도구]가 안 보일경우, 개발도구 활성화 관련 포스트를 참고하세요.

글을 발견하지 못했습니다.

[매크로]에서 우리가 방금 추가한 Merge_Workbook을 선택한 후, [옵션]을 클릭합니다.

엑셀 개발도구 매크로 옵션 변경
[개발도구] – [매크로] 를 클릭하여 방금 추가한 ‘Merge_Workbook’의 옵션을 선택합니다.
옵션에서 해당 매크로를 실행하기 위해 단축키를 Ctrl + Shift + B 키로 지정한 뒤 [확인] 버튼을 눌러 마무리합니다.

엑셀 매크로 단축키 설정
Merge_Workbook의 단축키를 Ctrl + Shift + B로 지정합니다.

6. 파일 합치기 매크로 작성 완료 및 테스트

축하합니다. 파일 합치기 매크로 작성이 완료되었습니다. 이제 단축키 Ctrl + Shift + B 키를 눌러 언제든 원하는 파일 및 그중에서도 원하는 시트만 선택하여 내용을 병합할 수 있습니다.

엑셀 파일 합치기 프로그램 완성
파일 합치기 프로그램 만들기가 완료되었습니다.

강의가 도움 되셨으면 좋아요와 댓글 부탁드려요! 여러분의 소중한 댓글은 강의제작에 큰 힘이 됩니다.
엑셀 공부는 오빠두엑셀과 함께하세요! #엑셀은오빠두

5 5 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
11 Comments
Inline Feedbacks
View all comments
TaylorJ
TaylorJ
2020년 3월 25일 12:04 오전
게시글평점 :
     

열심히 따라하고있습니다.
정말 좋은 강의 무료로 나누어주셔서 정말 감사합니다!

chicchick
chicchick
2020년 5월 17일 7:47 오후

게시글평점 :       안녕하세요! 유투브에서부터 잘 보고 사이트까지 들어와서 많이 배우고 있습니다. 그런데 문의드리고 싶은 내용이 있는데, 파일합치기 이후에 각 데이터의 가장 오른쪽에 새로운 컬럼을 만들어서 각 데이터의 출처 파일명도… 더보기 »

chicchick
chicchick
2020년 5월 18일 3:04 오후

빠른 답변 감사합니다! 바로 적용해보았는데.. 아쉽게도 제가 생각하는대로는 되지 않는군요. 1. Data의 출처가 아닌, Data를 취합하는 Sheet의 이름이 입력됩니다. 2. Sheet명이 아닌 파일명 입력을 하려고 합니다. 2번은 작은 문제이겠지만 1번이… 더보기 »

chicchick
chicchick
2020년 5월 18일 5:13 오후

오오! 해결되었네요 ㅎㅎㅎ! 정말 감사합니다 ㅎㅎ 큰 도움이 되었어요.

김정민
김정민
2020년 5월 19일 1:12 오후

게시글평점 :       안녕하세요, 강의 영상 및 자료 매우 유용하게 사용하고 있습니다. 감사합니다. 한가지 여쭤보고 싶은게, 불러온 파일들의 내용을 같은열에 쌓지 않고, 다른열에 순서대로 불러올수 있을까요? 예를들어 각 파일의 H열을… 더보기 »

Teddy
Teddy
2020년 5월 26일 11:14 오전

게시글평점 :       안녕하세요? 좋은 강의 정말 감사드립니다. 혹시 파일이 합쳐질 때 파일 별 내용이 다른 음영으로 칠해지게 할 수는 없을까요? 알려주신 부분은 이해가 잘 되는데, 응용이 힘든 것 같습니다.… 더보기 »

엑엑셀셀
엑엑셀셀
2020년 7월 2일 11:07 오전

게시글평점 :       안녕하세요. 올려주신 강의파일 너무도 잘 사용하고 있습니다. 그런데 파일병합시 다시 한번 파일병합을 하면 이전에 병합했던 맨 마지막 데이터에 새로운 병합파일이 덮어져서, 맨 마지막 데이터가 사라지는 현상이 있습니다.… 더보기 »

11
0
여러분의 생각을 댓글로 남겨주세요.x