VBA - update connection path for XML data

[ezykiwi]

Ive got a sheet where I have gone to the DATA table and linked in a remote xml file to bring the data into excel.. that works fine... and is displaying....

To change that data source i have to goto DATA and Connections and Modify it which is abit of a pain... so im wanting to be able to do it via VBA.... the problem is i have tried recording a macro to get the code but its not coming up with anything.

Im able to do it via xml mapping... but i want to be able to do it without the mapping *i could be dumb on this point but i dont use mapping.. i just pull in the xml into the s/s and then pivot the table of data

I have looked and stumbled over.... ActiveWorkbook.Connections("CONNECTION_NAME") but im not sure what if any code can be used in conjunction with that to change the connection .....

Any advice thoughts would be awesome.

Cheers

 

[Jerry Sullivan]

Hi ezykiwi,

Experimenting with the object model a bit, this seems to work..

Sub ChangeXML_DataSource()
 Dim tbl As ListObject
 
 Const sNewURL As String = "C:\TEST\SourceB.xml"
 
 '--edit to the location of your data table
 Set tbl = Sheets("Sheet1").Range("A1").ListObject
 
 With tbl.XmlMap.DataBinding
   .ClearSettings
   .LoadSettings sNewURL
   .Refresh
 End With

End Sub

I haven't worked with linked XML sources, so I'll caution that this might not be a good method.

 

[ezykiwi]

Awesome.. thanks for that..... thats working perfectly...... much appreciated