Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Conversion from text to excel
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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
.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default 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...
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem in Conversion of Excel to Text(Tab delimated) Sagar Excel Discussion (Misc queries) 1 March 22nd 10 12:17 PM
Number conversion to text in Ms excel bafarooqi Excel Worksheet Functions 2 November 20th 08 08:40 AM
Text file conversion to excel Michael M[_2_] Excel Discussion (Misc queries) 1 May 16th 08 08:28 PM
Excel Text Conversion Problem Michael D Excel Discussion (Misc queries) 0 March 14th 06 12:26 AM
Excel conversion to word 2000 Text Eric Excel Programming 2 November 28th 04 04:23 PM


All times are GMT +1. The time now is 05:44 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement