Dir( ) function not working first time through

[Guest]

For the code below, I get an error the first time through but after that it
then runs without error:

Sub GetWFiles(Wpath As String, WhichWfile As Integer)
Dim qt As QueryTable, Wdat As String
Wdat = Dir(Wpath & "\W*.dat")
etc...

Can anyone tell me why?

Here are more details. The error is:
"Excel cannot find the text file to refresh this external data range. Check
to make sure that the text file has not been moved or renamed, then try the
refresh
again."

I posted all the code of the subroutine in a previous post 3 days ago, which
was perhaps overkill since no one responded. If you're interested, that
message was entitled: "Query Table problem when running first time only"
-Tony

 

[NickHK]

Tony,
That error would not be returned because of using that "Dir" line of code.
I can only assume it is being generated later in the code when you try to
refresh a query table when the source file is invalid/missing.

NickHK

 

[Guest]

Thank you, Nick, you are right about that.

However, my question remains. Why does the error generated go away when I
simply run the program again immediately afterwards?

-Tony

 

[NickHK]

Tony,
Well it would depend on what the actual code generating the error in first
place.
I can't guess as the part of the code that you have not posted.

NickHK

 

[Guest]

Nick,

Fair enough. Here's the full code for the sub producing the error. As I
mentioned earlier, I hesitated to post it all because the first time I did I
got no responses. Anyway here it is, and as you point out, the true error
location is the refresh statement.

Sub GetWFiles(Wpath As String, WhichWfile As Integer)
Dim i As Long, qt As QueryTable, Wdat As String, OrigWSheet As String
'On Error Resume Next

Worksheets.Add After:=Worksheets("OrigTarr")
OrigWSheet = "OrigW"
If WhichWfile <> 1 Then OrigWSheet = OrigWSheet & CStr(WhichWfile)
ActiveSheet.Name = OrigWSheet
Wdat = Dir(Wpath & "\W*.dat")

Do While Wdat = ""
GetWDirectoryAgain Wpath, WhichWfile
Wdat = Dir(Wpath & "W*.dat")
Loop
i = 0
Do While Wdat <> ""
i = i + 1
Cells(1, i).Value = Wdat
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & Wpath & Wdat, Destination:=Cells(2, i))
.Name = Left(Wdat, Len(Wdat) - 4)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
'.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Wdat = Dir()
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next
Loop
End Sub

 

[NickHK]

Tony,
I don't see the point of:
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next
within your creation loop. Surely you want to delete them all before you
create them again.

Anyway, look at the Connection string that you are creating. It will never
be valid, hence the error on .Refresh
Hint: ....& "\" & ....

NickHK