Delete all external data connections at once

[maounde]

I have a file with hundreds of external data connections (to local CVS files imported). I need to remove these CVS files from my system. I'd rather break the connections from my XLSX file to these. On Excel 2016, I know I can break each connection one by one (Data > Queries & Connections > delete , confirm yes.... each time).

Is there a way to break these connections all at once, instead of one by one?

 

[Keebellah]

Hi there,
I have a macro that does it for my use, maybe the code will can help you too

Public Sub RemoveQueries(ByVal WKS As Worksheet)
Dim QRT                     As QueryTable
If WKS.QueryTables.Count > 0 Then
    For Each QRT In WKS.QueryTables
        QRT.Delete
    Next QRT
End If
End Sub

 

[maounde]

Thank you @Keebellah . Let me try this out.

 

[maounde]

@Keebellah I am trying to adapt your macro to my situation. I am not experienced in this at all and need more help.

I created a dummy macro and edited it a bit. I tried to run it on a test file and got this:

When I tried to debug, it shows this:

Any suggestion?

 

[Keebellah]

Dim WKS As WorkSheet

 

[Keebellah]

BTW the for you added is over kill and unnecessary, but dim the WKS you must add

 

[maounde]

So the code should be like this below?

Public Sub RemoveQueries(ByVal WKS As Worksheet)
Dim WKS As WorkSheet
If WKS.QueryTables.Count > 0 Then
QRT.Delete
Next QRT
End If
End Sub

 

[Keebellah]

You dim ALL variable, and probably due to my error you changed it
Here's how it should be

Public Sub RemoveQueries(ByVal WKS As Worksheet)
Dim WKS                     AS WorkSheet
Dim QRT                     As QueryTable
If WKS.QueryTables.Count > 0 Then
    For Each QRT In WKS.QueryTables
        QRT.Delete
    Next QRT
End If
End Sub