I'm trying to write Excel VBA code to delete a connection after running a TEXT QueryTables to import data from a CSV file. Import works fine but as it runs against a different daily CSV file there are a multitude of connections which won't be used in the future that I would like to delete. The issue I have is that it seems neither ActiveWorkbook nor Thisworkbook support the connection property on my version of Excel, i.e. Excel for Mac 2011 - Version 14.7.7 (170905). Neither does it appear in the MS documentation as an available ActiveWorkbook or ThisWorkbook property.
Attempted code is:
Dim conExternalData As WorkbookConnection
For Each conExternalData In ActiveWorkbook.Connections
conExternalData.Delete
Next conExternalData
However when defining .Connections property for ActiveWorkbook it does not appear in the drop down list of available properties. See image below. When code executed a 436 error occurs: "Object doesn't support the property or method".
Interesting Connections is also not shown as an option in the Excel Ribbon Data Tab.
Any suggestions on how to fix the problem or an alternative solution to achieve the same end would be most welcome.