【Excel VBA】郵便番号検索APIで住所を自動取得するサンプルコード

Sub ボタン1_Click()
    Dim objXMLHttp As Object, zipArr
    Dim yubinNo As Long
    Dim line As String
    Dim splitLine() As String
   
    Dim i As Long
    i = 2 '行番号
    
    Do While Cells(i, 1).Value <> ""
    '入力値からハイフンの削除
     yubinNo = Replace(Worksheets("Sheet1").Cells(i, 1).Value, "-", "")
    
     Set objXMLHttp = CreateObject("MSXML2.XMLHTTP")
        objXMLHttp.Open "GET", "http://zip.cgis.biz/csv/zip.php?zn=" & yubinNo, False
        objXMLHttp.Send
     
     line = Replace(objXMLHttp.responseText, vbLf, ",")    '改行削除
     line = Replace(line, """", "")    'クォート削除
     line = Replace(line, "none", "")    'noneの文字列削除(情報がない場合、noneのため)
     
     splitLine = Split(line, ",")    'CSVを配列へ格納
     
     Worksheets("Sheet1").Cells(i, 2).Value = splitLine(13) & splitLine(14) & splitLine(15) & splitLine(16)
     Worksheets("Sheet1").Cells(i, 3).Value = splitLine(9) & splitLine(10) & splitLine(11) & splitLine(12)
     
     i = i + 1
    Loop
    
End Sub