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 요청을 보냅니다.
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 & _
"¤tPage=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를 조합하면 대량 주소 데이터의 우편번호를 매우 빠르게 자동화할 수 있습니다.
특히 부동산 데이터, 고객 주소록, 배송지 목록 관리 업무에서 활용도가 높습니다.
