Delete Queries in a workbook

[drop05]

Hi I am looking for a way to delete queries in a workbook.

I have code that creates a copy of sheets in the active workbook then putting them into a created workbook, but when creating that new workbook it brings over the queries too. Is there a way to not bring over the queries as well or delete the queries in that created workbook.

i use

thisworkbook.worksheets(sheet1).copy before:=ActiveWorkbook.Worksheets("Sheet1")

to create a sheet, i do this multiple times to do different sheets.

 

[johnny51981]

I use this to delete the queries when no longer needed.

Sub ClearQueries()

Dim pq As Object

Dim q As String

For Each pq In ThisWorkbook.Queries

q = pq

ActiveWorkbook.Queries(q).Delete

Next

End Sub

 

[drop05]

I use this

Sub ClearQueries()

Dim pq As Object

Dim q As String

For Each pq In ThisWorkbook.Queries

q = pq

ActiveWorkbook.Queries(q).Delete

Next

End Sub

I use this to delete the queries when no longer needed.

Sub ClearQueries()

Dim pq As Object

Dim q As String

For Each pq In ThisWorkbook.Queries

q = pq

ActiveWorkbook.Queries(q).Delete

Next

End Sub

is there anything to add if it is not finding a query in the activeworkbook.delete? getting a run time error where it cannot find a query by the name

 

[Skyybot]

Do you have other Objects in the Workbook? i.e. - ListBoxes, Pictures, etc. ?

 

[drop05]

Do you have other Objects in the Workbook? i.e. - ListBoxes, Pictures, etc. ?

no not that i am aware with when checking my queries and connections

 

[Alex Blakenburg]

is there anything to add if it is not finding a query in the activeworkbook.delete? getting a run time error where it cannot find a query by the name

In @johnny51981's code change the ThisWorkbook line to Activeworkbook.
Note: Make sure you are working on a copy of your workbook or have a backup.

For Each pq In ActiveWorkbook.Queries

 

[drop05]

I am still getting a run time error, would there be a way to just copy the sheet without the query? The table within the sheet is created by the query, is there a way to copy the values over and the format?


I assume the code i used in the post copies the whole sheet and the includes the query if it has a table created by a query.

 

[Alex Blakenburg]

I can see that @johnny51981's delete code doesn't seem to fully remove all the query links.
If you are confident that there are no queries in the "created workbook" that you need to keep, then try the code below.

You can probably copy all your sheets across first then once done run this

Sub ClearQueriesAndConnections()

    Dim wbCreated As Workbook
    Set wbCreated = ActiveWorkbook

    ' Delete all connections
    Dim cn As WorkbookConnection

    For Each cn In wbCreated.Connections
        cn.Delete
    Next cn

    ' Delete all Queries
    Dim pq As Object
    Dim q As String

    For Each pq In wbCreated.Queries
        q = pq
        wbCreated.Queries(q).Delete
    Next

End Sub