Excel VBA API Loop Query Return results in excel

Hi All

I have set up an API JSON query within an excel workbook. Currently the workbook references a post code (zip code), runs the API via MSXML2.XMLHTTP, converts the results via the json converter class and imports the results.

The API can only take one post code query at a time.

I would like to have a list of postcodes and and for the API to loop through all the postcodes returning results for all postcodes into one sheet.

Any ideas how to do this?

Code and sheet attached.

I've had to remove my API key.

Thanks

Simon

CODE

Public Sub apicall()
Dim http As Object, JSON As Object, i As Integer

  Dim webServiceURL As String
  Dim actionType As String
  Dim targetWord As String
  Dim actionType2 As String
  Dim targetWord2 As String
  Dim postcode As String
  Dim LR As Long
 
  postcode = Sheets("post codes").Range("A2")
webServiceURL = "https://epc.opendatacommunities.org/api/v1/domestic/search?postcode=" + postcode
  actionType = "Accept:"
  targetWord = "application/json"
  actionType2 = "Authorization:"
  targetWord2 = "Basic MYKEY"

Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", webServiceURL, False
    http.setRequestHeader actionType, targetWord
    http.setRequestHeader actionType2, targetWord2
    http.Send


Sheets("Results").Range("A2:h10000").Clear

Set JSON = ParseJson(http.ResponseText)
i = 2
For Each Item In JSON("rows")
Sheets("Results").Cells(i, 1).Value = Item("address")
Sheets("Results").Cells(i, 2).Value = Item("address1")
Sheets("Results").Cells(i, 3).Value = Item("address2")
Sheets("Results").Cells(i, 4).Value = Item("address3")
Sheets("Results").Cells(i, 5).Value = Item("postcode")
Sheets("Results").Cells(i, 6).Value = Item("current-energy-rating")
Sheets("Results").Cells(i, 7).Value = Item("current-energy-efficiency")
i = i + 1
Next

LR = ActiveSheet.UsedRange.Rows.Count



Range("h1").AutoFill Destination:=Range("h1:h" & LR)

MsgBox ("complete")
End Sub
pass-withOUT-key.xlsm