Deleting only data connections with certain names.

[d0rian]

I currently run the below, which deletes every Connection in my workbook. But I only want to delete those with names starting with the text "Connection". See the image below; at the bottom of that list of all of those names are a few that I created manually named "import_file", "finance", and a few others. I don't want to delete those. Only the ones that begin with the text "Connection" (with any number after it).

Sub Delete_all_data_connections()    Do While ActiveWorkbook.Connections.Count > 0
    ActiveWorkbook.Connections.Item(ActiveWorkbook.Connections.Count).Delete
    Loop
End Sub

 

[Trebor76]

Hi d0rian,

Try this:

Option Explicit
Sub Macro1()

    Dim lngCount As Long
    
    Application.ScreenUpdating = False
    
    For lngCount = 1 To ActiveWorkbook.Connections.Count
        If Left(StrConv(ActiveWorkbook.Connections.Item(lngCount), vbProperCase), 10) = "Connection" Then
            ActiveWorkbook.Connections.Item(lngCount).Delete
        End If
    Next lngCount
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[d0rian]

It generates a Run-time error '9': Subscript out of range error, and when I debug it highlights the If Left...line in the VBA editor.

Any thoughts?
(strangely, it seems to delete SOME of the Connection# connections before generating that error, but not all of them...)
(also, i didn't include the Option Explicit line since i wasn't sure what it did, but don't think that would be the cause of this problem)

 

[Trebor76]

Any thoughts?

Only that one or more of the connection names have become corrupt from previous attempts to delete them so now they're showing something like #REF! or the like. See if this does the job (which in no way tries the fix the problem but just stops the error message from appearing):

Option Explicit
Sub Macro2()

    Dim lngCount As Long
    
    Application.ScreenUpdating = False
    
    For lngCount = 1 To ActiveWorkbook.Connections.Count
        On Error Resume Next
            If Left(StrConv(ActiveWorkbook.Connections.Item(lngCount), vbProperCase), 10) = "Connection" Then
                ActiveWorkbook.Connections.Item(lngCount).Delete
            End If
        On Error GoTo 0
    Next lngCount
    
    Application.ScreenUpdating = True
    
End Sub

also, i didn't include the Option Explicit line since i wasn't sure what it did, but don't think that would be the cause of this problem

No, that's not causing the issue. Option Explicit forces you to declare all your variables - without it any undeclared variable(s) will be assigned as as a variant the most expensive in terms of memory.

Robert