Retrieving XML data by looping through a range in a column

I have a worksheet that has in one column links to different xml IDs which I can copy the link then choose Data> Get Data From External Sources paste the link in the File Name box and click open and then select ok for the dialogue box and then select the select that I want the information to appear in, but go through many of these in the many is time consuming.

I have a macro that begins to do what I am doing manually, but would like for the macro to essentially go down the column and repeat the process until it reaches a blank cell then stops.

Any assistance would be greatly appreciated?

Here is the process:

Sub retrieve()
'
' retrieve Macro
'

'
    Range("A2").Select
    Selection.Copy
    ActiveWorkbook.XmlImport URL:= _
        "http://domain.com/cgi-bin/site/xml/itemResponse.w?items=AS31" _
        , ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$B$2")
    Range("A3").Select
    Selection.Copy
    ActiveWorkbook.XmlImport URL:= _
        "http://domain.com/cgi-bin/site/xml/itemResponse.w?items=AS3X" _
        , ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$B$3")
    Range("A4").Select
    Selection.Copy
    ActiveWorkbook.XmlImport URL:= _
        "http://domain.com/cgi-bin/site/xml/itemResponse.w?items=AS4XZ _
        , ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$B$4")
End Sub

Hi,

Do you have a copy of the workbook and xml that you can share with us?

Thanks

it is company workbook so I am quite sure I cannot post it here

but the xml is like the following:

HTML Code: 

Can you not amend the workbook and XML to dummy data so we know what we are working with?

It is easy enough to write some code to loop through your workbook, however, need to know what the key is for retrieving the XML items.

Cheers

Does Column A have the XML Link?

Such as "http://domain.com/cgi-bin/site/xml/itemResponse.w?items=AS31"

I am using a different version of Excel from you and do not really know much about the XML Import, however, from looking at your code this should do what you are after?

Public Sub Retrieve()

    Dim lngRow As Long
    Dim strXML As String
    
    lngRow = 2
    
    Do While Cells(lngRow, 1) <> ""
        strXML = Cells(lngRow, 1)
        ActiveWorkbook.XmlImport URL:=strXML, ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$B$" & lngRow)
        lngRow = lngRow + 1
    Loop

End Sub

Thank You mccrimmon for your assistance

Yes I am using Excel 2007

the results of the macro produce

Run-time error '1004'
Method 'Range' of object '_Global' failed when I push debug the problem line is highlighted:

Public Sub Retrieve()

    Dim lngRow As Long
    Dim strXML As String
    
    lngRow = 2
    
    Do While Cells(lngRow, 1) <> ""
        strXML = Cells(lngRow, 1)
        ActiveWorkbook.XmlImport URL:=strXML, ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$B$:" & lngRow)
        lngRow = lngRow + 1
    Loop

End Sub

Apologies.
I had a typo in my code.

Try again please.

No worries.

Please mark thread as solved.