Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a query
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting the text without deleting the formula | Excel Worksheet Functions | |||
another macro query - deleting a worksheet within a query | Excel Discussion (Misc queries) | |||
Deleting cell data without deleting formula | Excel Discussion (Misc queries) | |||
deleting values in a worksheet without deleting the formulas | Excel Worksheet Functions | |||
how prevent formula in cell from deleting when deleting value???? | New Users to Excel |