One got truncated (and minor tweaks--long instead of integer, random
capitalizations, and ".txt" instead of "*.txt". (It seems to work better on
some versions of windows.) And used the .foundfiles(i) directly.

Option Explicit
Sub testme2()

Dim i As Long
Dim myFolder As String
Dim txtWb As Workbook
Dim consWb As Workbook
Dim DestCell As Range

Set consWb = Workbooks.Add(1)

myFolder = "C:\my documents\excel"

With Application.FileSearch
.NewSearch
.LookIn = myFolder
.SearchSubFolders = False
.Filename = ".txt"
If .Execute() 0 Then
Application.StatusBar = "Found Files: " & .FoundFiles.Count
Else
MsgBox "There were no files found."
Exit Sub
End If

For i = 1 To .FoundFiles.Count
Application.StatusBar = "Processing #" & i & ": " & .FoundFiles(i)

Workbooks.OpenText Filename:=.FoundFiles(i), _
DataType:=xlDelimited, Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1))

Set txtWb = ActiveWorkbook

With consWb.Worksheets(1)
If Application.CountA(.Columns("a:a")) = 0 Then
Set DestCell = .Range("a1")
Else
Set DestCell = .Range("a1").End(xlDown).Offset(1, 0)
End If
End With

With txtWb.Worksheets(1)
.UsedRange.Copy _
Destination:=DestCell
End With

txtWb.Close savechanges:=False

Next i
End With
Application.StatusBar = False

End Sub




steve wrote:

Jeff,

If you are doing this manually, than you can record your steps to automate
the task.

There has been much code posted in this forum in opening files in a folder.
The code will loop through all the files (of a given type) in a folder and
open and work on the files.

See if the below will get you started...

Do a Google search and post back with further questions.

===================================
Here's some code from Dave Peterson

this might get you started (but I didn't rename .txt to .old. I just kept
track
of which ones were processed):

Option Explicit
Sub testme2()

Dim myfiles() As String
Dim i As Integer
Dim myfile As String
Dim myfolder As String
Dim txtwb As Workbook
Dim conswb As Workbook
Dim destcell As Range

Set conswb = Workbooks.Add(1)

myfolder = "C:\my documents\excel"

With Application.FileSearch
.NewSearch
.LookIn = myfolder
.SearchSubFolders = False
.Filename = "*.txt"
If .Execute() 0 Then
ReDim Preserve myfiles(1 To .FoundFiles.Count)
Application.StatusBar = "Found Files: " & .FoundFiles.Count
For i = 1 To .FoundFiles.Count
myfiles(i) = .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
Exit Sub
End If
End With

For i = LBound(myfiles) To UBound(myfiles)
Application.StatusBar = "Processing #" & i & ": " & myfiles(i)

Workbooks.OpenText Filename:=myfiles(i), _
DataType:=xlDelimited, Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1))
Set txtwb = ActiveWorkbook

With conswb.Worksheets(1)
If Application.CountA(.Columns("a:a")) = 0 Then
Set destcell = .Range("a1")
Else
Set destcell = .Range("a1").End(xlDown).Offset(1, 0)
===================================
And another from Rod de Bruin
Sub test()
Dim i As Long
With Application.FileSearch
.NewSearch
.LookIn = "c:\Data"
.SearchSubFolders = False
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute(msoSortOrderDescending) 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
Cells(i, 2).Value = FileDateTime(.FoundFiles(i))
Cells(i, 3).Value = FileLen(.FoundFiles(i))
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
===================================

--
sb
"Jeff Armstrong" wrote in message
...
Hello,

I need to combine information from multiple files. All
the files contain the exact same format. The difference
is each file contains a different item. I am trying to
figure out how to automatically open the data files from
Excel and insert them into Excel to create a list. Is
Excel capable of going out to my PC and opening files? If
I can do it for the first file than I would think the rest
of the files could be done through a repeatable loop.

There is a total of 111 files. This would be to painful
to do manually. Additionally the files are sent to my PC
via FTP from and Informix database daily. I need to try
and refresh this data on a daily basis. This appears to
going to be a complex macro. Some direction might be all
that I need. If anyone has some suggestions or can point
me to some websites that might go into detail this would
be great. Any help will be greatly appreciated.

Thanks,
Jeff

--

Dave Peterson