How do I Stop a Continuous Loop?

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

The below code imports data from text files into a
database. Unfortunately, the loop does not end and just
keeps importing the data over and over and over again.
Does anyone have any suggestions?

Private Sub Command11_Click()

Dim myfile
Dim mypath
mypath = "C:\Documents and Settings\u26g\My
Documents\reusability\"
Do
myfile = Dir(mypath & "*.txt")
'this will import ALL the text files (one at a time, but
automatically) in this folder.
DoCmd.TransferText
acImportDelim, "Tab_Spec", "Resuability_test", mypath &
myfile
myfile = Dir
Loop Until myfile = ""

End Sub
 
Bob said:
The below code imports data from text files into a
database. Unfortunately, the loop does not end and just
keeps importing the data over and over and over again.
Does anyone have any suggestions?

Private Sub Command11_Click()

Dim myfile
Dim mypath
mypath = "C:\Documents and Settings\u26g\My
Documents\reusability\"
Do
myfile = Dir(mypath & "*.txt")
Click to expand...
[snip]

The first Dir() call above has to be outside your Do Loop. Otherwise you
are just resetting it to the first text file over and over again.
 
Private Sub Command11_Click()

Dim myfile
Dim mypath

mypath = "C:\Documents and Settings\u26g\My Documents\reusability\"
myfile = Dir(mypath & "*.txt")

Do
myfile = Dir()
'this will import ALL the text files (one at a time, but
automatically) in this folder.
DoCmd.TransferText
acImportDelim, "Tab_Spec", "Resuability_test", mypath & myfile
myfile = Dir
Loop Until myfile = ""

End Sub
 
Do
myfile = Dir()
Click to expand...

The two problems with this are that

a) the first return from Dir is thrown away, and
b) the last value will be "", which will cause an error in the rest of the
loop.

The correct order is:

' get the first file here
strMyFile = Dir(Something)

' I always like tests at the top!
Do While True

' body of loop here
DoSomethingWith strMyFile

' now get the next value
strMyFile = Dir()

If Len(strMyFile)=0 Then ' neater than strMyFile=""
' no files left
Exit Do

End If

Loop



Hope that helps


Tim F
 
Back
Top