Remove old connections with VBA

ServerDude

New Member
Joined
Mar 31, 2011
Messages
30
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
 
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.

Date-issue11.PNG
Date-issue12.PNG


VBA Code:
    '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;"))
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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?

Code:
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
 
Upvote 0
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.
Date-issue14.PNG
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
If the connections are created each time the macro is run, then why not delete all connections?
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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