It now gets to ThisWorkbook.Names(RngName).Delete and
comes back with Object defined error, any idea?

Almost there....



What does the string RngName hold?

Is there a name (Insert = Name = Define) on worksheets
(2)/Sheet2 with that
name?

Does Worksheets(2) have a tab name of sheet2 (is Sheet2
the second sheet in
the tab order).

If all of the above meets expectations, then the code
should work.

--
Regards,
Tom Ogilvy




"Rhonda" wrote in
message
...
Everytime I run this code it never picks up on the query,
it always comes back and says it can't be found. I
attempted to Debug using the Step Into and when it gets
to Set rng = sh.Range(RngName), I get sh.Range(RngName)
=<Application-Defined or Object-Defined-Error, Rng =
Nothing and also Method "Range" of Object "_Worksheet"
failed.

It does get the iqy file and then strips of the last 4
characters to give the range name but for some reason
this is all that happens, it just goes on to display that
it wasn't found. (Just a note, all of my queries are in
Sheet2). Could you take a look, I would really like to
get this working.

Dim Fname As String
Dim RngName As String
Dim qt As QueryTable
Dim sh As Worksheet
Dim sPath As String
Dim sName As String
Dim bFlag As Boolean
Dim rng As Range
sPath = Application.Path & "\Queries"

Set sh = Worksheets(2)

ChDrive sPath
ChDir sPath
Fname = Application.GetOpenFilename( _
filefilter:="Query Files (*.iqy),*.iqy")

If Fname < "False" Then
sName = Dir(Fname)
RngName = Left(sName, Len(sName) - 4)
On Error Resume Next
Set rng = sh.Range(RngName)
On Error GoTo 0
If Not rng Is Nothing Then
For Each qt In sh.QueryTables
If qt.ResultRange.Address = rng.Address Then
bFlag = True
qt.SaveData = False
sh.Range(RngName).ClearContents
ThisWorkbook.Names(RngName).Delete
Exit For
End If
Next qt
If bFlag Then
Kill Fname
Else
MsgBox sName & " query was not found"
End If
Else
MsgBox "Range with name " & RngName & " not found"
End If
End If

Regards,
Tom Ogilvy


"Rhonda" wrote in
message
...
Hi,

I was given this code from another post but after I
tried
to run it, it gives me a couple of errors.
Here is what I wanted it to do
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!!!!

Here is what someone responded with:

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.



Here is the code:
Dim Fname As String
Dim RngName As String
Dim qt As QueryTable

ChDir "C:\Program Files\Microsoft
Office\Office\Queries\"
Fname = Application.GetOpenFilename

If Fname < "False" Then
RngName = Left(Dir(Fname), Len(Dir(Fname)) - 4)
For Each qt In Sheets("QuerySheet").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
When it gets to the second if, I run into trouble. the
range its picking up for the query is wrong and the
third
if gives me an object defined error.


Could soneone please clean up this code for me so it
can
work, its an excellent idea that I need to make my
project complete. Thank you

Demonstrating from the immediate window:

Range("A1:A200").Name = "ABCD"
? Thisworkbook.Names(1).Name
ABCD
rngName = "ABCD"
? Thisworkbook.Names(rngName).RefersToRange.Address
$A$1:$A$200
thisWorkbook.Names(rngName).Delete


So thisworkbook.Names(rngName).Delete will work fine if you give it a valid
name.

See what the value of rngName is before you delete and see if there is a
name that matches rngName.

I ran test code form another sheet to delete a name:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
rngName = "Name1"
ThisWorkbook.Names(rngName).Delete
End Sub


That worked fine as well.

I believe the problem has something to do with your environment - the syntax
of the code appears OK.

--
regards,
Tom Ogilvy



"Rhonda" wrote in message

It now gets to ThisWorkbook.Names(RngName).Delete and
comes back with Object defined error, any idea?

Almost there....



What does the string RngName hold?

Is there a name (Insert = Name = Define) on worksheets
(2)/Sheet2 with that
name?

Does Worksheets(2) have a tab name of sheet2 (is Sheet2
the second sheet in
the tab order).

If all of the above meets expectations, then the code
should work.

--
Regards,
Tom Ogilvy




"Rhonda" wrote in
message
...
Everytime I run this code it never picks up on the query,
it always comes back and says it can't be found. I
attempted to Debug using the Step Into and when it gets
to Set rng = sh.Range(RngName), I get sh.Range(RngName)
=<Application-Defined or Object-Defined-Error, Rng =
Nothing and also Method "Range" of Object "_Worksheet"
failed.

It does get the iqy file and then strips of the last 4
characters to give the range name but for some reason
this is all that happens, it just goes on to display that
it wasn't found. (Just a note, all of my queries are in
Sheet2). Could you take a look, I would really like to
get this working.

Dim Fname As String
Dim RngName As String
Dim qt As QueryTable
Dim sh As Worksheet
Dim sPath As String
Dim sName As String
Dim bFlag As Boolean
Dim rng As Range
sPath = Application.Path & "\Queries"

Set sh = Worksheets(2)

ChDrive sPath
ChDir sPath
Fname = Application.GetOpenFilename( _
filefilter:="Query Files (*.iqy),*.iqy")

If Fname < "False" Then
sName = Dir(Fname)
RngName = Left(sName, Len(sName) - 4)
On Error Resume Next
Set rng = sh.Range(RngName)
On Error GoTo 0
If Not rng Is Nothing Then
For Each qt In sh.QueryTables
If qt.ResultRange.Address = rng.Address Then
bFlag = True
qt.SaveData = False
sh.Range(RngName).ClearContents
ThisWorkbook.Names(RngName).Delete
Exit For
End If
Next qt
If bFlag Then
Kill Fname
Else
MsgBox sName & " query was not found"
End If
Else
MsgBox "Range with name " & RngName & " not found"
End If
End If

Regards,
Tom Ogilvy


"Rhonda" wrote in
message
...
Hi,

I was given this code from another post but after I
tried
to run it, it gives me a couple of errors.
Here is what I wanted it to do
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!!!!

Here is what someone responded with:

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.



Here is the code:
Dim Fname As String
Dim RngName As String
Dim qt As QueryTable

ChDir "C:\Program Files\Microsoft
Office\Office\Queries\"
Fname = Application.GetOpenFilename

If Fname < "False" Then
RngName = Left(Dir(Fname), Len(Dir(Fname)) - 4)
For Each qt In Sheets("QuerySheet").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
When it gets to the second if, I run into trouble. the
range its picking up for the query is wrong and the
third
if gives me an object defined error.


Could soneone please clean up this code for me so it
can
work, its an excellent idea that I need to make my
project complete. Thank you