ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Conversion from text to excel (https://www.excelbanter.com/excel-programming/436214-conversion-text-excel.html)

Ayobami Adeloye
Conversion from text to excel
 
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
Conversion from text to excel
 
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" wrote:

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
.

Ayobami Adeloye
Conversion from text to excel
 
On Nov 16, 10:42*am, Joel wrote:
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" wrote:
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
.

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


All times are GMT +1. The time now is 10:50 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com