jeetusaini85
Board Regular
- Joined
- Aug 9, 2013
- Messages
- 131
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 :
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.
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 :
Code:
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.
Code:
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