Retrieving XML data by looping through a range in a column

[capnhud]

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:

Please Login or Register  to view this content.

[mccrimmon]

Hi,

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

Thanks

[capnhud]

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

but the xml is like the following:

[mccrimmon]

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

[mccrimmon]

Does Column A have the XML Link?

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

[capnhud]

Yes A2 has the XML Link

[mccrimmon]

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?

Please Login or Register  to view this content.

[capnhud]

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:

Please Login or Register  to view this content.

[mccrimmon]

Apologies.
I had a typo in my code.

Try again please.

[capnhud]

You may not know much about XML import but you got this to do what I wanted it to do. Thank you so very much.

[mccrimmon]

No worries.

Please mark thread as solved.

[capnhud]

No worries.

Please mark thread as solved.

Not sure how I missed not marking it as solved but the forum will not let me mark it as solved