Remove old connections with VBA

[ServerDude]

HI,

I have seen multiple posts about removing old connections using VBA but they remove all connections. I have a sheet with 62000+ connections that have built up over time and would like to remove them and retain the 100 usable connections. Is there a way i can filter against the "Last Refreshed" criteria? The code i have used so far is:

Sub Remove()
Dim connection As WorkbookConnection
On Error Resume Next
For Each connection In ThisWorkbook.Connections
connection.Delete
Next
End Sub

 

[ServerDude]

Almost but no cigar!

Is there something missing from DateDiff?

 

[ServerDude]

Getting closer, after adding the "d" to ensure DateDiff works in days, i am now getting the following Compile error

 

[ServerDude]

Added End with and am back to an issue with the DateDiff command

 

[ServerDude]

I have the following amendment that works, it just deletes all connections,

Sub Remove()
    Dim conConnect As WorkbookConnection
    
    'On Error Resume Next
    
    For Each conConnect In ThisWorkbook.Connections
        With conConnect
            'Delete connection if older than 30 days
            If .Type = 2 Then 'xlConnectionTypeODBC = 2
                If DateDiff("d", xlExternal, Date) > 30 Then
                    conConnect.Delete
                End If
            End If
        End With
        Next conConnect

    'On Error Goto 0
End Sub

If i change the operator before the 30 then it does not delete any. When using the .ODBCConnection.RefreshDate it seems to be looking for extra information

 

[sijpie]

The strange thing is that in the earlier debug test you ran the ODBCConnection.RefreshDate property worked for a number of connectioons. But now it is playing up. Seems like a number of connections are type 2 (ODBC), but do not have a date.

would this work? An extra test to see if there is a date

Sub Remove()
    Dim conConnect As WorkbookConnection
    
    'On Error Resume Next
    
    For Each conConnect In ThisWorkbook.Connections
        With conConnect
            'Delete connection if older than 30 days
            If .Type = 2 Then 'xlConnectionTypeODBC = 2
                If .ODBCConnection.RefreshDate <> vbNullString Then
                    If DateDiff("d", .ODBCConnection.RefreshDate, Date) > 30 Then
                        conConnect.Delete
                    End If
                End If
            End If
        End With
        Next conConnect

    'On Error Goto 0
End Sub

 

[ServerDude]

The connections in the earlier debug were old connections without a date view-able in the Excel connections window. These are the connections i am looking to remove.

Is it possible to remove any connections without a date?

With the change this is the new error.

 

[sijpie]

If we could remove the connections without a date, then we could do the other one as well. THis one is really stubborn. So, lets first use an variant in between, and test if the variant contains a date before testing the date.

If it throws an error, please let me know which line. As variants are forgiving, I hope this time round it works...

Sub Remove()
    Dim conConnect As WorkbookConnection
    Dim vD as Variant
    
    'On Error Resume Next
    
    For Each conConnect In ThisWorkbook.Connections
        With conConnect
            'Delete connection if older than 30 days
            If .Type = 2 Then 'xlConnectionTypeODBC = 2
                vD = .ODBCConnection.RefreshDate
                If  IsDate(vD) Then
                    If DateDiff("d", vD, Date) > 30 Then
                        conConnect.Delete
                    End If
                End If
            End If
        End With
        Next conConnect

    'On Error Goto 0
End Sub

 

[ServerDude]

Hi

Apologies for the delay, i have been on site all day and just got back. I have pasted in the code and get a failure on the line in yellow.

 

[ServerDude]

I have the following amendment that works, it just deletes all connections,

Sub Remove()
    Dim conConnect As WorkbookConnection
   
    'On Error Resume Next
   
    For Each conConnect In ThisWorkbook.Connections
        With conConnect
            'Delete connection if older than 30 days
            If .Type = 2 Then 'xlConnectionTypeODBC = 2
                If DateDiff("d", xlExternal, Date) > 30 Then
                    conConnect.Delete
                End If
            End If
        End With
        Next conConnect

    'On Error Goto 0
End Sub

If i change the operator before the 30 then it does not delete any. When using the .ODBCConnection.RefreshDate it seems to be looking for extra information

Have you seen this post?

 

[sijpie]

So there are "ODBC connections" which still do not conform to the proerties they should have. What if we try to skip these? Does the following code delete any connections?

Sub Remove()
    Dim conConnect As WorkbookConnection
    Dim vD as Variant
    
    On Error GoTo NextCon
    
    For Each conConnect In ThisWorkbook.Connections
        With conConnect
            'Delete connection if older than 30 days
            If .Type = 2 Then 'xlConnectionTypeODBC = 2
                vD = .ODBCConnection.RefreshDate
                If  IsDate(vD) Then
                    If DateDiff("d", vD, Date) > 30 Then
                        conConnect.Delete
                    End If
                End If
NextCon:
            End If
        End With
        Next conConnect

    On Error Goto 0
End Sub