Changing queryTable name in 2007/2010 using VBA

[Lxocram]

Since Excel 2007 querytables are part of a Listobject.

I've been able to succesfully alter the CommandText and Connection property using VBA

For Each ws In ActiveWorkbook.Sheets
For Each lo In ws.ListObjects
If lo.SourceType = XlListObjectSourceType.xlSrcQuery Then
lo.QueryTable.PreserveColumnInfo = False
lo.QueryTable.PreserveFormatting = False
lo.QueryTable.Refresh BackgroundQuery:=False
lo.QueryTable.RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells
lo.QueryTable.Commandtext = Replace(lo.QueryTable.Commandtext,OldCommandText,NewCommandText)
lo.QueryTable.Connection=Replace(lo.QueryTable.Connection,OldConnectionText,NewConnectionText)


but when I try to alter the QueryTable.Name property I get an error
I can't even print the name
Debug.Print lo.QueryTable.Name does not work

I can print and change the Listobject name "External_Data_..." but I want to change the Connection name and description

http://office.microsoft.com/en-us/excel-help/connection-properties-HA010175443.aspx

I can do it via

Dim lngConn As Long
For lngConn = .Connections.Count To 1 Step -1
.Connections(lngConn).Name = Replace(.Connections(lngConn).Name, OldConnectionName, NewConnectionName)
Next lngConn
End With

But how do i match the activeworkbook.connection to the listobject.queryTable.Connection

 

[geboman]

Will this work for you?

Dim sh As Worksheet, LO As ListObject, QT As QueryTable

Set sh = ActiveSheet

Set QT = sh.ListObjects.Item(1).QueryTable

MsgBox QT.WorkbookConnection.name
​