0

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

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Dracius
  • 79
  • 10
  • The connection should be retrievable with [`ThisWorkbook.Connections`](https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.connections) – Mathieu Guindon Apr 26 '19 at 20:12
  • That's part of the issue; the solutions I found for updating the ConnectionString and not a QueryTable, specify an OLEDB connection, but mine is an XMLMAP, and when I try to assign it as a WorkbookConnection, I get a plethora of errors. ![Object Errors](https://imgur.com/HQzdwEQ.png) – Dracius Apr 26 '19 at 20:24
  • That's quite essential information definitely worth including in your question ([edit]), as part of "what you've tried". Can you make it a PowerQuery connection? – Mathieu Guindon Apr 26 '19 at 20:33
  • I did include it in my original question. "What I have is a list object with XML Map Properties." – Dracius Apr 26 '19 at 20:37
  • I could try using a PowerQuery; does that mean there's no way to update the URL attached to the XMLMap? [Trying to read through this documentation atm](https://learn.microsoft.com/en-us/office/vba/api/excel.xmlmap.import). – Dracius Apr 26 '19 at 20:40

1 Answers1

0

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)

Dracius
  • 79
  • 10