엑셀 VBA - XML Parsing(XML 6.0 기준) (4) (LoadXML - 2)

라. VBA 코드 작성

토지임야정보조회.xlsm

1.08MB

 

 

 

(1) VBA 코드

Option Explicit

Sub Get_URL_XML()
    'xml parsing을 위한 변수 설정
    Dim xmlHttp As New MSXML2.ServerXMLHTTP60
    Dim xDoc As MSXML2.DOMDocument60
    Dim xNodes As MSXML2.IXMLDOMNodeList, yNodes As MSXML2.IXMLDOMNodeList, zNodes As MSXML2.IXMLDOMNodeList
    Dim xNode As MSXML2.IXMLDOMNode, yNode As MSXML2.IXMLDOMNode, zNode As MSXML2.IXMLDOMNode
    
   
    Dim i As Integer, j As Integer, k As Integer
    Dim endRow As Integer, sanilban As Integer
    Dim search_area As Range
    
    Dim dongcode As String, bonbun As String, bubun As String
    Dim authKey As String, search_URL As String
    
    
    'A열에서 G열 지우기
    Range("C4:f20000").Clear

'    Call ClearImmediateWindow
    
    'xml doc 문서 생성
    Set xDoc = New MSXML2.DOMDocument60
    
    'vlookup함수의 2번째 인수인 찾을 범위 지정
    Sheets(1).Select
    Set search_area = Range("a2").CurrentRegion
    
    
    Sheets(2).Select
    authKey = Range("b1") '인증키
    endRow = Range("a" & Rows.Count).End(xlUp).Row '실행할 마지막행 설정
    
    '4행부터 A열의 맨 아래행까지 반복
    For i = 4 To endRow
    
        ' 행정구역명을 첫번째 시트에서 찾기
        If IsError(WorksheetFunction.VLookup(Range("a" & i), search_area, 2, 0)) Then
            MsgBox "행정구역명을 정확히 입력하세요."
        Else
            dongcode = WorksheetFunction.VLookup(Range("a" & i), search_area, 2, 0)
        End If
        
        ' 산인지 일반인지에 따라 토지(임야)대장구분과 본번, 부번을 구함
        If Left(Range("b" & i), 1) = "산" Then
            sanilban = 2 '산 지번
            
            '지번에 -이 있는 경우
            If InStr("-", Range("b" & i)) > 0 Then
                ' 본번은 지번에서 두번째 부터 하이픈 전까지인데,
                ' 앞에 0을 붙인 후 오른쪽 4개를 가져옴
                bonbun = Right("000" & Mid(Range("b" & i), 2, InStr("-", Range("b" & i)) - 2), 4)
                
                ' 부번은 하이픈 다음부터 끝까지임
                bubun = Right("000" & Mid(Range("b" & i), InStr("-", Range("b" & i)) + 1), 4)
            
            Else '지번에 하이픈이 없는 경우
                '본번은 지번에서 두번째 부터이고, 앞에 0을 붙인 후 오른쪽 4개를 가져옴
                bonbun = Right("000" & Mid(Range("b" & i), 2), 4)
                
                ' 부번은 0000임
                bubun = "0000"
            End If
        Else
            sanilban = 1 '일반 지번
            
            '지번에 하이픈이 있는 경우
            If InStr("-", Range("b" & i)) > 0 Then
                ' 본번은 지번에서 첫번째 부터 하이픈 전까지인데,
                ' 앞에 0을 붙인 후 오른쪽 4개를 가져옴
                bonbun = Right("000" & Left(Range("b" & i), InStr("-", Range("b" & i)) - 1), 4)
                
                ' 부번은 하이픈 다음부터 끝까지임
                bubun = Right("000" & Mid(Range("b" & i), InStr("-", Range("b" & i)) + 1), 4)
            
            Else '지번에 하이픈이 없는 경우
                ' 본번은 지번에서 첫번째 부터 끝까지임
                bonbun = Right("000" & Range("b" & i), 4)
                ' 부번은 0000
                bubun = "0000"
            End If
        End If
        
        search_URL = "http://apis.data.go.kr/1611000/nsdi/eios/LadfrlService/ladfrlList.xml?" & _
                        "pnu=" & dongcode & sanilban & bonbun & bubun & "&ServiceKey=" & authKey
        
        ' GET 방식 URL을 전송
        xmlHttp.Open "GET", search_URL, False
        xmlHttp.setRequestHeader "Content-Type", "text/xml"
        xmlHttp.send
    
        '검증 결과를 xDoc에 저장하고,노드(nodes)를 node 기준으로 parsing
        xDoc.LoadXML (xmlHttp.responseText)
        
        ' xNodes를 xDoc에서 fileds인 노드를 선택한 후 인덱스 0인 item의 자식노드들을 저장
        Set xNodes = xDoc.SelectNodes("fields").Item(0).ChildNodes '.Item(3).ChildNodes
    
        For Each xNode In xNodes
    '        Debug.Print ("xNode의 노드명: " & xNode.nodeName)
            
            'yNodes 설정
            Set yNodes = xNode.ChildNodes

            For Each yNode In yNodes
                Select Case yNode.nodeName
                    Case "lndcgrCodeNm"   '지목
                        Range("c" & i) = yNode.Text
                    Case "lndpclAr"       '면적
                        Range("d" & i) = yNode.Text
                        Range("d" & i).NumberFormatLocal = "#,##0.0"
                    Case "posesnSeCodeNm"  '소유구분
                        Range("e" & i) = yNode.Text
                    Case "ladFrtlScNm"       '축적구분
                        Range("f" & i) = "'" & yNode.Text
                    Case "lastUpdtDt"      '최종 갱신일
                        Range("g" & i) = yNode.Text
                End Select
                    
            Next
        Next
            

    Next

    Range("a3:f3").HorizontalAlignment = xlCenter
    Columns("c:g").AutoFit
End Sub

 

(2) 코드의 구성

아래와 같이 크게 7개로 구분되어 있는데, 

 ① XML 변수 및 일반 변수 선언

 ② 기존 내용 지우기

 ③ XML 문서 생성

 ④ 검색 URL 생성 : PNU 코드 만들기, 인증키를 받아 접속 URL과 연결

 ⑤ 검색 URL 실행하고, 검색 결과를 XML 문서에 저장

 ⑥ XML 문서에서 nodeName과 text 알아내서 엑셀에 입력하기

 ⑦ 가운데 정렬, 열 너비 자동 정렬

 

(3) 코드 설명

(가) 변수 선언

Dim xmlHttp As New MSXML2.ServerXMLHTTP60     Dim xDoc As MSXML2.DOMDocument60     Dim xNodes As MSXML2.IXMLDOMNodeList, yNodes As MSXML2.IXMLDOMNodeList     Dim zNodes As MSXML2.IXMLDOMNodeList     Dim xNode As MSXML2.IXMLDOMNode, yNode As MSXML2.IXMLDOMNode, zNode As MSXML2.IXMLDOMNode              Dim i As Integer, j As Integer, k As Integer     Dim endRow As Integer, sanilban As Integer     Dim search_area As Range          Dim dongcode As String, bonbun As String, bubun As String     Dim authKey As String, search_URL As String

- Dim xmlHttp As New MSXML2.ServerXMLHTTP60 : XML 파일을 읽을 때는 필요 없는 부분으로, HTTP 전송을 위한 변수 선언 부분입니다.

 

- Dim endRow As Integer, sanilban As Integer : endRow는 실행할 마지막 행을 저장하기 위한 변수이고, sanilban은 산과 일반지번을 저장하기 위한 변수로 1은 일반 지번, 2는 산 지번으로 하기 위해 정수 형식으로 선언

- Dim search_area As Range : Vlookup 함수의 두 번째 인수인 table_array를 저장하기 위한 변수로 범위 형식임

 

- Dim dongcode As String, bonbun As String, bubun As String : dongcode는 행정동에 해당하는 코드를 저장하고, bonbun은 본번, bubun은 부번을 저장하기 위한 변수입니다. 네 자리 수로 앞에 0이 있을 수 있기 때문에 문자 형식으로 정했습니다.

 

- Dim authKey As String, search_URL As String : authKey는 인증키를 저장하기 위한 변수, search_URL은 검색 URL을 저장하기 위한 변수입니다.

 

(나) 기존 내용 지우기

Range("C4:f20000").Clear

지목, 면적 등 기존 내용을 지우기 위한 코드로 넉넉하게 F열 2만행까지로 정했으며, Clear는 홈 탭 편집 그룹의 모두 지우기에 해당합니다.

 

(다) XML 문서 생성

Set xDoc = New MSXML2.DOMDocument60

XML Document를 생성하는 구문으로 파일을 읽을 때와 동일합니다.

 

 

(라) 검색 URL 생성 : PNU 코드 만들기, 인증키를 받아 접속 URL과 연결

 

(a) 행정동 코드 찾기

Sheets(1).Select     Set search_area = Range("a2").CurrentRegion

- Sheets(1).Select : 첫 번째 시트, 다시 말해 행정동과 해당 코드가 있는 시트를 선택합니다.

- Set search_area = Range("a2").CurrentRegion : 찾을 범위를 A2셀을 기준으로 한 현재 영역, 다시 말해 연속된 범위로 지정합니다.

 

Sheets(2).Select     authKey = Range("b1") '또는 여기에서 인증키를 입력하고 도구 - 프로젝트 속성에서 비밀번호 설정     endRow = Range("a" & Rows.Count).End(xlUp).Row '실행할 마지막행 설정     '4행부터 A열의 맨 아래행까지 반복     For i = 4 To endRow              ' 행정구역명을 첫번째 시트에서 찾기         If IsError(WorksheetFunction.VLookup(Range("a" & i), search_area, 2, 0)) Then             MsgBox "행정구역명을 정확히 입력하세요."         Else             dongcode = WorksheetFunction.VLookup(Range("a" & i), search_area, 2, 0)         End If

- Sheets(2).Select : 첫 번째 시트에서 두 번째 시트, 다시 말해 찾고자 하는 법정동과 지번이 있는 시트로 이동합니다.

- authKey = Range("b1") : B1셀에 있는 인증키를 authKey변수에 저장합니다. 또는 VB Editor 메뉴 도구 - 프로젝트 속성에서 비밀번호를 설정할 수도 있습니다.

- endRow = Range("a" & Rows.Count).End(xlUp).Row : A열의 총 행수, 다시 말해 A열 맨 아래에서 Ctrl + ↑키를 누르면 데이터가 있는 맨 아래 셀에 셀 포인터가 위치하는데, 그 셀의 행수를 endRow변수에 저장합니다.

 

- For i = 4 To endRow : 4행부터 데이터가 있는 마지막 행, endRow까지 반복 실행합니다.

 

- If IsError(WorksheetFunction.VLookup(Range("a" & i), search_area, 2, 0)) Then
      MsgBox "행정구역명을 정확히 입력하세요."
  Else
      dongcode = WorksheetFunction.VLookup(Range("a" & i), search_area, 2, 0)
  End If

 

입력된 법정동명을 첫번째 시트에서 찾는데 에러가 난 경우, 다시 말해 법정동이 없을 경우는 "행정구역명을 정확히 입력하세요."란 메시지를 표시하고,

아니면 법정동명에 해당하는 코드를 dongcode변수에 저장합니다.

 

(b) 토지(임야)대장 구분, 본번, 부번 구하기

 

㉮ 산 지번인 경우

If Left(Range("b" & i), 1) = "산" Then             sanilban = 2 '산 지번                          '지번에 -이 있는 경우             If InStr(Range("b" & i), "-") > 0 Then                 ' 본번은 지번에서 두번째 부터 하이픈 전까지인데,                 ' 앞에 0을 붙인 후 오른쪽 4개를 가져옴                 bonbun = Right("000" & Mid(Range("b" & i), 2, InStr(Range("b" & i), "-") - 2), 4)                                  ' 부번은 하이픈 다음부터 끝까지임                 bubun = Right("000" & Mid(Range("b" & i), InStr(Range("b" & i), "-") + 1), 4)                          Else '지번에 하이픈이 없는 경우                 '본번은 지번에서 두번째 부터이고, 앞에 0을 붙인 후 오른쪽 4개를 가져옴                 bonbun = Right("000" & Mid(Range("b" & i), 2), 4)                                  ' 부번은 0000임                 bubun = "0000"             End If

- If Left(Range("b" & i), 1) = "산" Then : B열 i행 셀의 지번 맨 앞 글자가 "산"인 경우

 

- sanilban = 2 : 토지(임야) 대장 구분은 2가 되며 이 값을 sanilban 변수에 저장합니다.

 

- If InStr(Range("b" & i), "-") > 0 Then : B열 i행 셀에 -(하이픈)이 있는 경우

 

-bonbun = Right("000" & Mid(Range("b" & i), 2, InStr(Range("b" & i), "-") - 2), 4) : 산 지번이므로 두 번째부터 하이픈 이전까지 지번을 가져온 후 앞에 0 3개를 붙인 다음 오른쪽 4개를 가져와 bonbun 변수에 저장합니다. 다시 말해 산35-2번지라면 00035가 되는데 오른쪽 4개를 가져오므로 0035가 됩니다.

 

- bubun = Right("000" & Mid(Range("b" & i), InStr(Range("b" & i), "-") + 1), 4) : 하이픈이 있는 다음부터 끝까지 지번을 가져와서 앞에 000을 붙인 후 마찬가지로 오른쪽 4개를 가져옵니다. 워크시트 Mid함수의 경우는 Mid(범위, 시작위치, 개수) 형식으로 입력하는데, VBA의 Mid함수는 범위와 시작위치만 지정하고 개수는 지정하지 않아도 됩니다.

 

- Else : 지번에 하이픈이 없는 경우입니다.

 

- bonbun = Right("000" & Mid(Range("b" & i), 2), 4) : 산 지번이므로 B열 i행의 지번에서 두번째부터 숫자를 가져와 000을 붙인 후 오른쪽 4개를 가져옵니다.

 

- bubun = "0000" : 하이픈이 없으므로 부번이 없고, 따라서, 0000을 bubun 변수에 저장합니다. 다시 말해 산35라면  2와 0035, 그리고 0000이 결합되므로 200350000이 됩니다.

 

 

㉯ 일반 지번인 경우

Else             sanilban = 1 '일반 지번                          '지번에 하이픈이 있는 경우             If InStr(Range("b" & i), "-") > 0 Then                 ' 본번은 지번에서 첫번째 부터 하이픈 전까지인데,                 ' 앞에 0을 붙인 후 오른쪽 4개를 가져옴                 bonbun = Right("000" & Left(Range("b" & i), InStr(Range("b" & i), "-") - 1), 4)                                  ' 부번은 하이픈 다음부터 끝까지임                 bubun = Right("000" & Mid(Range("b" & i), InStr(Range("b" & i), "-") + 1), 4)                          Else '지번에 하이픈이 없는 경우                 ' 본번은 지번에서 첫번째 부터 끝까지임                 bonbun = Right("000" & Range("b" & i), 4)                 ' 부번은 0000                 bubun = "0000"             End If         End If

일반 지번은 산이 없으므로 sanilban이 1이고, 산과는 달리 본번을 첫 번째부터 가져온다는 점만이 다릅니다.

 

(c) 검색 URL 만들기

search_URL = "http://apis.data.go.kr/1611000/nsdi/eios/LadfrlService/ladfrlList.xml?" & _                         "pnu=" & dongcode & sanilban & bonbun & bubun & "&ServiceKey=" & authKey

검색 URL은 접속 URL http://apis.data.go.kr/1611000/nsdi/eios/LadfrlService/ladfrlList.xml에 에 ?pnu=을 붙인 다음 pnu code를 붙이는데, pnu코드는 dongcode & sanilban & bonbun & bubun, 다시 말해 법정도코드에 토지(임야) 대장 구분, 본번과 부번을 결합한 것입니다.

 

그리고, &ServiceKey= 다음에 authKey(인증키)를 결합니다.

 

&가 큰 따옴표 안에 있는 것과 바깥에 있는 것이 있는데 큰 따옴표 안에 있는 것은 Get변수를 전달할 때 변수를 연결하기 위한 것이고, 바깥에 있는 것은 변수끼리 또는 문자와 변수를 결합하기 위한 결합 연산자로 다릅니다.

 

searh_URL은 http://apis.data.go.kr/1611000/nsdi/eios/LadfrlService/ladfrlList.xml?pnu=1114010300100310000&ServiceKey=R2... 식이 됩니다.

 

 

(마) 검색 URL 실행하고, 검색 결과를 XML 문서에 저장

' GET 방식 URL을 전송         xmlHttp.Open "GET", search_URL, False         xmlHttp.setRequestHeader "Content-Type", "text/xml"         xmlHttp.send              '응답 결과를 xDoc에 저장         xDoc.LoadXML (xmlHttp.responseText)

위 구문은 크게 URL을 전송하는 부분과 응답결과를 xDoc 문서에 저장하는 부분으로 나뉩니다. 

파일을 읽어서 저장할 때는 xDoc.Load (S_filename)라고 xDoc.Load라고 쓰는데, URL응답결과를 저장할 때는 xDoc.LoadXML (xmlHttp.responseText)이라고 쓰는 점이 다릅니다.

 

 

(바) XML 문서에서 nodeName과 text 알아내서 엑셀에 입력하기

(첫 번째 방법)

' xNodes를 xDoc에서 fileds인 노드를 선택한 후 인덱스 0인 item의 자식노드들을 저장         Set xNodes = xDoc.SelectNodes("fields")              For Each xNode In xNodes     '        Debug.Print ("xNode의 노드명: " & xNode.nodeName)                          'yNodes 설정             Set yNodes = xNode.ChildNodes             For Each yNode In yNodes                 Set zNodes = yNode.ChildNodes                 For Each zNode In zNodes                     Select Case zNode.nodeName                         Case "lndcgrCodeNm"   '지목                             Range("c" & i) = zNode.Text                         Case "lndpclAr"       '면적                             Range("d" & i) = zNode.Text                             Range("d" & i).NumberFormatLocal = "#,##0.0"                         Case "posesnSeCodeNm"  '소유구분                             Range("e" & i) = zNode.Text                         Case "ladFrtlScNm"       '축적구분                             Range("f" & i) = "'" & zNode.Text                         Case "lastUpdtDt"      '최종 갱신일                             Range("g" & i) = zNode.Text                     End Select                 Next             Next         Next     Next

 

응답결과가 아래와 같이 fields, ladfrlVOList, 그 아래 pnu 등으로 3단계로 되어 있으므로 for each문도 3단계로 구성했습니다.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<fields>
    <numOfRows>10</numOfRows>
    <pageNo>1</pageNo>
    <totalCount>1</totalCount>
    <ladfrlVOList>
        <pnu>1114010300100310000</pnu>
        <ldCode>1114010300</ldCode>
        <ldCodeNm>서울특별시 중구 태평로1가</ldCodeNm>
        <mnnmSlno>31-0</mnnmSlno>
        <regstrSeCode>1</regstrSeCode>
        <regstrSeCodeNm>토지대장</regstrSeCodeNm>
        <lndcgrCode>08</lndcgrCode>
        <lndcgrCodeNm>대</lndcgrCodeNm>
        <lndpclAr>12709.4</lndpclAr>
        <posesnSeCode>04</posesnSeCode>
        <posesnSeCodeNm>시, 도유지</posesnSeCodeNm>
        <cnrsPsnCo>0</cnrsPsnCo>
        <ladFrtlSc>06</ladFrtlSc>
        <ladFrtlScNm>1:600</ladFrtlScNm>
        <lastUpdtDt>2022-06-07</lastUpdtDt>
    </ladfrlVOList>
</fields>

 

- Set xNodes = xDoc.SelectNodes("fields") : 첫번째 노드 네임 fields에 해당하는 노드 들을 xNodes 변수에 저장합니다. 범위 지정할 때처럼 Set문을 사용합니다.

 

- Set yNodes = xNode.ChildNodes : yNodes는 xNode의 자식 노드들로 설정합니다. numOfRows, pageNo 등이 해당됩니다.

 

- Set zNodes = yNode.ChildNodes : zNodes는 yNode의 자식 노드들로 설정합니다.

 

- Select Case zNode.nodeName
         Case "lndcgrCodeNm"   '지목
               Range("c" & i) = zNode.Text

  zNode의 노드 네임이 lndcgrCodeNm인 경우 지목이므로 C열 i행에 입력합니다.

 

- Range("d" & i) = zNode.Text
  Range("d" & i).NumberFormatLocal = "#,##0.0"

 D열 i행에 면적을 입력하는데 쉼표를 입력하고 소수점이하 첫째 자리까지 입력합니다.

 

- Range("f" & i) = "'" & zNode.Text : F열 i행에 축적을 입력하는데 1:600으로 입력하면 소수로 입력돼서 앞에 '를 입력해서 문자 형식으로 입력되도록 했습니다.

 

(두 번째 방법)

아래와 같이 yNode까지만 설정한 후 SelectSingleNode안에 nodeName을 직접 입력하여 셀에 값을 입력할 수도 있습니다.

Set xNodes = xDoc.SelectNodes("fields")               For Each xNode In xNodes     '        Debug.Print ("xNode의 노드명: " & xNode.nodeName)                          'yNodes 설정             Set yNodes = xNode.SelectNodes("ladfrlVOList")                              For Each yNode In yNodes                 Range("c" & i) = yNode.SelectSingleNode("lndcgrCodeNm").Text                 Range("d" & i) = yNode.SelectSingleNode("lndpclAr").Text                 Range("e" & i) = yNode.SelectSingleNode("posesnSeCodeNm").Text                 Range("f" & i) = "'" & yNode.SelectSingleNode("ladFrtlScNm").Text                 Range("g" & i) = yNode.SelectSingleNode("lastUpdtDt").Text                                  Range("d" & i).NumberFormatLocal = "#,##0.0"             Next         Next

 

 

(세 번째 방법)

xNodes를 설정할 때 item과 selectNodes("ladfrlVOList")를 이용해 yNodes까지 설정해서 1단계로 끝낼 수 있습니다.

Set xNodes = xDoc.SelectNodes("fields").Item(0).SelectNodes("ladfrlVOList") For Each xNode In xNodes     Range("c" & i) = xNode.SelectSingleNode("lndcgrCodeNm").Text     Range("d" & i) = xNode.SelectSingleNode("lndpclAr").Text     Range("e" & i) = xNode.SelectSingleNode("posesnSeCodeNm").Text     Range("f" & i) = "'" & xNode.SelectSingleNode("ladFrtlScNm").Text     Range("g" & i) = xNode.SelectSingleNode("lastUpdtDt").Text     Range("d" & i).NumberFormatLocal = "#,##0.0" Next

 

(네 번째 방법)

xNodes를 설정할 때 zNodes까지 설정해서 1단계로 끝내는데, 이 때는 xNode.SelectSingleNode("lndcgrCodeNm")가 아니라 xNode.nodeName에 따라 Select Case문으로 처리해야 합니다.

Set xNodes = xDoc.SelectNodes("fields").Item(0).ChildNodes.Item(3).ChildNodes         For Each xNode In xNodes                     Select Case xNode.nodeName                         Case "lndcgrCodeNm"   '지목                             Range("c" & i) = xNode.Text                         Case "lndpclAr"       '면적                             Range("d" & i) = xNode.Text                             Range("d" & i).NumberFormatLocal = "#,##0.0"                         Case "posesnSeCodeNm"  '소유구분                             Range("e" & i) = xNode.Text                         Case "ladFrtlScNm"       '축적구분                             Range("f" & i) = "'" & xNode.Text                         Case "lastUpdtDt"      '최종 갱신일                             Range("g" & i) = xNode.Text                     End Select         Next

 

검색 결과는 아래와 같으며, 인증키는 지웠으므로 각자 발급받아 사용하기 바랍니다.