ExcelBanter - Deleting a query

Rhonda

Here's something to try.

Sub DelQuery()

Dim Fname As String
Dim RngName As String
Dim qt As QueryTable

ChDir "C:\Windows\Application Data\Microsoft\Queries\"

Fname = Application.GetOpenFilename

If Fname < "False" Then
RngName = Left(Dir(Fname), Len(Dir(Fname)) - 4)
For Each qt In ActiveSheet.QueryTables
If qt.ResultRange.Address = ActiveSheet.Range(RngName).Address Then
qt.SaveData = False
ActiveSheet.Range(RngName).ClearContents
ActiveSheet.Names(RngName).Delete
Exit For
End If
Next qt
Kill Fname
End If

End Sub

Excel creates a range name when a query is added to the sheet that is the
same as the iqy file name. This loops through the querytables to find one
with the same range as that range name, then deletes it. The problem that
you may run into is if more than one query is created with the same iqy
file. If you were to delete a query, then create another query with the
same name, it would append an "_1" to the end of the range name which would
cause a problem. Hopefully it will get you started though.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Rhonda" wrote in message
...
Hi,

I hope someone could help me this, I have a feeling this
is not possible but anyway, here goes, is it possible to
create a routine for a command button that would prompt
the user to enter a query name(achieved when you right
click on a query and then open Data Range Properties,
this is the actual name of the iqy file)and when the user
enters the name, he can then delete it from it's place on
the spreadsheet(in my case it would be somewhere in sheet
2) and the iqy file stored in the Microsoft/Queries
default folder? Maybe you know another way of handling
it. Either way, I would be truly grateful!!!! Thanks
for your help!