|
The Delete Method
To delete a QueryTable, use the Delete method of the QueryTable object.
The QueryTable object represents on External Data table unlike QueryTables which
is a collection object that contains all the External Data tables. To
access the QueryTable object, use the Item method of the QueryTables collection,
like this
Sheets("Sheet1").QueryTables.Item("MyTable").Delete
"MyTable" is the name of the External Data table. You can also use the
index number, with 1 being the first QueryTable in the collection. Also, Item is
the default method for QueryTables, so you can eliminate that. Here's
another example:
Sheets("Sheet1").QueryTables(1).Delete
What the Delete Method Doesn't Do
The Delete method breaks the link between the table on the worksheet and the
underlying table. It doesn't, however, delete the data on the worksheet.
It's similar (if not exactly like) removing the check box next to Save Query
Definition in the Data Range Properties
dialog box.
Deleting All the Queries
Danger, Will Robinson. Once you delete a QueryTable, it's gone for
good. One wayward deletion is not too big of a problem, you just create a
new one. If you delete them all, you may be looking at a lot of work
getting them back. Enough mothering already. Use a For Each
statement to loop through the QueryTables collection and delete each
QueryTable.Sub DeleteAllQueries()
Sub DeleteAllQueries()
Dim qt As QueryTable
Dim WSh As Worksheet
For Each WSh In ThisWorkbook.Worksheets
For Each qt In
WSh.QueryTables
qt.Delete
Next qt
Next WSh
End Sub
Because QueryTables is a property of the Worksheet object, I also loop
through all the Worksheets to get to each QueryTables collection. If you
also want the data to disappear, you can use the ClearContents method on the
range that occupies the QueryTable. To get to that range, use the
ResultRange property of the QueryTable object. Add a line like this above
qt.Delete:
qt.ResultRange.ClearContents
Don't add it after qt.Delete, because it won't be there.
|
