PDA

View Full Version : Solved: macro to convert all txt files in a folder to excel files



aravindhan_3
07-19-2009, 03:00 AM
Hi,

I need your help again..

I have some 100 txt files in a folder. i open each file manually and convert the data text to columns using the below code and save the same as excel files.

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(55, 1), Array(68, 1)), _
TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

Can we do this by a macro. that is loop through each files in a folder, open, text to columns and save the same as xls files.

Arvind

GTO
07-19-2009, 03:13 AM
Hi Arvind,

Could you post one of the text files or a similarly constructed example textfile?

Edit: I forgot to ask, are they all similar in that they all are fixed widths?

Mark

aravindhan_3
07-19-2009, 03:33 AM
Hi,

please find attached the sample txt file. All files are same format, so i recorded a macro for text to columns and saved in personal xls.
each time i open a txt file and run the macro and i save the file as xls manually.

Thanks for your help
Arvind

GTO
07-19-2009, 04:05 AM
Hi Arvind,

Maybe a dopey question, but (in xl2000) when I open the textfile (thru Win Explorer to avoid Excel's wanting to TextToColumn it) with Excel, I get 4 columns of data.

Even if I open the textfile in notepad and wack it into a worksheet, same thing.

If I run your current code, I error out, as there's no where near 68 columns.

Sorry I'm missing it, as I'd like to help. Maybe I am staring at something (that should be) obvious, but could you post a worksheet that has had your current code run on it, using the textfile (ReportTEST.txt) that you posted. I'm hoping that might help me see what I am missing.

Mark

aravindhan_3
07-19-2009, 04:15 AM
Hi,

I apologies, i have attached the text file again, please try if you can open?

Arvind

mdmackillop
07-19-2009, 05:47 AM
I ageee with Mark regarding column numbers. Text like this can be tricky to get in a suitable format. Is the enclosed something like what you are after? (no code attached)

aravindhan_3
07-19-2009, 06:38 AM
Hi,

Dont know y different results

I have attached 2 files one txt file and another xl file after converting to text to columns.

Arvind

aravindhan_3
07-19-2009, 06:39 AM
attached the original txt file

GTO
07-19-2009, 06:46 AM
@Malcom:

Hi buddy :hi:

Must hit sack for a few hours and didn't see yours til just now. I used Arvind's current to delimit for now...

@ Arvind:

The second text file was helpful, as at least I was able to get it to a worksheet all in Col A, and run your current code on it.

On the sheet in the attached is the results I get from the textfile example supplied.

In a Standard Module:

Option Explicit

Sub ConvertTextFiles()
Dim fso As Object '<---FileSystemObject
Dim fol As Object '<---Folder
Dim fil As Object '<---File
Dim strPath As String
Dim aryFileNames As Variant
Dim i As Long
Dim wbText As Workbook

Application.ScreenUpdating = False
'// I am assuming the textfiles are in the same folder as the workbook with //
'// the code are. //
strPath = ThisWorkbook.Path & Application.PathSeparator

'// Set a reference to the folder using FSO, so we can use the Files collection.//
Set fso = CreateObject("Scripting.FileSystemObject")
Set fol = fso.GetFolder(strPath)

'// Using FSO's Files collection, we'll run through and build an array of //
'// textfile names that exist in the folder. //
ReDim aryFileNames(0)
For Each fil In fol.Files
If fil.Type = "Text Document" Then
'// If correct Type (a text file), we'll assign the name of the found //
'// textfile to the last element in the array - then add an empty //
'// element to the array for next loop around... //
aryFileNames(UBound(aryFileNames)) = fil.Name
ReDim Preserve aryFileNames(UBound(aryFileNames) + 1)
End If
Next
'// ... now since we were adding an empty element to the array, that means we'll//
'// have an emmpty ending element after the above loop - rid it here. //
ReDim Preserve aryFileNames(UBound(aryFileNames) - 1)

'// Basically, For Each element in the array... //
For i = LBound(aryFileNames) To UBound(aryFileNames)
'// ...open the textfile, set a reference to it, SaveAs and Close. //
Workbooks.OpenText Filename:=strPath & aryFileNames(i), _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), _
Array(7, 1), _
Array(55, 1), _
Array(68, 1))
Set wbText = ActiveWorkbook
wbText.Worksheets(1).Columns("A:D").EntireColumn.AutoFit
wbText.SaveAs Filename:=strPath & Left(aryFileNames(i), Len(aryFileNames(i)) - 4), _
FileFormat:=xlWorkbookNormal

wbText.Close
Next
Application.ScreenUpdating = True
End Sub


Hope that helps and a great day to all here,

Mark

aravindhan_3
07-19-2009, 08:33 AM
Hi,

Perfect almost working.. thanks a lot for your time and help.
is it possible to keep all the sheet name as "sheet1" as I am running another macro and I want all the excel files sheet names with sheet1.

Thanks for your help

Arvind

GTO
07-19-2009, 11:27 AM
Sure. To change the sheet name, just add:

wbText.Worksheets(1).Name = "Sheet1"

immedietely above the the line:

wbText.SaveAs Filename:=strPath & Left(aryFileNames(i), Len(aryFileNames(i)) - 4), _
FileFormat:=xlWorkbookNormal

GTO
07-19-2009, 02:43 PM
Addendum:

You mentioned at #7 getting different results. Though I do not oft do stuff w/textfiles (a disclaimer for anything else I botch here), the different results has to do with the the arg 'FieldInfo:='

See at #1 your code example had:

FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(55, 1), Array(68, 1))

But in the attached wb at #7, this arg included:

FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(40, 1), Array(55, 1), Array(78, 1))

The help topic in vba help seems like a decent explanation, so you might want to take a look there.

A CORRECTION:

I misinformed at #4. The error I experienced (and should have spotted/recalled) was due to inclusion of the 'TrailingMinusNumbers' arg, which is not available in Excel 2000. Excel could care less if you include FieldInfo args for non filled columns from the textfile.

Mark