How do I update an Excel Workbook Web Query Connection String using VBA?

Question

I'm trying to modify a Web Query connection string

using VBA, however all of the answers I've found online give solutions that require a QueryTable, but my workbook has no QueryTable for the reasons that this post explains. What I have is a list object with XML Map Properties.

I've tried using an External Data Range QueryTable,

but the formatting comes through with modified column headers in alphabetical order.

Ideally, I'd like to just modify the GUID on my existing Connection String (since they expire and need to be updated periodically) so that it'll retain the XML formatting from my source. If that's not possible and my only option is to utilize a QueryTable, then I'd like a way to format the results from the QueryTable so that the column headers don't include "/row/@".

Answer 1

I've found a working solution here: https://www.mrexcel.com/forum/excel-questions/750075-vba-update-connection-path-xml-data.html

Final working code:

Sub RefreshXML_Click()
Dim GetProjects As String: GetProjects = "http://api.aceproject.com/?fct=getprojects&guid=" & Cerberus.GUID & "&Filtercompletedproject=False&projecttemplate=0&assignedonly=True&format=xml"
With Sheets("GetProjects").ListObjects("Table1").XmlMap.DataBinding
    .ClearSettings
    .LoadSettings GetProjects
    .Refresh
End With
End Sub

The solution was to clear and then reload the DataBinding since it's a read only field that can't be edited. All the other solutions focus on trying to modify a Query Table (which won't exist when you use the import wizard)