Conversion from text to excel

[Ayobami Adeloye]

Hi guys, I need help, i have a code to convert text files into excel,
however the problem is that the text files are many and can vary in
number. The code i have is repetitive and it contains a code to
convert each of the files, so for instance i have about 295 text files
i need the same number of code to convert them, i believe that this is
not efficient and i need a simple code that will convert all as they
have a common names. A sample of the code is below.

Sub Convert()
ChDir "C:\Documents and Settings\aayobami\Desktop\br by br convert"
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\aayobami\Desktop\br by br convert
\BSH_100_06nov09.txt", _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 1), Array(10, 1), Array(50, 1), Array(80, 1), Array
(110, 1)), _
TrailingMinusNumbers:=True
Cells.Select
Selection.ColumnWidth = 8.29
Cells.EntireColumn.AutoFit
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\aayobami\Desktop\br by br convert
\BSH_100_06nov09.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\aayobami\Desktop\br by br convert
\BSH_101_06nov09.txt", _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 1), Array(10, 1), Array(50, 1), Array(80, 1), Array
(110, 1)), _
TrailingMinusNumbers:=True
Cells.Select
Selection.ColumnWidth = 8.29
Cells.EntireColumn.AutoFit
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\aayobami\Desktop\br by br convert
\BSH_101_06nov09.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\aayobami\Desktop\br by br convert
\BSH_102_06nov09.txt", _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 1), Array(10, 1), Array(50, 1), Array(80, 1), Array
(110, 1)), _
TrailingMinusNumbers:=True
Cells.Select
Selection.ColumnWidth = 8.29
Cells.EntireColumn.AutoFit
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\aayobami\Desktop\br by br convert
\BSH_102_06nov09.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\aayobami\Desktop\br by br convert
\BSH_103_06nov09.txt", _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 1), Array(10, 1), Array(50, 1), Array(80, 1), Array
(110, 1)), _
TrailingMinusNumbers:=True
Cells.Select
Selection.ColumnWidth = 8.29
Cells.EntireColumn.AutoFit
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\aayobami\Desktop\br by br convert
\BSH_103_06nov09.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\aayobami\Desktop\br by br convert
\BSH_104_06nov09.txt", _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 1), Array(10, 1), Array(50, 1), Array(80, 1), Array
(110, 1)), _
TrailingMinusNumbers:=True
Cells.Select
Selection.ColumnWidth = 8.29
Cells.EntireColumn.AutoFit
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\aayobami\Desktop\br by br convert
\BSH_104_06nov09.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\aayobami\Desktop\br by br convert
\BSH_105_06nov09.txt", _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 1), Array(10, 1), Array(50, 1), Array(80, 1), Array
(110, 1)), _
TrailingMinusNumbers:=True
Cells.Select
Selection.ColumnWidth = 8.29
Cells.EntireColumn.AutoFit
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\aayobami\Desktop\br by br convert
\BSH_105_06nov09.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

Please help

 

[Joel]

Try this code

Sub Convert()

Folder = "C:\Documents and Settings\aayobami\Desktop\br by br convert\"

FName = Dir(Folder & "*.txt")
Do While FName <> ""
Workbooks.OpenText Filename:=Folder & FName, _
StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array( _
Array(0, 1), Array(10, 1), Array(50, 1), _
Array(80, 1), Array(110, 1)), _
TrailingMinusNumbers:=True
Set bk = ActiveWorkbook
With bk.ActiveSheet
.Cells.ColumnWidth = 8.29
.Columns("A").AutoFit
BaseName = Left(FName, InStrRev(FName, "."))
bk.SaveAs Filename:=Folder & BaseName & "xls", _
FileFormat:=xlExcel8
bk.Close savechanges:=False
End With
FName = Dir()
Loop
End Sub

 

[Ayobami Adeloye]

Try this code

Sub Convert()

Folder = "C:\Documents and Settings\aayobami\Desktop\br by br convert\"

FName = Dir(Folder & "*.txt")
Do While FName <> ""
   Workbooks.OpenText Filename:=Folder & FName, _
        StartRow:=1, DataType:=xlFixedWidth, _
        FieldInfo:=Array( _
           Array(0, 1), Array(10, 1), Array(50, 1), _
           Array(80, 1), Array(110, 1)), _
           TrailingMinusNumbers:=True
    Set bk = ActiveWorkbook
    With bk.ActiveSheet
       .Cells.ColumnWidth = 8.29
       .Columns("A").AutoFit
       BaseName = Left(FName, InStrRev(FName, "."))
       bk.SaveAs Filename:=Folder & BaseName & "xls", _
          FileFormat:=xlExcel8
       bk.Close savechanges:=False
    End With
    FName = Dir()
Loop
End Sub

thansk a lot Joel, it worked like clockwork...