[FIXED] How to modify the name of the connection that Excel shows in the interface in VBA?

Issue

This is the first question I create in the forum although I have been reading it for a while and finding solutions. It should be pretty straighforward but there is something I am missing.

I am using VBA to create connections in excel to a couple of URLs. The attribute ".name" of the connection does not seem to be working properly. It does create the connection with the desired name but the name of the connection shown in the List of Connections is just the standard "Conection" ("Conexión" in Spanish).

By using the macro recorder I got that the same ".name" can be used to modify the name. But it is still not working.

This is just the part of the code I am having problems with right now.

GroupURL, QueryString and DestinationRange are variables. An example of GroupURL is "Q35G10/".

With Worksheets(DestinationSheet).QueryTables.Add(Connection:=QueryString, _ 
                                                   Destination:=Range(DestinationRange))    
        'This line just does not work but I do not know what it is for.
        .CommandType = 0    
        'The name has a problematic final character that is deleted this way (Ex: "/").
        .Name = "Dump_" & Left(GroupURL, Len(GroupURL) - 1)  
        
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = True
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

This gave results like the image of the following link (Sorry for the blue, is covered stuff)

[

So I tried adding the following line at the end to modifiy the name again:

 Worksheets(DestinationSheet).Connections("Dump_" & Left(GroupURL, Len(GroupURL) - 1)).Name = "Prueba"

And it claims: "Error 438. The object does not allow this property or method."

Thanks

Solution

There are two different things:

• The QueryTable name, which is the name that is was creating.

    queryTables.Add(...) .name
  

• The connection name, which is the one Excel shows. Managed with

    ActiveWorkbook.Connections(i).Name
  

By default, Excel arranges the connections alphabetically, and just adds the name "Connection" & CorreltiveNumber. As you can see in the image whenever a connection is created. This is language sensitive to your application setup.

Connections Snapshot

As I am creating several connections at once, I created a new sub to create them and rename each one, one by one, given that the name is going to be "Connection#". To do this, this is the solution I came up with, which checks if the connection name is already in use and works for Spanish and English.

Private Sub Create_Connection(ConnectionSheet As String, QueryString As String, DestinationRange As String, QueryName As String, ConnectionName As String)

Dim ConnectionCount As Long, i As Long, ConnectionMarker As Long, NamelessConnectionCount As Long

'Create connection
    With Worksheets(ConnectionSheet).QueryTables.Add(Connection:=QueryString, Destination:=Range(DestinationRange))
    'If the connection already exists with the same name, excel adds ... "_#".

    'The Name of the Connection will be the value of the Origin Cell
    .Name = QueryName
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingAll
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = True
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

'Check how many deffault name connection exist
ConnectionCount = ActiveWorkbook.Connections.Count
NamelessConnectionCount = 0
For i = 1 To ConnectionCount

If Left(ActiveWorkbook.Connections(i).Name, 8) = "Conexión" Or _
    Left(ActiveWorkbook.Connections(i).Name, 10) = "Connection" Then
        ConnectionMarker = i
        NamelessConnectionCount = NamelessConnectionCount + 1
End If
Next

'Change the name of the connection to a controlled one.
    If NamelessConnectionCount = 1 Then
        For i = 1 To ConnectionCount
            If ActiveWorkbook.Connections(i).Name = ConnectionName Then
                    MsgBox "The desired connection name " & ConnectionName & " is already in use." & vbCrLf & _
                    "Connection created with deffault name.", vbOKOnly, "Create Connection Error"
                    GoTo Label
            End If
        Next
        
        With ActiveWorkbook.Connections(ConnectionMarker)
            .Name = ConnectionName
            .Description = ""
        End With
    Else
        MsgBox "Error creating connection. The number of Nameless connections is not 1" & vbCrLf & _
                "Connection created with deffault name.", vbOKOnly, "Create Connection Error"
    End If

'Update connection
    ActiveWorkbook.Connections(ConnectionName).Refresh

Label:
End Sub

Answered By - Luca

Answer Checked By - Mary Flores (PHPFixing Volunteer)