excelvbaconnection-stringexcel-web-query

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


I'm trying to modify a Web Query connection string

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.

XML Map Properties

I've tried using an External Data Range QueryTable, External Data Range QueryTable

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

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/@".


Solution

  • 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)