Deleting only data connections with certain names.

d0rian

Active Member
Joined
May 30, 2015
Messages
313
Office Version
  1. 365
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).

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

osXncyH.jpg
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi d0rian,

Try this:

Code:
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
 
Upvote 0
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)
 
Last edited:
Upvote 0
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):

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top