엑셀 VBA로 도로명주소 우편번호 자동 조회하기 (Juso API 활용)

엑셀에 저장된 주소 데이터를 하나씩 검색하는 작업은 시간이 매우 많이 걸립니다. 이번 글에서는 행정안전부 주소기반산업지원서비스의 Juso API를 활용해, VBA에서 도로명주소 우편번호를 자동으로 가져오는 방법을 정리해보겠습니다.

이번 예제에서는 주소 정제 → API 호출 → 우편번호 자동 입력까지 전체 흐름을 VBA로 구현합니다.

Juso API 사이트

우편번호 검색 API는 아래 버튼을 통해 신청 후 사용할 수 있습니다.

Juso API 사이트 바로가기
TIP
API 사용 전 반드시 승인 키(confmKey)를 발급받아야 합니다.

1. 주소 데이터 정제하기

API 검색 정확도를 높이기 위해 괄호, 대괄호, 쉼표 이후 문자열 등을 제거합니다. 특히 "외 n필지" 같은 문구는 검색 실패 원인이 되는 경우가 많습니다. 
Option Explicit

Sub 주소정제복사()

    Dim 선택영역 As Range
    Dim 셀 As Range

    Dim 원본주소 As String
    Dim 정제주소 As String

    Dim 새열번호 As Long

    If TypeName(Selection) <> "Range" Then
        MsgBox "주소 열을 먼저 선택하세요.", vbExclamation
        Exit Sub
    End If

    If Selection.Columns.Count > 1 Then
        MsgBox "한 개 열만 선택하세요.", vbCritical
        Exit Sub
    End If

    Set 선택영역 = Selection

    선택영역.Columns(1).Offset(0, 1).EntireColumn.Insert

    새열번호 = 선택영역.Column + 1

    Cells(선택영역.Row, 새열번호).Value = "정제주소"

    For Each 셀 In 선택영역.Cells

        If 셀.Row > 선택영역.Row Then

            원본주소 = Trim(셀.Value)

            If 원본주소 <> "" Then

                정제주소 = 원본주소

                정제주소 = RemoveAfterChar(정제주소, "(")
                정제주소 = RemoveAfterChar(정제주소, "[")
                정제주소 = RemoveAfterChar(정제주소, ",")

                Dim p As Long

                p = InStr(정제주소, "외 ")

                If p > 0 Then
                    정제주소 = Left(정제주소, p - 1)
                End If

                Do While InStr(정제주소, "  ") > 0
                    정제주소 = Replace(정제주소, "  ", " ")
                Loop

                정제주소 = Trim(정제주소)

                Cells(셀.Row, 새열번호).Value = 정제주소

            End If

        End If

    Next 셀

    MsgBox "주소 정제 완료", vbInformation

End Sub
실행 결과
불필요한 괄호 및 추가 문자열이 제거된 정제 주소가 오른쪽 열에 자동 생성됩니다.

2. VBA에서 Juso API 호출하기

이제 정제된 주소를 기반으로 실제 우편번호를 조회합니다. VBA에서는 MSXML2.XMLHTTP 객체를 이용해 HTTP GET 요청을 보냅니다. 
url = "https://business.juso.go.kr/addrlink/addrLinkApi.do?" & _
      "confmKey=" & apiKey & _
      "&currentPage=1" & _
      "&countPerPage=1" & _
      "&keyword=" & WorksheetFunction.EncodeURL(주소) & _
      "&resultType=json"

Set http = CreateObject("MSXML2.XMLHTTP")

http.Open "GET", url, False
http.Send

결과 = http.responseText
실행 결과
API 응답 JSON 데이터가 문자열 형태로 반환됩니다.
TIP
WorksheetFunction.EncodeURL()을 사용해야 한글 주소가 정상 전송됩니다.

3. JSON 응답에서 우편번호 추출하기

반환된 JSON 데이터 안에는 zipNo 항목이 포함되어 있습니다. 문자열 함수로 간단하게 추출할 수 있습니다. 
시작위치 = InStr(결과, """zipNo"":""")

If 시작위치 > 0 Then

    시작위치 = 시작위치 + 9
    끝위치 = InStr(시작위치, 결과, """)

    우편번호 = Mid(결과, 시작위치, 끝위치 - 시작위치)

Else

    우편번호 = "검색실패"

End If
실행 결과
검색 성공 시 우편번호가 추출되어 엑셀 셀에 자동 입력됩니다.

4. 전체 자동화 매크로

아래 코드는 선택한 주소 영역 전체를 반복 처리하면서 우편번호를 자동 입력하는 완성형 VBA 예제입니다. 
Option Explicit

Sub 우편번호가져오기()

    Dim 선택영역 As Range
    Dim 셀 As Range

    Dim http As Object
    Dim url As String
    Dim 결과 As String

    Dim 시작위치 As Long
    Dim 끝위치 As Long

    Dim 우편번호 As String
    Dim 주소 As String

    Dim apiKey As String
    apiKey = "발급받은 API KEY 입력"

    Set 선택영역 = Selection

    선택영역.Columns(선택영역.Columns.Count).Offset(0, 1).EntireColumn.Insert

    선택영역.Cells(1, 선택영역.Columns.Count + 1).Value = "우편번호"

    For Each 셀 In 선택영역.Cells

        If 셀.Row > 선택영역.Row Then

            If Trim(셀.Value) <> "" Then

                주소 = CleanAddress(셀.Value)

                url = "https://business.juso.go.kr/addrlink/addrLinkApi.do?" & _
                      "confmKey=" & apiKey & _
                      "&currentPage=1" & _
                      "&countPerPage=1" & _
                      "&keyword=" & WorksheetFunction.EncodeURL(주소) & _
                      "&resultType=json"

                Set http = CreateObject("MSXML2.XMLHTTP")

                http.Open "GET", url, False
                http.Send

                결과 = http.responseText

                시작위치 = InStr(결과, """zipNo"":""")

                If 시작위치 > 0 Then

                    시작위치 = 시작위치 + 9
                    끝위치 = InStr(시작위치, 결과, """)

                    우편번호 = Mid(결과, 시작위치, 끝위치 - 시작위치)

                Else

                    우편번호 = "검색실패"

                End If

                셀.Offset(0, 1).Value = 우편번호

            End If

        End If

    Next 셀

    MsgBox "우편번호 입력 완료", vbInformation

End Sub
실행 결과
선택한 주소 목록 전체에 우편번호가 자동 입력됩니다.

마무리 정리

VBA와 Juso API를 조합하면 대량 주소 데이터의 우편번호를 매우 빠르게 자동화할 수 있습니다. 특히 부동산 데이터, 고객 주소록, 배송지 목록 관리 업무에서 활용도가 높습니다.

```
  • 주소 정제를 먼저 해야 검색 성공률이 올라갑니다.
  • MSXML2.XMLHTTP 객체로 API 호출이 가능합니다.
  • JSON 응답의 zipNo 값을 추출해 우편번호를 자동 입력할 수 있습니다.
```
반응형