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

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

홈페이지 » 엑셀 VBA 파일 합치기 프로그램 만들기 :: 오빠두 퀵 VBA 4강

엑셀 VBA 파일 합치기 프로그램 만들기 :: 오빠두 퀵 VBA 4강

엑셀 파일 합치기 프로그램 목차 바로가기
영상강의


예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [엑셀VBA] 엑셀 파일합치기 모듈 (파일병합 프로그램)
    완성파일

엑셀 파일 합치기 프로그램 유저폼 추가하기

사용자가 원하는 파일을 쉽게 선택할 수 있도록 사용자 정의 유저폼을 추가합니다. 예제파일을 다운받은 뒤 개발도구 - Visual Basic 버튼을 클릭하거나, 단축키 ALT + F11을 눌러 매크로편집기를 실행합니다. 만약 개발도구 탭이 안 보일경우, 아래 개발도구 활성화 관련 링크를 참고하세요.

매크로 편집창이 실행되면, 상단의 [삽입] – [사용자 정의 폼]을 클릭하여 아래와 같이 사용자정의폼을 생성합니다.

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

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

엑셀 파일 선택창 모듈 추가하기 (Multiple_FileDialog)

매크로 편집창에서 [삽입] – [모듈]을 클릭하여 새로운 모듈을 생성합니다.

모듈의 이름은 "sub_FileSelection"으로 변경합니다.

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

모듈이 추가 되었으면, 퀵 VBA 1강에서 다루었던 파일선택창 마스터코드를 복사하여 모듈 안에 붙여넣기합니다. 파일선택창 마스터코드에 대한 자세한 설명은 아래 퀵 VBA 1강 관련 링크를 참고하세요.

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

'병합할 파일 선택' 버튼 명령문 추가하기

유저폼에서 [ 병합 할 파일 선택 ] 버튼을 더블클릭합니다. 버튼을 더블클릭하면 btnSelect_Click 명령문이 추가됩니다.

아래 명령문을 복사한 뒤, btnSelect_Click 명령문 위에 덮어쓰기 합니다.

Private Sub btnSelect_Click()
 
Dim strFilePath As String
Dim varFilePaths As Variant: Dim varFilePath As Variant
 
' 파일 선택 창 명령문에서 선택된 파일경로를 strFilePath로 받아옵니다.
strFilePath = Multiple_FileDialog
 
varFilePaths = Split(strFilePath, ", ")
 
Me.lstWB.Clear
' 각 파일 경로를 리스트상자에 추가합니다.
For Each varFilePath In varFilePaths
    Me.lstWB.AddItem varFilePath
Next
 
End Sub

엑셀 파일 합치기 본 명령문 작성하기

유저폼에서 [ 선택 된 파일 병합 ] 버튼을 더블클릭합니다. 더블클릭하면 btnMerge_Click 명령문이 추가됩니다.

명령문 안에 아래 전체명령문을 복사하여 덮어쓰기하거나 단계별로 코드를 작성합니다.

1. 변수 설정하기

명령문에 사용 될 변수를 설정합니다. 이전 강의에서 다룬 시트합치기 명령문에서 사용했던 것과 동일한 방식으로 변수를 설정합니다.

엑셀 매크로 변수 설정 방법에 대한 자세한 설명은 아래 관련 VBA 기초강의에서 관련 포스트를 참고하세요.

Dim WB as Workbook 	' : 병합할 엑셀파일입니다.
Dim WS as Worksheet 	' : 병합할 엑셀파일에 작성된 각각의 워크 시트입니다.
Dim toWS as Worksheet 	' : 각 WS의 내용을 취합할 활성화된 워크 시트입니다.
Dim rng as Range 		' : 각 WS에서 취합될 범위입니다.
Dim i as Long 		' : 리스트상자에 선택된 엑셀파일들의 순번입니다.
Dim j as Long 		' : 복사한 rng를 toWS에 붙여넣기 할 행 번호입니다.
Dim endCol as Long 	' : rng의 넓이를 계산하기 위한 마지막 열번호입니다.
Dim endRow as Long 	' : rng의 넓이를 계산하기 위한 마지막 행번호입니다.
Dim strWS as String 	' : 이전 시트합치기 명령문에서만 사용된 변수이므로 지워도 무방합니다.
2. 오류 방지용 명령문 추가하기

만약 유저폼 리스트상자에 선택 된 파일이 없을 경우, 파일 합치기 명령문이 참조할 대상이 없으므로 해당 명령문은 오류를 반환하게 됩니다.

따라서 리스트상자의 선택된 값이 없을 경우, "파일을 선택하세요" 라는 안내 메시지를 띄운 뒤 명령문을 종료합니다.

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

리스트상자에 입력된 각각의 엑셀 파일을 실행하고 종료하는 명령문을 추가합니다.

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

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

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

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

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
6. 안내메세지 출력

파일 병합이 완료되었다는 안내메세지를 띄운 후, 유저폼을 종료합니다.

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

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

'// 스크린업데이트 및 파일 저장 알림 중단
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
'// ..................
'// 실행할 명령문 작성
'// ..................
 
'//스크린 업데이트 파일 저장 알림 활성화
Application.ScreenUpdating = True
Application.DisplayAlerts = True
8. 파일 병합하기 버튼에 사용된 전체 명령문
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
Application.DisplayAlerts = 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
Application.DisplayAlerts = True
 
End Sub

파일 합치기 유저폼 실행을 위한 단축키 만들기

유저폼을 실행하기 위한 위한 명령문을 추가합니다.

아래 명령문을 복사한 뒤, 기존에 생성하였던 Module1 붙여넣기합니다.

Sub Merge_Workbook()
 
frmWBSelect.Show
 
End Sub

다시 엑셀 시트로 돌아온 뒤, 화면 상단의 [개발도구] – [매크로] 를 클릭하면 '매크로' 창이 출력됩니다. 매크로 창의 목록에서 방금 전에 추가한 Merge_Workbook을 선택한 뒤, [옵션] 버튼을 클릭합니다.

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

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

엑셀 파일 합치기 매크로 완성 및 테스트

파일 합치기 매크로 작성이 완료되었습니다. 이제 단축키 Ctrl + Shift + B 키를 누르면 언제든지 원하는 파일과 각 파일의 특정 시트를 선택하여 내용을 병합할 수 있습니다.

엑셀 파일 합치기 프로그램 완성
파일 합치기 프로그램 만들기가 완료되었습니다.
4.9 15 투표
게시글평점
guest
29 댓글
Inline Feedbacks
모든 댓글 보기
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열을 병합하는 파일 A열부터… 더보기 »

Teddy
Teddy
2020년 5월 26일 11:14 오전
게시글평점 :
     

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

엑엑셀셀
엑엑셀셀
2020년 7월 2일 11:07 오전
게시글평점 :
     

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

류효정
류효정
2020년 7월 9일 5:34 오후
게시글평점 :
     

안녕하세요. 알려주신 방법을 이용하는데, 셀의 범위를 바꾸고 싶은데 뭘 바꾸면 될까요.... 셀의 열을 더 선택해서 가져오고 싶어요.

CPark
CPark
2020년 7월 29일 5:37 오후
게시글평점 :
     

안녕하세요 ~ 도움 잘 받고 있습니다 ^^
모든 파일의 첫번째 시트만 가져오려고 하면 어떻게 수정해야할까요?
답변 부탁드립니다 ~

dladnwns
dladnwns
2020년 8월 1일 10:28 오후
게시글평점 :
     

좋은 강의 감사드립니다~~

삽자루부대
삽자루부대
2020년 8월 20일 6:05 오후
게시글평점 :
     

예제파일이 쉬트합치기 입니다. 확인부탁드려요.

Hegon
Hegon
2020년 9월 23일 9:38 오전
게시글평점 :
     

좋은강의 감사합니다.

With WS 루프에서 복사한 내용을 기존에 존재하는 worksheet("WMS")에 붙여넣고 싶을어서

rng.Copy toWS부분을

rng.Copy Destination:=Worksheets("WMS").Cells(j, 39)로
고쳐봤는데 에러만 표시되네요.. 바쁜와중 대단히 죄송하지만, 좋은방법 있으면 알려주실 수 있으신지요..

chickenn
chickenn
2020년 10월 13일 6:19 오후
게시글평점 :
     

엑셀 여러개 하나로 합치기 완성파일로 바로 코드 복붙해서 쓰고 싶은데 작동안할까요?

ccs3027
ccs3027
2020년 10월 17일 3:58 오후
게시글평점 :
     

좋은 강의 감사합니다 🙂 제공해주신 코드를 살짝 바꾸어, 다음 행부터 복사가 아니라 다음 열부터 복사하게끔 하여 잘 사용하고 있습니다. 혹시 코드를 추가하여 매크로가 없는 새로운 xlsx 파일로 복사한 데이터를 저장하려고… 더보기 »

jdje89
jdje89
2021년 1월 11일 11:38 오전
게시글평점 :
     

안녕하세요 해당 강의가 업무에 큰도움이 되고있습니다. 해당 매크로를 수정하여 홍길동 010 ** ** ** - 1파일 홍길동 서울시 중구 -2 파일 두가지 파일을 합칠시 홍길동, 010 ***** ,서울시 중구 이런식으로… 더보기 »

sinbal
sinbal
2021년 3월 17일 6:32 오후
게시글평점 :
     

감사합니다. 열심히 보고 있습니다. 몇 가지 궁금한 점이 있어서 여쭤보겠습니다. 유저폼을 사용하지 않고도 매크로 버튼을 통해 실행하는 방법도 있을까요? 그리고 만약 다른 엑셀파일의 시트가 파일병합이 이루어진 엑셀파일의 시트에 있지 않는다면… 더보기 »

jieuny1221
jieuny1221
2021년 4월 28일 11:23 오후
게시글평점 :
     

안녕하세요! 열심히 공부하다가 업무에 활용을 해보려고하는데,, 파일 병합 & 시트 병합 VBA 사용할때, 원하는 내용만 가져올 수 명령문이 있을까요? 자료가공을 너무 많이 해야되서.. 병합하면서 원하는 내용만 가져 올수 있으면 업무… 더보기 »

사과나무
사과나무
2021년 11월 12일 6:02 오후
게시글평점 :
     

안녕하세요 강의보고 천천히 하나씩 잘 배워가고 있습니다. 덕분에 엑셀실력많이 늘은거 같습니다.(실무에도 많이 활용하고 있고요^^) 오늘 강의에서 본것을 현장 업무에서 응용하여 쓸려고하는데 With WS endCol = .Cells(1, .Columns.Count).End(xlToLeft).Column endRow = .Cells(.Rows.Count,… 더보기 »

Last edited 15 일 전 by 사과나무
29
0
여러분의 생각을 댓글로 남겨주세요.x