Loop thru and delete all ODBC connections

bubbapost

Board Regular
Joined
Mar 11, 2009
Messages
116
Hello,

I have a workbook that I created to import any table on our SQL server via ODBC and once I am done with table I completely clear the worksheet to it's original state (as if I just inserted a brand new sheet) and before I import another table, I would like to be able to loop through all the existing connections and delete them. I attempted this with the code below, however I don't really know how to work with the ODBC object model.

Code:
Sub DeleteConnections()
Dim Conn As ODBCConnection
For Each Conn In ThisWorkbook.Connections
     [COLOR=seagreen]'Not sure what to put here.[/COLOR]
Next Conn
End Sub

Please HELP!!!:confused:
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
This might get you started

Code:
Sub DeleteConnections()
On Error Resume Next
    Dim i
    For i = 1 To 5000
        ActiveWorkbook.Connections("connection" & i).delete
    Next i
End Sub

as they take a while i also had added

sheets("name").Range("B3") = i

before next i, so i could see how well they were being deleted, your connection name will need to be adjusted to suit yours
 
Upvote 0
The following will delete all connectionsl:

Code:
Sub DeleteConnections()
Dim i As Long
For i = 1 To ThisWorkbook.Connections.Count
     ThisWorkbook.Connections(i).Delete
Next i
End Sub
 
Last edited:
Upvote 0
Can someone assist by offering a way to specify which pivot table to delete the connection on? I'm refreshing multiple workbooks every day and want to preserve yesterdays report before refreshing the pivots. I'm thinking of copying (sheets1) to (sheets2) then renaming (sheets2) and deleting the connection to that pivot table. If someone suggests an improved method great, but a sample process to delete the specific connection would be awesome also. Thanks in advance.
 
Upvote 0
if you just want the data can you not automate the table selection, then copy paste the result to a fresh location (record it as a macro and tweak as appropriate)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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