Help with importing text files with VBA

[Captain Smith]

Hi. My objective is to import all .txt files located in a folder named output located on my desktop. The code below I mostly gleaned from this message board. I am running into a strange thing, however. Suppose I have 22 files in the output folder. The first time I run the macro it imports the 22 files correctly. If I then delete these files and replace them with 9 different files, the macro appears to try reading the same 22 files from the first time (it then gives me an error that it cannot find these files). Is there something I am missing here? Do I need to clear .FoundFiles.Count or something else in the beginning of my code?

Thanks much.


Sub Find()
Dim TmpDir, tmpdir1
TmpDir = CurDir()
ChDrive "C"
ChDir "\Documents and Settings\johnc\Desktop\output"
tmpdir1 = CurDir()
Location = tmpdir1

Application.DisplayAlerts = False
Set fs = Application.FileSearch
With fs
.LookIn = Location
.Filename = "*.txt"
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found. Click OK to continue."
For I = 1 To .FoundFiles.Count


Workbooks.OpenText Filename:=.FoundFiles(I), Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1))

Next I
Else
MsgBox "There were no files found."

End If
End With
Call testIt
End Sub
Sub testIt()
Dim newWB As Workbook, aWB As Workbook
Set newWB = Workbooks.Add
For Each aWB In Application.Workbooks
If aWB Is newWB Or aWB Is ThisWorkbook Then
Else
aWB.Sheets(1).Move Before:=newWB.Sheets(1)
End If
Next aWB
SortWorksheets
End Sub

 

[Andrew Poulsom]

From Help:

Use the NewSearch method to reset the search criteria to the default settings. All property values are retained after each search is run, and by using the NewSearch method you can selectively set properties for the next file search without manually resetting previous property values. The following example resets the search criteria to the default settings before beginning a new search.

With Application.FileSearch
    .NewSearch
    .LookIn = "C:\My Documents"
    .SearchSubFolders = True
    .FileName = "Run"
    .MatchTextExactly = True
    .FileType = msoFileTypeAllFiles
End With

 

[Captain Smith]

Hi..where exactly in my code do I add this? I put it at the top and I am having the same issue. I did change the path in the code you provided to the path I want.

Thanks.

 

[Andrew Poulsom]

Add the NewSearch to your code, like this:

Set fs = Application.FileSearch 
With fs 
.NewSearch
.LookIn = Location 
.Filename = "*.txt" 
If .Execute > 0 Then

 

[Captain Smith]

Wow..still not working. I did exactly as you said, but it does not refresh the output folder. Could something be set on my computer that interferes with the .NewSearch function?

Thanks again.