Help with importing text files with VBA

Captain Smith

Active Member
Joined
Feb 28, 2003
Messages
324
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.

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

Captain Smith

Active Member
Joined
Feb 28, 2003
Messages
324
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Add the NewSearch to your code, like this:

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

Captain Smith

Active Member
Joined
Feb 28, 2003
Messages
324
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,334
Messages
5,769,473
Members
425,553
Latest member
chrisc297

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top