VBA: Update the source of an XML Map and refresh

[Ferdi24]

Hi, I have an XML map and I need to update the source and refresh.
The result is currently in a table. I usesd developer > source > XML Maps > Add and then dragged the elements into the sheet to create a table.

How do I do this via VBA? All the elements will be exactly the same.

So far I've tried

mYQuery = "http://example.myxml.com/feed.aspx?id=12
ThisWorkbook.XmlMaps("MyMap").Import (myQuery)

But this does not work properly. It updates some parts of the new data, but not all of it for some reason.

I have also tried

ActiveWorkbook.XmlMaps("MyMap").Delete
ActiveWorkbook.XmlMaps.Add(mQuery, "search").Name = "MyMap"

But then all the elements are not mapped and I don't know how to add the to the table.

Thanks for you help!

 

[RoryA]

Are you actually changing the source path, or just trying to refresh the map in Excel?

 

[Ferdi24]

Hi Rorya,

I am changing the source. The id at the end of the URL changes, which results in a very different set of data (but the elements/xml nodes will have exactly the same names).
http://example.myxml.com/feed.aspx?id=12

 

[RoryA]

OK. Can you give an example of what you meant by "It updates some parts of the new data, but not all of it for some reason."?

 

[Ferdi24]

Hi rorya,

I was just about to copy an example but now it works.
It might have been a temporary issue, but usually I don't believe in temporary issue. Logic is logic.
So would you confirm that generally

ThisWorkbook.XmlMaps("MyMap").Import (myQuery)

is the correct code to update the source of an XML map?

 

[RoryA]

Yes, as far as I am aware. Do let me know if the problem happens again though.