VBA 코드를 활용한 엑셀 파일 병합 자동화하기

VBA 코드를 활용한 엑셀 파일 병합 자동화하기

엑셀을 사용하시는 분들이라면, 대부분 다양한 파일의 데이터를 통합해서 나름의 계산 및 분석 작업을 하실 일이 있습니다. 이런 작업을 수작업으로 진행하면 많은 시간과 노력이 들어갑니다. 하지만 마이크로소프트 엑셀에서는 이런 작업을 VBA 코드를 활용해서 자동화할 수 있습니다.

이번 포스팅에서는 VBA 코드를 활용한 엑셀 파일 병합 자동화 방법을 소개하려 합니다.

칼퇴키트 바로가기

칼퇴키트, 업무용 도구모음

 

1. 비쥬얼 베이직 언어(Visual Basic for Applications)란?

VBA는 대표적인 스크립트 언어 중 하나로, 마이크로소프트의 엑셀, 워드, 액세스 등과 같은 애플리케이션에서 사용할 수 있는 프로그래밍 언어입니다. VBA는 개발환경이 비쥬얼 베이직 언어 기반이어서 VB와 동일한 문법 규칙과 구조를 갖습니다.

---

2. 엑셀 파일 병합 자동화 방법

엑셀 파일 병합 자동화 방법 우선, 합칠 파일들이 위치한 폴더의 경로를 입력해주셔야 합니다. 아래 코드의 주소처럼 "파일 병합"이라는 폴더를 만드시기 바랍니다.

Dim folderPath As String
folderPath = "C:\Users\사용자명\Desktop\파일 병합"

다음으로, 병합된 파일의 이름을 정해주시는데, 현재 시간을 이름으로 설정하는 방법을 소개합니다.

 

Dim currentDateTime As String
currentDateTime = Format(Now(), "yyyy-MM-dd_hh-mm-ss")

위와 같이 작성하시면, "2021-09-08_10-13-51"과 같은 형식으로 파일 이름이 생성될 것입니다.

 

이제 합칠 엑셀 파일들이 위치한 폴더에서 엑셀 파일들의 이름을 가져와 배열로 저장합니다.

Dim excelFiles As Variant
excelFiles = GetExcelFilesInFolder(folderPath)

그리고, 가져온 배열을 바탕으로 엑셀 파일을 하나씩 열어서 병합 작업을 수행합니다. 

Dim mergedRange As Range
Set mergedRange = Nothing

For i = LBound(excelFiles) To UBound(excelFiles)
    Dim currentFilePath As String
    currentFilePath = folderPath & "\" & excelFiles(i)

    Dim sourceWorkbook As Workbook
    Set sourceWorkbook = Workbooks.Open(currentFilePath)

    If mergedRange Is Nothing Then
        Set mergedRange = sourceWorkbook.ActiveSheet.UsedRange
    Else
        Set mergedRange = Union(mergedRange, sourceWorkbook.ActiveSheet.UsedRange)
    End If

    sourceWorkbook.Close SaveChanges:=False
Next

위 코드에서는 엑셀 파일을 열어서 Union 함수를 이용하여 mergedRange로 데이터를 병합합니다. UsedRange은 해당 시트에서 사용중인 셀의 범위를 나타내며, 해당 범위를 mergedRange에 추가하고 다음 파일을 열어서 Union 함수로 유한 mergedRange에 추가하는 작업을 반복합니다.

 

마지막으로, 병합된 데이터를 저장하고 엑셀 파일을 닫습니다.

Dim destWorkbook As Workbook
Set destWorkbook = Workbooks.Add

mergedRange.Copy
destWorkbook.ActiveSheet.Paste

Dim saveFilePath As String
saveFilePath = folderPath & "\" & currentDateTime & "_병합된.xlsx"

destWorkbook.SaveAs saveFilePath

destWorkbook.Close SaveChanges:=False

위 코드에서는 mergedRange를 복사해서 새로운 엑셀 파일을 생성하고, 그 파일에 데이터를 붙여넣는 작업을 수행합니다. 이후, 저장할 파일 경로를 설정하고 파일을 저장하고, 엑셀 파일을 닫는 작업을 진행합니다.

---

3. 코드 전체

Sub Excel파일병합()
    Dim 폴더경로 As String
    폴더경로 = "C:\Users\사용자명\Desktop\파일 병합"
    
    Dim 현재시각 As String
    현재시각 = Format(Now(), "yyyy-MM-dd_hh-mm-ss")
    
    Dim 엑셀파일목록 As Variant
    엑셀파일목록 = 폴더내_엑셀파일목록(폴더경로)
    
    Dim 병합범위 As Range
    Set 병합범위 = Nothing
    
    For i = LBound(엑셀파일목록) To UBound(엑셀파일목록)
        Dim 현재파일경로 As String
        현재파일경로 = 폴더경로 & "\" & 엑셀파일목록(i)
        
        Dim 원본워크북 As Workbook
        Set 원본워크북 = Workbooks.Open(현재파일경로)
        
        If 병합범위 Is Nothing Then
            Set 병합범위 = 원본워크북.ActiveSheet.UsedRange
        Else
            Set 병합범위 = Union(병합범위, 원본워크북.ActiveSheet.UsedRange)
        End If
        
        원본워크북.Close SaveChanges:=False
    Next
    
    Dim 병합된워크북 As Workbook
    Set 병합된워크북 = Workbooks.Add
    
    병합범위.Copy 병합된워크북.ActiveSheet.Paste
    
    Dim 저장파일경로 As String
    저장파일경로 = 폴더경로 & "\" & 현재시각 & "_병합된.xlsx"
    
    병합된워크북.SaveAs 저장파일경로
    병합된워크북.Close SaveChanges:=False
End Sub

Function 폴더내_엑셀파일목록(ByVal 폴더경로 As String) As Variant
    Dim i As Integer
    Dim 파일명 As String
    Dim 파일배열() As String
    Dim 파일수 As Integer
    
    ' 폴더 내부 파일 확인 및 Excel 파일명을 배열에 추가.
    If Right(폴더경로, 1) <> "\" Then
        폴더경로 = 폴더경로 & "\"
    End If
    
    파일명 = Dir(폴더경로 & ".xls")
    While 파일명 <> ""
        If UCase(Right(파일명, 4)) = ".XLS" Or UCase(Right(파일명, 5)) = ".XLSX" Then
            파일수 = 파일수 + 1
            ReDim Preserve 파일배열(1 To 파일수)
            파일배열(파일수) = 파일명
        End If
        파일명 = Dir()
    Wend
    
    폴더내_엑셀파일목록 = 파일배열
End Function

---

4. 마치며

이번 포스팅에서는 엑셀 파일 병합 작업을 VBA 코드를 통해 자동화하는 방법에 대해 알아보았습니다. VBA를 사용하시면, 수십 개, 수백 개의 파일을 손쉽게 통합하실 수 있으며, 더 나아가서 다양한 엑셀 기능을 자동화하실 수 있습니다. VBA 코드를 활용한 자동화 작업은, 엑셀 사용자라면 반드시 익혀야 할 스킬 중 하나입니다.

 

 

포스팅이 도움이 되셨다면 구독, 공감, 댓글 부탁드려요!

행복한 하루 되세요!