Description:
Sometimes more frequently we may have to read a delimated text file and convert the data into excel columns; and use it for further processing as a Excel data, in order to convert the text to columns every time manually the below programme/code will help converting it programmatically. First it will ask you to select the text file that you want to convert and will gohead with convertion. Here "#" is been used a data delimeter, of course you can change the delimeter by passing it to "OtherChar" attribute.
Code Usage & Description:
Copy & paste the code in a module in your macro programme. You may change the names of the procedures as per your requirements. This procedure will pops up the File Open Dialog box to allow you to pick the delimated text file (you may refer to the sample data above in the code section used) and converts it as a General Data Type. If no file is selected then it pops up a message box saying the same.
Private Sub ConvertTXT2Column()
Dim sINPUTFILENAME$
sINPUTFILENAME = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "Open Text file to be converted.")
If (Len(sINPUTFILENAME) <> 0 And StrComp(LCase(sINPUTFILENAME), "false") <> 0) Then
'Convert & format cells - Start
Columns("A:A").Select
Workbooks.OpenText Filename:=sINPUTFILENAME, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
, Space:=False, Other:=True, OtherChar:="#", FieldInfo:=Array(Array(1, 1 _
), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))
'Convert & format cells - End
Else
MsgBox "You have not selected any text file (*.txt) to convert.", vbExclamation, "No file"
End If
End Sub
'-------------------------------------------------------------------------------------------
| #ID | #Name | #Country | #Sex |
| #0001 | #Kenney Martin | #Singapore | #M |
| #0002 | #Kanagal | #Singapore | #M |
| #0003 | #Ananthi | #Malaysia | #F |
[Via]