Dir( ) function not working first time through
 

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

Dir( ) function not working first time through
 

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

"T_o_n_y" wrote in message
...
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

Dir( ) function not working first time through
 

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" wrote:

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

"T_o_n_y" wrote in message
...
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

Dir( ) function not working first time through
 

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

"T_o_n_y" wrote in message
...
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" wrote:

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

"T_o_n_y" wrote in message
...
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

Dir( ) function not working first time through
 

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" wrote:

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

"T_o_n_y" wrote in message
...
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" wrote:

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

"T_o_n_y" wrote in message
...
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

Dir( ) function not working first time through
 

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

"T_o_n_y" wrote in message
...
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" wrote:

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

"T_o_n_y" wrote in message
...
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" wrote:

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

"T_o_n_y" wrote in message
...
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