QueryTable.connection FoundFiles(i) can not load

Hi there,
I have a series of .txt files that need to be imported in order and processed in EXCEL. I'm trying to sort it first and then processed one by one.
The problem is when I'm using .Foundfiles(i) in the QueryTable. connection to indicate the path, it pops up the window to let you select the file, where I thought it should directly load the current .FoundFiles(i) file.
Here is part of the file. Any help would be greatly appreciated!


Set fs = Application.FileSearch
With fs
.LookIn = "C:\Documents and Settings\mx1000\Desktop\VBA"
.Filename = "*.txt"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
'fsort(i) = .FoundFiles(i)
'import data
Sheets("Row data").Select
Range("A1").Select
With Selection.QueryTable
.Connection = "TEXT;C:\Documents and Settings\mx1000\Desktop\VBA\.FoundFiles(i)"
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
........

Originally Posted by Domski

I'm guessing this might fix it:

Please Login or Register  to view this content.

Dom

Hi Dom,
Thank you for your quick reply. But it doesn't work. It stoped on this sentence and asked for debug. Is there other possibilities?

Thanks

Originally Posted by Domski

I think the whole thing should be more like this:

Please Login or Register  to view this content.

Dom

Thanks so much. I will give a try and get back to you. Appreciate it!

Thanks Dom,
That works! I added to your reputation. Many Thanks!

One more question: it still pop up a selection table each time with the selected FoundFiles(i). Then I need to click "import" to load each file. Is there anyway It can just run through all the files without click each time?