How to REALLY delete a query table

[Glory]

When I clear the range that holds query table "MyTable", the table vanishes. If I delete the name reference in ThisWorkbook.Names, then there's nothing left of the query table...

Or is there?

When I try to create another table with the same name:

    With .QueryTables.Add(Connection:="URL;" & a1 & b5, Destination:=b3.Range("A1"))
        .AdjustColumnWidth = False
        .FieldNames = False
        .BackgroundQuery = True
        .Name = "[COLOR=red]MyTable[/COLOR]"
        .RowNumbers = False
        .WebFormatting = xlWebFormattingNone
        .WebSelectionType = xlSpecifiedTables
        .WebTables = "3"
 
        .Refresh BackgroundQuery:=False
    End With

The name of the table is automatically appended with a "_1", like: "MyTable_1".

This behavior can be avoided if the workbook is closed and reopened before the new table is created. That solution doesn't help at all if I'm trying to create a button that will delete broken tables and reproduce them with the same name to preserve ADO functionality.

How can I really delete a query table?

 

[Sektor]

Try to delete sheet.

 

[Glory]

Did. Had a huge problem with OLE object failure. Mouse-over crashes Excel.

Had a thread on that last night, GTO couldn't reproduce the event but I can reproduce it reliably every time, with new code, in new workbooks.

Tried closing and reopening the workbook after sheet recreation within the macro using OnTime and now this is happening:

http://www.mrexcel.com/forum/showthread.php?t=550554

Is there really no way to completely delete a query table?

 

[Sektor]

Excel 2007/2010.

1. Change extension to "zip".
2. Use WinZip (or other utility) and enter archive.
3. Delete folder "xl\queryTables".
4. Save changes.
5. Change extension back.

When launching file, press "OK".

 

[Glory]

That sounds like a really ingenuitive solution.

Excel '03 and Windows XP, is there a similar solution? I'm on a work PC and can't get winzip and I need to do this using a macro not manually because I need to automate the procedure.

Edit: Did try it btw, popup reads "file corrupted".

 

[Sektor]

Change sheet name.

Sub DeleteQT()

    Dim qt As QueryTable
    
    For Each qt In Sheets("Sheet1").QueryTables
        qt.Delete
    Next

End Sub

 

[Glory]

That's not the worst idea in the world. It's going to make me feel sick every time think about somebody running it because I'll have to do this:

[COLOR=darkgreen]a = Sheets("Sheet1").Name[/COLOR]
 
[COLOR=darkgreen]Sheets.Add After:=Sheets("Sheet1")[/COLOR]
 
[COLOR=darkgreen]Sheets("Sheet1").Visible = False[/COLOR]
 
[COLOR=darkgreen]Sheets("Sheet1").Name = "DeleteMe" & a[/COLOR]
 
[COLOR=darkgreen]End Sub[/COLOR]
 
 
 
[COLOR=blue][WorkBook_Close / WorkBook Open][/COLOR]
 
[COLOR=blue]For each sheet in ThisWorkbook.Sheets[/COLOR]
 
[COLOR=blue] If Left(Sheet.Name, 8) = "DeleteMe" Then[/COLOR]
[COLOR=blue]      Sheet.Delete[/COLOR]
[COLOR=blue] End if[/COLOR]

But I guess it's better than nothing... I can't believe how big a problem this has become. All I wanted was to give them the ability to easily recreate the query tables in case something broke.

Thanks Sektor. I would not have thought of this alone.

 

[Sektor]

Try this one.

 

[Glory]

If I ever get a computer I'll try downloading it >_>

Thanks for lending a hand.