PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Conversion from text to excel

Reply
Thread Tools Rate Thread

Conversion from text to excel

 
 
Ayobami Adeloye
Guest
Posts: n/a
 
      16th Nov 2009
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 Quote
 
 
 
 
Joel
Guest
Posts: n/a
 
      16th Nov 2009
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 Quote
 
Ayobami Adeloye
Guest
Posts: n/a
 
      17th Nov 2009
On Nov 16, 10:42*am, Joel <J...@discussions.microsoft.com> 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 Quote
 
 
 
Reply

« user form parameters | Userform question »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Number conversion to text in Ms excel Bob Phillips Microsoft Excel Worksheet Functions 0 20th Nov 2008 08:40 AM
Re: Number conversion to text in Ms excel muddan madhu Microsoft Excel Worksheet Functions 0 20th Nov 2008 07:09 AM
Text file conversion to excel Michael M Microsoft Excel Misc 1 16th May 2008 08:28 PM
Conversion in EXCEL Numeric to Text News Microsoft Excel Discussion 2 29th Jan 2007 01:34 PM
Excel Text Conversion Problem =?Utf-8?B?TWljaGFlbCBE?= Microsoft Excel Misc 0 14th Mar 2006 12:26 AM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 07:32 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top