[기초방] VBA 100제 #92 [ 엑셀 데이터 병합 자동화 ]

VBA 코드를 활용한 엑셀 데이터 병합 자동화

  이번 포스팅에서는 두 개의 엑셀 파일을 기준으로 VBA 코드를 사용해 데이터를 자동으로 병합하는 방법을 소개하려고 한다. 

 

이 코드는 기준 문서(quoteFile)와 참고 문서(productFile)의 데이터를 비교하여 일치하는 상품 ID를 기반으로 정보를 자동으로 가져와 병합하는 방식이다. 엑셀 작업을 효율화하고 반복되는 수작업을 줄이는 데 활용할 수 있다.

 

---

코드 설명

아래는 코드의 각 부분을 이해하기 쉽게 분해한 설명이다.

1. 변수 설정 및 경로 지정

Dim quoteFile As Workbook
Dim productFile As Workbook
Dim productID As String
Dim wsQuote As Worksheet
Dim wsProduct As Worksheet
Dim rngAll As Range
Dim rngA As Range
Dim rngProduct As Range
Dim foundProduct As Range
Dim FnameBase As String, FnameRef As String

• quoteFile과 productFile: 두 엑셀 파일(기준 문서와 참고 문서)을 담을 변수이다.
• productID: 각 상품의 ID를 저장할 변수이다.
• FnameBase와 FnameRef: 기준 문서와 참고 문서의 경로 및 파일명을 지정하는 문자열 변수이다.

---

2. 파일 경로 설정 및 열기

FnameBase = ThisWorkbook.Path & "\" & "기준문서.xlsx"
FnameRef = ThisWorkbook.Path & "\" & "참고문서.xlsx"

Set quoteFile = Workbooks.Open(FnameBase)
Set productFile = Workbooks.Open(FnameRef)

Set wsQuote = quoteFile.Sheets(1)
Set wsProduct = productFile.Sheets(1)

• 기준문서.xlsx와 참고문서.xlsx 파일의 경로를 현재 매크로가 있는 경로 기준으로 설정한다.
• 두 파일을 열고 각 파일의 첫 번째 시트를 변수에 할당한다.

---

3. 데이터 범위 지정

Set rngAll = Range(wsQuote.[d4], wsQuote.[d4].End(4))
Set rngProduct = Range(wsProduct.[a2], wsProduct.[a2].End(4))

• rngAll: 기준 문서의 D4 셀부터 마지막 데이터 셀까지의 범위이다.
• rngProduct: 참고 문서의 A2 셀부터 마지막 데이터 셀까지의 범위이다.

---

4. 상품 ID 비교 및 데이터 복사

For Each rngA In rngAll
    productID = rngA
    Set foundProduct = rngProduct.Find(What:=productID, LookIn:=xlValues, LookAt:=xlWhole)

    If Not foundProduct Is Nothing Then
        rngA(1, 0) = rngProduct(1, 3) '거래유형
        rngA(1, 2) = rngProduct(1, 5) '상품명
        rngA(1, 3) = rngProduct(1, 10) '규격
        rngA(1, 4) = rngProduct(1, 13) '주문단위
        rngA(1, 8) = rngProduct(1, 11) '제조원ID
        rngA(1, 9) = rngProduct(1, 12) '제조원
        rngA(1, 10) = rngProduct(1, 15) '국가
    End If
Next rngA

 

• For Each 루프를 사용해 기준 문서의 모든 상품 ID를 순회한다.
• Find 함수를 통해 참고 문서에서 동일한 상품 ID를 찾는다.
• 일치하는 상품 ID가 발견되면 해당 상품의 상세 정보(거래유형, 상품명, 규격 등)를 기준 문서에 복사한다.

---

5. 파일 저장 및 종료

quoteFile.Save
quoteFile.Close
productFile.Close False

MsgBox "상품 ID를 기준으로 데이터를 병합했습니다.", vbInformation​

• 기준 문서를 저장하고 모든 파일을 닫는다.
• 데이터 병합이 완료되었다는 메시지 박스를 띄워 사용자에게 알린다.

 

---

활용 예시

• 상품 재고 관리: 새로운 재고 데이터를 기존 문서와 비교해 업데이트할 때 사용한다.
• 주문 관리: 주문 내역에 상품 정보를 자동으로 채워 넣을 때 활용한다.
• 비교 데이터 생성: 여러 데이터를 통합해 분석용 문서를 만들 때 유용하다.

---

Sub 기초방92()

    Dim quoteFile As Workbook
    Dim productFile As Workbook
    Dim productID As String
    Dim wsQuote As Worksheet
    Dim wsProduct As Worksheet
    Dim rngAll As Range
    Dim rngA As Range
    Dim rngProduct As Range
    Dim foundProduct As Range
    Dim FnameBase As String, FnameRef As String

    ' 기준 문서 및 참고 문서 경로 설정
    FnameBase = ThisWorkbook.Path & "\" & "기준문서.xlsx" ' 여기에 기본 파일 이름 입력
    FnameRef = ThisWorkbook.Path & "\" & "참고문서.xlsx" ' 여기에 참고 문서 이름 입력

    ' 기준 문서 및 참고 문서 열기
    Set quoteFile = Workbooks.Open(FnameBase)
    Set productFile = Workbooks.Open(FnameRef)

    ' 각 파일의 첫 번째 시트를 변수에 할당
    Set wsQuote = quoteFile.Sheets(1)
    Set wsProduct = productFile.Sheets(1)
      
    Set rngAll = Range(wsQuote.[d4], wsQuote.[d4].End(4))
    
    Set rngProduct = Range(wsProduct.[a2], wsProduct.[a2].End(4))
   
    For Each rngA In rngAll
        productID = rngA
        Set foundProduct = rngProduct.Find(What:=productID, LookIn:=xlValues, LookAt:=xlWhole)

        If Not foundProduct Is Nothing Then
            rngA(1, 0) = rngProduct(1, 3) '거래유형
            rngA(1, 2) = rngProduct(1, 5) '상품명
            rngA(1, 3) = rngProduct(1, 10) '규격
            rngA(1, 4) = rngProduct(1, 13) '주문단위
            rngA(1, 8) = rngProduct(1, 11)  '제조원ID
            rngA(1, 9) = rngProduct(1, 12)  '제조원
            rngA(1, 10) = rngProduct(1, 15) '국가
        End If
    Next rngA
   
    quoteFile.Save
    quoteFile.Close
    productFile.Close False

    MsgBox "상품 ID를 기준으로 데이터를 병합했습니다.", vbInformation
End Sub

 

 

기초방92.zip

0.06MB