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]

Hi,

Apologise again for the delay, have had meeting frenzies.

First pic contains the error, the second contains the line. No connections were deleted. I have also included the the VBA to create each pivot table. As you will notice, the connections is created using the Pivot table wizard. I believe this is why it is creating new connections every time and ignoring the old ones.

    'pivot2 - Counters
    Sheets(Sheets(x).Name).Range("AQ5").Select
    Sheets(Sheets(x).Name).PivotTableWizard SourceType:=xlExternal, SourceData:=Array( _
            "SELECT uthead.uh_account, uthead.uh_mref, utcnt.uu_counter, uthead.uh_oref, utcnt.uu_start, utcnt.uu_vprice, sname.sn_name, umach.um_type, utcnt.uu_finish, uu_finish-uu_start AS 'Vends'" & Chr(13) & "" & Chr(10) & "FROM sname sn" _
            , _
            "ame, umach umach, utcnt utcnt, uthead uthead" & Chr(13) & "" & Chr(10) & "WHERE utcnt.uu_mref = uthead.uh_mref AND utcnt.uu_date = uthead.uh_date AND sname.sn_account = uthead.uh_account AND uthead.uh_mref = umach.um_ref AND ((u" _
            , _
            "thead.uh_date>={d '" & Date1 & "'} And uthead.uh_date<={d '" & date2 & "'}) AND (uthead.uh_account<>'CYM001') AND (uthead.uh_oref='" & ActiveSheet.Name & "')AND (utcnt.uu_counter In ('98','99')))" _
            ), Connection:=Array(Array( _
            "ODBC;Driver={Microsoft Visual FoxPro Driver};UID=;;SourceDB=\\Dc01\Server VFP Static and Dynamic\Data\Cymraeg\COMP_C.DBC;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machi" _
            ), Array("ne;Null=Yes;Deleted=Yes;"))

 

[ServerDude]

Hi Sijpie,

Any thoughts?

 

[sijpie]

You're in a hurry.. I need to work as well in between!

I don't understand why the code ignores the on error. It should not have shown an error code.

Can you try and run the following, then post the contents of the direct window again?

Sub check()
    Dim conConnect As WorkbookConnection
    Dim vD As Variant
    Dim lC As Long
    
    On Error GoTo NextCon
    
    For Each conConnect In ThisWorkbook.Connections
        lC = lC + 1
        With conConnect
            Debug.Print lC & ": " & .Name & " - " & .Type
                vD = .ODBCConnection.RefreshDate
                If IsDate(vD) Then
                    Debug.Print vD
                End If
NextCon:
        End With
        Err = 0
    Next conConnect

    On Error GoTo 0
End Sub

 

[ServerDude]

Hi Sijpie,

Apologise, i fully understand you have to work, just a gentle prompt as i know emails slip down the list. I certainly do not expect you to prioritise my requests over your work demands.

I do appreciate all the help i have received here.

Output
1: Connection - 2
25/11/2019 15:46:11
2: Connection1 - 2
25/11/2019 15:46:13
3: Connection10 - 2
25/11/2019 15:46:17
4: Connection11 - 2
25/11/2019 15:46:17
5: Connection12 - 2
25/11/2019 15:46:18
6: Connection13 - 2
25/11/2019 15:46:18
7: Connection14 - 2
25/11/2019 15:46:19
8: Connection15 - 2
25/11/2019 15:46:19
9: Connection16 - 2
25/11/2019 15:46:20
10: Connection17 - 2
25/11/2019 15:46:20
11: Connection18 - 2
25/11/2019 15:46:20
12: Connection19 - 2
25/11/2019 15:46:20
13: Connection2 - 2
25/11/2019 15:46:14
14: Connection20 - 2
25/11/2019 15:46:20
15: Connection21 - 2
25/11/2019 15:46:21
16: Connection22 - 2
25/11/2019 15:46:21
17: Connection23 - 2
25/11/2019 15:46:21
18: Connection24 - 2
25/11/2019 15:46:22
19: Connection25 - 2
25/11/2019 15:46:22
20: Connection26 - 2
25/11/2019 15:46:23
21: Connection27 - 2
25/11/2019 15:46:23
22: Connection28 - 2
25/11/2019 15:46:23
23: Connection29 - 2
25/11/2019 15:46:23
24: Connection3 - 2
25/11/2019 15:46:14
25: Connection30 - 2
25/11/2019 15:46:24
26: Connection31 - 2
25/11/2019 15:46:24
27: Connection32 - 2
25/11/2019 15:46:25
28: Connection33 - 2
25/11/2019 15:46:26
29: Connection34 - 2
25/11/2019 15:46:26
30: Connection35 - 2
25/11/2019 15:46:27
31: Connection36 - 2
25/11/2019 15:46:27
32: Connection37 - 2
25/11/2019 15:46:27
33: Connection38 - 2
25/11/2019 15:46:27
34: Connection39 - 2
25/11/2019 15:46:27
35: Connection4 - 2
25/11/2019 15:46:15
36: Connection40 - 2
25/11/2019 15:46:28
37: Connection41 - 2
25/11/2019 15:46:28
38: Connection42 - 2
25/11/2019 15:46:29
39: Connection43 - 2
25/11/2019 15:46:30
40: Connection44 - 2
25/11/2019 15:46:30
41: Connection45 - 2
25/11/2019 15:46:30
42: Connection46 - 2
25/11/2019 15:46:31
43: Connection47 - 2
25/11/2019 15:46:31
44: Connection48 - 2
25/11/2019 15:46:31
45: Connection49 - 2
25/11/2019 15:46:32
46: Connection5 - 2
25/11/2019 15:46:15
47: Connection50 - 2
25/11/2019 15:46:32
48: Connection51 - 2
25/11/2019 15:46:32
49: Connection52 - 2
25/11/2019 15:46:33
50: Connection53 - 2
25/11/2019 15:46:34
51: Connection54 - 2
25/11/2019 15:46:34
52: Connection55 - 2
25/11/2019 15:46:34
53: Connection56 - 2
25/11/2019 15:46:35
54: Connection57 - 2
25/11/2019 15:46:35
55: Connection58 - 2
25/11/2019 15:46:35
56: Connection59 - 2
25/11/2019 15:46:35
57: Connection6 - 2
25/11/2019 15:46:15
58: Connection60 - 2
25/11/2019 15:46:36
59: Connection61 - 2
25/11/2019 15:46:36
60: Connection62 - 2
25/11/2019 15:46:37
61: Connection63 - 2
25/11/2019 15:46:37
62: Connection64 - 2
25/11/2019 15:46:38
63: Connection65 - 2
25/11/2019 15:46:38
64: Connection66 - 2
25/11/2019 15:46:39
65: Connection67 - 2
25/11/2019 15:46:39
66: Connection68 - 2
25/11/2019 15:46:39
67: Connection69 - 2
25/11/2019 15:46:39
68: Connection7 - 2
25/11/2019 15:46:16
69: Connection70 - 2
25/11/2019 15:46:39
70: Connection71 - 2
25/11/2019 15:46:40
71: Connection72 - 2
25/11/2019 15:46:41
72: Connection73 - 2
25/11/2019 15:46:41
73: Connection74 - 2
25/11/2019 15:46:42
74: Connection75 - 2
25/11/2019 15:46:42
75: Connection76 - 2
25/11/2019 15:46:42
76: Connection77 - 2
25/11/2019 15:46:43
77: Connection78 - 2
25/11/2019 15:46:43
78: Connection79 - 2
25/11/2019 15:46:43
79: Connection8 - 2
25/11/2019 15:46:16
80: Connection80 - 2
25/11/2019 15:46:43
81: Connection81 - 2
25/11/2019 15:46:44
82: Connection82 - 2
25/11/2019 15:46:45
83: Connection83 - 2
25/11/2019 15:46:45
84: Connection84 - 2
25/11/2019 15:46:45
85: Connection85 - 2
25/11/2019 15:46:46
86: Connection86 - 2
25/11/2019 15:46:46
87: Connection87 - 2
25/11/2019 15:46:46
88: Connection88 - 2
25/11/2019 15:46:46
89: Connection89 - 2
25/11/2019 15:46:46
90: Connection9 - 2
25/11/2019 15:46:16
91: Connection90 - 2
25/11/2019 15:46:47
92: Connection91 - 2
25/11/2019 15:46:47
93: Connection92 - 2
25/11/2019 15:46:48
94: Connection93 - 2
25/11/2019 15:46:48
95: Connection94 - 2
25/11/2019 15:46:48
96: Connection95 - 2
25/11/2019 15:46:49
97: Connection9532 - 2
98: Connection9533 - 2

The script did fall over on the same line as in the screen shot below.

 

[sijpie]

So it does process 97 connections correctly before it falls over on the 98th connection.
Connection 1-96 all have a refreshdate, connection 97 does not, but causes no issue. It will have triggered an error likely which was captured correctly.
Connection 98 does also not have a refreshdate, is also type 2, but causes an error that is not captured correctly. This I do not understand.
It also means I cannot program around such a connection.

But what I could do is say write an extra macro with which you can delete one specific connection. It would be a manual intensive task, but possibly only once.
So you would run through the remove macro (slightly altered). Then you check which connection has thrown the error. You run the 2nd macro to delete that specific connection and continue with the remove macro.

What do you think?

 

[ServerDude]

Hi,

I believe connection 98 is not refreshing as it was used on the last update. As the macro uses pivot table wizard as pasted above, the macro creates a new connection every time it is run or every time the program is updated, causing the bloated number of connections in the file.

The only problem i can see is that the connection number changes every time the report is run. A connections that is active now will be disused the next time the report is opened and run, hence the need to find another identifiable variable. The only other one i can see is the refreshed date.

 

[sijpie]

If the connections are created each time the macro is run, then why not delete all connections?