m4nd4li4


Try this modification.

Adds a new sheet once col count = or greater than 256




Sub Getmhl()
Dim wkbk As Workbook
Dim shDest As Worksheet
Dim col As Long
Dim i As Long
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range

Set shDest = ThisWorkbook.ActiveSheet
shDest.UsedRange.ClearContents
col = 1
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\My Documents"
.SearchSubFolders = False
.FileName = "*.mhl"
.FileType = msoFileTypeAllFiles

If .Execute() 0 Then
For i = 1 To .FoundFiles.Count

Workbooks.OpenText _
FileName:=.FoundFiles(i) ' _
' Origin:=437, _
' StartRow:=1, _
' DataType:=xlDelimited, _
' TextQualifier:=xlDoubleQuote, _
' ConsecutiveDelimiter:=False, _
' Tab:=True, Semicolon:=False, _
' Comma:=False, Space:=False, _
' Other:=False, FieldInfo:=Array(1, 1), _
' TrailingMinusNumbers:=True
Set wkbk = ActiveWorkbook
With wkbk.Worksheets(1)
Set rng = .Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
rng.Copy Destination:=shDest.Cells(2, col)
shDest.Cells(1, col) = wkbk.Name
Set rng1 = shDest.Cells(Rows.Count, col).End(xlUp)(2)
Set rng2 = shDest.Range(shDest.Cells(2, col), rng1(0))
rng1.Formula = "=Average(" & rng2.Address & ")"
rng1(2).Formula = "=Stdev(" & rng2.Address & ")"
rng1(3).Formula = "=Count(" & rng2.Address & ")"
wkbk.Close SaveChanges:=False
If col < 256 Then
col = col + 1
Else
ThisWorkbook.Sheets.Add Befo=Worksheets(1)
End If
Next i
Else
MsgBox "There were no files found."
End If
End With
End Su

--
Message posted from http://www.ExcelForum.com