I got up to this far:
Sub getfile()
Dim TargetSht As Worksheet
Dim i As Integer
Dim Wks As Worksheet
Application.ScreenUpdating = False
Set TargetSht = ThisWorkbook.ActiveSheet
With Application.FileSearch
.NewSearch
.LookIn = "filepath"
.SearchSubFolders = False
.FileName = "*.txt*"
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
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:=True, OtherChar:=":", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array _
(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array( _
27, 1), Array(28, 1), Array(29, 1)), TrailingMinusNumbers:=True
Set wb = ActiveWorkbook
wb.SaveAs FileName = Left(wb.FileName, Len(wb.FileName) - 4) & ".xls", _
FileFormat:=xlWorkbookNormal
wb.Close SaveChanges:=False
Next i
Else
MsgBox "There were no files found."
End If
End With
Application.ScreenUpdating = True
End Sub
_____________________________________________________
but I am having trouble saving error on this part:
Set wb = ActiveWorkbook
wb.SaveAs FileName = Left(wb.FileName, Len(wb.FileName) - 4) & ".xls", _
FileFormat:=xlWorkbookNormal
wb.Close SaveChanges:=False
Your help is greatly appreciated. Thanks.