Macro to convert txt file into excel - Help required

[jeetusaini85]

Dear Friends,

I need your help to get this done.

I have a macro code in which we can convert .TXT files to excel files. We have a software who generate .TXT files and we convert those files into excel sheets. The below mentioned code works find but not as per requirement.

The .TXT file data is :

 8970652888 | Dear Customer,
We wish to inform you that your loan application no.[ICL/AF/1687] for Loan facility of Rs. [3004750.0000] has been approved. Our representative will call your shortly. Terms & Conditions apply.

Thanks & Regards

i need this line in excel into two columns only i.e. in "A" column we need only mobile numbers and in "B" column we need rest of the line.

I tried to change Array number but not success. Hope you all will help me.

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(10, 1))

Set wbText = ActiveWorkbook
wbText.Worksheets(1).Columns("A:B").EntireColumn.AutoFit
wbText.SaveAs Filename:=strPath & Left(aryFileNames(i), Len(aryFileNames(i)) - 4), _
FileFormat:=xlWorkbookNormal

wbText.Close
Next
Application.ScreenUpdating = True
End Sub

 

[jeetusaini85]

Dear All,

Please Help !!!

 

[jeetusaini85]

I m surprised that no one is here to help me out, then why this forum is here?

 

[jeetusaini85]

Required little help in Macro to convert txt file into excel

Dear All,

I hope that someone will help me on that.

Thanks

 

[stefankemp]

Re: Required little help in Macro to convert txt file into excel

Open it as a delimited file:

<code>Workbooks.Open(Filename:=strPath & aryFileNames(i), Format:=6, Delimiter:="|")

</code></pre>

 

[jeetusaini85]

Re: Required little help in Macro to convert txt file into excel

Dear stefankemp,

First of all thank you very much for reply. I have received syntax error when applying your code in below mention line:

Workbooks.Open (Filename:=strPath & aryFileNames(i), Format:=6, Delimiter:="|") _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), _
Array(10, 1))

Please help....

 

[exceldevs]

Re: Required little help in Macro to convert txt file into excel

Dear stefankemp,

First of all thank you very much for reply. I have received syntax error when applying your code in below mention line:

Workbooks.Open (Filename:=strPath & aryFileNames(i), Format:=6, Delimiter:="|") _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), _
Array(10, 1))

Please help....

Remove everything after Delimiter:="|").

 

[jeetusaini85]

Re: Required little help in Macro to convert txt file into excel

Thanks exceldevs, but still getting syntax error:

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.Open(Filename:=strPath & aryFileNames(i), Format:=6, Delimiter:="|")

Set wbText = ActiveWorkbook
wbText.Worksheets(1).Columns("A:B").EntireColumn.AutoFit
wbText.SaveAs Filename:=strPath & Left(aryFileNames(i), Len(aryFileNames(i)) - 4), _
FileFormat:=xlWorkbookNormal

wbText.Close
Next
Application.ScreenUpdating = True
End Sub

 

[stefankemp]

Re: Required little help in Macro to convert txt file into excel

Remove the brackets around the arguments:

Workbooks.Open Filename:=strPath & aryFileNames(i), Format:=6, Delimiter:="|"

 

[jeetusaini85]

Re: Required little help in Macro to convert txt file into excel

Dear Stefankemp,

Thanks for support, it works bot not as per my requirement. Let me explain with pictures:

I have txt file like this:

And i want this after converted in excel like this:

Hope you understand what i want.