VBA Delete all Web Queries in a Worksheet

JStellato · Nov 9, 2010

Is there a way to delete all web queries within a worksheet/workbook using VBA?

Thanks

John_w · Nov 9, 2010

This deletes all web queries on the active sheet, leaving other types of query untouched.

Code:

Sub DWQ()
    Dim i As Integer
    For i = QueryTables.Count To 1 Step -1
        If InStr(QueryTables(i).Connection, "URL;") = 1 Then QueryTables(i).Delete
    Next
End Sub

JStellato · Nov 10, 2010

Hey, Thanks for the reply. I've been trying to get this code to work. When I first ran it, I got an object not defined, so I added ActiveSheet

Code:

    Dim i As Integer
    For i = [COLOR=Red]ActiveSheet.[/COLOR]QueryTables.Count To 1 Step -1
        If InStr(QueryTables(i).Connection, "URL;") = 1 Then QueryTables(i).Delete
    Next

However this resulted in a Runtime Error 13 type mismatch, I tried it on a new workbook and I got the same result. I've also tried this code:

Code:

  For i = 1 To ActiveSheet.QueryTables.Count
      ActiveSheet.QueryTables(i).Delete
    Next i

However this results in Run Time Error 9, Subscript out of range. Any ideas?

Thanks again

John_w · Nov 10, 2010

My code only works if placed in a sheet module. Try this instead in a standard module:

Code:

Sub DWQ()
    Dim i As Integer
    With ActiveSheet
        For i = .QueryTables.Count To 1 Step -1
            .QueryTables(i).Delete
        Next
    End With
End Sub

chuckchuckit · Jun 4, 2012

Glad I found this code that deletes query tables. Apparently the path to "each" query table is stored somewhere in the background of excel. So if you do a lot of queries, and then save the wrkbk, the file size gets larger and larger. It can get massive...

Simply deleting the query tables associated solves this increasing filesize problem. Thanks.

VBA Delete all Web Queries in a Worksheet

JStellato

Board Regular

Excel Facts

John_w

MrExcel MVP

JStellato

Board Regular

John_w

MrExcel MVP

chuckchuckit

Well-known Member

Similar threads

Forum statistics

Share this page

VBA Delete all Web Queries in a Worksheet

JStellato

Board Regular

Excel Facts

John_w

MrExcel MVP

JStellato

Board Regular

John_w

MrExcel MVP

chuckchuckit

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock