Saturday, April 10, 2010

VBScript - Convert text file into Excel Columns

VBScript - Convert text file into Excel Columns (Opens a text file and converts the data to column format.)

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]