Remove old connections with VBA

S

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
 
Sort by date Sort by votes
The DataFeedConnection object is only defined if the connection's Type is a datafeed connection, therefore you need an extra check before accessing that object:

If .Type = xlConnectionTypeDATAFEED Then
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Hi John,

Thanks for the assistance. Not sure if i have placed this in the correct place as i am now getting "Variable not defined" error.

1574715601307.png


Thanks,

Jason
 
Upvote 0
It compiles for me. You also need the closing End If, if it was obvious:

VBA Code: 
Sub RemoveOldConnections()

    Dim conConnect As WorkbookConnection

    For Each conConnect In ThisWorkbook.Connections
        With conConnect
            'Delete connection if it was refreshed more than 30 days ago
            If .Type = xlConnectionTypeDATAFEED Then
                If .DataFeedConnection.RefreshDate < Date - 30 Then
                    .Delete
                End If
            End If
        End With
    Next conConnect
 
End Sub
Note - I don't know if DataFeedConnection is appropriate for your particular connections - that was sijpie's decision in Remove old connections with VBA. The run time error "438". "Object doesn't support this object or method" suggests there are connections other than DataFeedConnections, hence the need for the additional If...End If lines. There are several different types of connection within Workbook.Connections. You need to look at all the connection definitions via Data tab -> Connections -> Properties -> Definition to see the type of connections in your workbook, then code appropriately.

EDIT - what version of Excel are you using? Maybe xlConnectionTypeDATAFEED is not available in your version.
 
Last edited:
Upvote 0
Please run the following debug code. If it does not halt with an error it should print some output (hopefully dates) in the direct window left below the window where you have the code

VBA Code: 
Sub CheckWBC()
    Dim conConnect As WorkbookConnection
    
    On Error Resume Next '  ""
    
    For Each conConnect In ThisWorkbook.Connections
        With conConnect
              Debug.Print .Name
              Debug.Print .DataFeedConnection.RefreshDate 

        Next conConnect

   On Error Goto 0
End Sub

Let me know the results
 
Upvote 0
Hi John

still getting error on .Type line.

sijpie

Here is the output. they are the connection names not dates. Last night i spend many hours removing all the unused connections and it has made an amazing difference to the sheet performance. After running the sheet once this morning, it is now using a whole load of new connections again. All of the lower numbers have been created by the system this morning.

Connection28
Connection29
Connection3
Connection30
Connection31
Connection32
Connection33
Connection34
Connection35
Connection36
Connection37
Connection38
Connection39
Connection4
Connection40
Connection41
Connection42
Connection43
Connection44
Connection45
Connection46
Connection47
Connection48
Connection49
Connection5
Connection50
Connection51
Connection52
Connection53
Connection54
Connection55
Connection56
Connection57
Connection58
Connection59
Connection6
Connection60
Connection61
Connection62
Connection62723
Connection62724
Connection62725
Connection62726
Connection62727
Connection62728
Connection62729
Connection62730
Connection62731
Connection62732
Connection62733
Connection62734
Connection62735
Connection62736
Connection62737
Connection62738
Connection62739
Connection62740
Connection62741
Connection62742
Connection62743
Connection62744
Connection62745
Connection62746
Connection62747
Connection62748
Connection62749
Connection62750
Connection62751
Connection62752
Connection62753
Connection62754
Connection62755
Connection62756
Connection62757
Connection62758
Connection62759
Connection62760
Connection62761
Connection62762
Connection62763
Connection62764
Connection62765
Connection62766
Connection62767
Connection62768
Connection62769
Connection62770
Connection62771
Connection62772
Connection62773
Connection62774
Connection62775
Connection62776
Connection62777
Connection62778
Connection62779
Connection62780
Connection62781
Connection62782
Connection62783
Connection62784
Connection62785
Connection62786
Connection62787
Connection62788
Connection62789
Connection62790
Connection62791
Connection62792
Connection62793
Connection62794
Connection62795
Connection62796
Connection62797
Connection62798
Connection62799
Connection62800
Connection62801
Connection62802
Connection62803
Connection62804
Connection62805
Connection62806
Connection62807
Connection62808
Connection62809
Connection62810
Connection62811
Connection62812
Connection62813
Connection62814
Connection62815
Connection62816
Connection62817
Connection62818
Connection62819
Connection62820
Connection62821
Connection62822
Connection62823
Connection62824
Connection62825
Connection62826
Connection62827
Connection62828
Connection62829
Connection62830
Connection62831
Connection62832
Connection62833
Connection62834
Connection62835
Connection62836
Connection62837
Connection62838
Connection62839
Connection62840
Connection62841
Connection62842
Connection63
Connection64
Connection65
Connection66
Connection67
Connection68
Connection69
Connection7
Connection70
Connection71
Connection72
Connection73
Connection74
Connection75
Connection76
Connection77
Connection78
Connection79
Connection8
Connection80
Connection81
Connection82
Connection83
Connection84
Connection85
Connection86
Connection87
Connection88
Connection89
Connection9
Connection90
Connection91
Connection92
Connection93
Connection94
Connection95
Connection96
Connection97
Connection98
Connection99
 
Upvote 0
Having looked through the VBA i have noticed the SourceType is xlExternal.

This is the VBA i am using to update the pivot tables:
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;"))

I have got the debugging code to work by using the following:

Sub CheckWBC() 'used to check the output for debuging only. Dim conConnect As WorkbookConnection On Error Resume Next ' "" For Each conConnect In ThisWorkbook.Connections With conConnect Debug.Print .Name Debug.Print .ODBCConnection.RefreshDate End With Next conConnect On Error GoTo 0 End Sub

We get an output of:
26/11/2019 13:58:20
Connection489
26/11/2019 13:58:20
Connection49
Connection490
26/11/2019 13:58:21
Connection491
26/11/2019 13:58:21
Connection492
26/11/2019 13:58:22
Connection493
26/11/2019 13:58:22
Connection494
26/11/2019 13:58:23
Connection495
26/11/2019 13:58:23
Connection496
26/11/2019 13:58:23
Connection497
26/11/2019 13:58:24
Connection498
26/11/2019 13:58:24
Connection499
26/11/2019 13:58:24
Connection5
Connection50
Connection500
26/11/2019 13:58:24
Connection501
26/11/2019 13:58:25
Connection502
26/11/2019 13:58:26
Connection503
26/11/2019 13:58:26
Connection504
26/11/2019 13:58:27
Connection505
26/11/2019 13:58:27
Connection506
26/11/2019 13:58:27
Connection507
26/11/2019 13:58:27
Connection508
26/11/2019 13:58:28
Connection509
26/11/2019 13:58:28
Connection51
Connection510
26/11/2019 13:58:28
Connection511
26/11/2019 13:58:29
Connection512
26/11/2019 13:58:30
Connection513
26/11/2019 13:58:30
Connection514
26/11/2019 13:58:30
Connection515
26/11/2019 13:58:31
Connection516
26/11/2019 13:58:31
Connection517
26/11/2019 13:58:31
Connection518
26/11/2019 13:58:31
Connection519
26/11/2019 13:58:32
Connection52
Connection520
26/11/2019 13:58:32
Connection521
26/11/2019 13:58:32
Connection522
26/11/2019 13:58:33
Connection523
26/11/2019 13:58:34
Connection524
26/11/2019 13:58:34
Connection525
26/11/2019 13:58:34
Connection526
26/11/2019 13:58:35
Connection527
26/11/2019 13:58:35
Connection528
26/11/2019 13:58:35
Connection529
26/11/2019 13:58:35
Connection53
Connection530
26/11/2019 13:58:36
Connection531
26/11/2019 13:58:36
Connection532
26/11/2019 13:58:37
Connection533
26/11/2019 13:58:37
Connection534
26/11/2019 13:58:38
Connection535
26/11/2019 13:58:38
Connection536
26/11/2019 13:58:38
Connection537
26/11/2019 13:58:39
Connection538
26/11/2019 13:58:39
Connection539
26/11/2019 13:58:39
Connection54
Connection540
26/11/2019 13:58:39
Connection541
26/11/2019 13:58:40
Connection542
26/11/2019 13:58:41
Connection543
26/11/2019 13:58:41
Connection544
26/11/2019 13:58:41
Connection545
26/11/2019 13:58:42
Connection546
26/11/2019 13:58:42
Connection547
26/11/2019 13:58:42
Connection548
26/11/2019 13:58:42
Connection549
26/11/2019 13:58:43
Connection55
Connection550
26/11/2019 13:58:43
Connection551
26/11/2019 13:58:43
Connection552
26/11/2019 13:58:44
Connection553
26/11/2019 13:58:44
Connection554
26/11/2019 13:58:44
Connection555
26/11/2019 13:58:45
Connection556
26/11/2019 13:58:45
Connection557
26/11/2019 13:58:45
Connection558
26/11/2019 13:58:45
Connection559
26/11/2019 13:58:46
Connection56
Connection57
Connection58
Connection59
Connection6
Connection60
Connection61
Connection62
Connection63
Connection64
Connection65
Connection66
Connection67
Connection68
Connection69
Connection7
Connection70
Connection71
Connection72
Connection73
Connection74
Connection75
Connection76
Connection77
Connection78
Connection79
Connection8
Connection80
Connection81
Connection82
Connection83
Connection84
Connection85
Connection86
Connection87
Connection88
Connection89
Connection9
Connection90
Connection91
Connection92
Connection93
Connection94
Connection95
Connection96
Connection97
Connection98
Connection99
 
Upvote 0
Wonderful!

Good that you found it was an odbc connection.

So in this case the following should work:
VBA Code: 
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(.ODBCConnection.RefreshDate, Date) > 30 Then
                    conConnect.Delete
                End If
            End If
        Next conConnect
        
    'On Error Goto 0
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Can't Remove Connections and Queries
Replies
3
Views
178
Alex Blakenburg
A
D
  • Question
vba code to refresh a connection only query
Replies
1
Views
151
RoryA
RoryA
L
  • Solved
Code in excel VBA to refresh SAS tables instead of pivot tables
Replies
2
Views
166
Luigiportucale
L
A
  • Solved
Copy Excel Table to a new workbook by removing all connections
Replies
9
Views
732
alishern
A
C
  • Question
ole db CopyFromRecordset skipping cell values
Replies
0
Views
342
Clif McIrvin
C

Forum statistics

Threads
1,215,972
Messages
6,128,017
Members
449,414
Latest member
sameri

Share this page

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