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

 

[sijpie]

Please use code tags when you post code. See example in red/blue below


I haven't tested the below code, but it should work. Please read the comments

Option Explicit

Sub Remove()
    Dim conConnect As WorkbookConnection
    'you had your variable named connection. That is quite dangerous as _
      there is a property called connections. A small typo and things go haywire. _
      I also always use at least on Capital letter in a variable, so I can see if _
      I make a typo: The vba editor should automatically modify the variable with _
      the capital letter.
    
    'On Error Resume Next '<<< why are you using this? _
      If you don't need it it can only lead to very difficult to spot bugs. _
      You will also have to reset its behaviour after the line where it was _
      needed  by using  "On Error Goto 0"
    
    For Each conConnect In ThisWorkbook.Connections
        With conConnect
            'Delete connection if older than 30 days
            If .DataFeedConnection.RefreshDate < (Date - 30) Then
                conConnect.Delete
            End If
        Next conConnect
End Sub

 

[ServerDude]

Hi and thanks fro your reply.

I have copied in and am getting a compile error or "Next without For"

Thanks in advance.

Jason

 

[Yongle]

End With was missing

    For Each conConnect In ThisWorkbook.Connections
        With conConnect
            'Delete connection if older than 30 days
            If .DataFeedConnection.RefreshDate < (Date - 30) Then
                conConnect.Delete
            End If
        End With          
    Next conConnect

 

[sijpie]

Thanks yongle for correcting that.

 

[ServerDude]

Hi Guys,

I am now getting an error or the object is not supported when using the date - 30. I have also tried to replace the date with an actual date but it still fails.

I am running Excel 2010. Is the above code compatible?

I have noticed that in your script above the Date - 30 is colour coded, 30 in red, on my script it is in black.

Thanks,

Jason

 

[Yongle]

Try this

    Dim conConnect As WorkbookConnection, D As Date
    D = Date - 30
    For Each conConnect In ThisWorkbook.Connections
        With conConnect
            If .DataFeedConnection.RefreshDate < D Then
                conConnect.Delete
            End If
        End With
    Next conConnect

 

[ServerDude]

Hi Yongle,

Thanks again, but still have the same error run time error "438". "Object doesn't support this object or method"

 

[sijpie]

For date calculations apparently we need to use a function DateDiff

DateDiff("d",Date1,Date2) will give the number of days between the two dates. So we need to use:

        With conConnect
            ' Check if the date is more then 30 days ago
            If DateDiff("d",.DataFeedConnection.RefreshDate , Date) > 30 Then
                conConnect.Delete
            End If
        End With

 

[ServerDude]

I am really sorry to keep pestering,

still the same error. This is what i have.

Again, i am not seeing the correct colour format of the 30.

Thanks again.