Function Error

[Rhonda]

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


message

 

[Tom Ogilvy]

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.

 

[Rhonda]

RngName contains the right name but for some reason this
code Set rng = sh.Range(RngName) indicates that the
worksheet failed and rng = Nothing.

Everything else is right.

Rhonda

 

[Rhonda]

Sorry Tom, I now see what the problem was: this is a
sample iqy file name St. Johns_12308.iqy and the range
name was St._Johns_12308.iqy, I guess it put an underscore
for the blank!

Again, Thanks!