CODEWITHSUNDEEP
excelvbscriptformatvba
satish
satish

Reputation: 21

Tab delimited text file to Excel file with text formatted

I am new to VB Programming .I want to convert a text file into a excel file.The text file is comma seperated and Unicode file.And the converted excel file should have all fields in text format(To preserve the prefixed Zeros).After converting the text file to excel with the below code,the zeros are truncated in the excel file.First i am converting the text file with comma delimited to tab delimited and loading the tab delimited text file into excel.

Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("SOURCE_FILE_PATH.txt", ForReading,False,-1)
strContents = objFile.ReadAll
objFile.Close
strContents = Replace(strContents, ",", vbTab)
Set objFile = objFSO.OpenTextFile("SOURCE_FILE_PATH.txt", ForWriting, True, -1)
objFile.Write strContents
objFile.Close
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(Wscript.Arguments.Item(0))
objWorkbook.Saveas WScript.Arguments.Item(1),51
objWorkbook.Close False
Set objWorkbook = objExcel.Workbooks.Open("Target_XLSX_FILE_PATH.xlsx") 
objExcel.Visible = True
Set objRange = objExcel.Range("A:K")
objRange.NumberFormat = "@"--converting to text format
objExcel.Save 
objWorkbook.Close False
objexcel.Quit

In the line Set objWorkbook = objExcel.Workbooks.Open(Wscript.Arguments.Item(0)) The tab delimited file is loaded in to various columns with Genetal format.After i convert that to text format also,The zeros won't be there. Thanks in advance for the help.

Upvotes: 0

Views: 9875

Answers (1)

WGS
WGS

Reputation: 14179

There's no need to actually use FSO for this, if you're not going to do anything to the data inside the text file prior to importing to Excel.

This is my data:

enter image description here

This is the code:

Sub TextOpen()
    arrtext = Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2))
    Workbooks.OpenText Filename:="C:\Users\jeromem\Desktop\BK201.txt", Comma:=True, FieldInfo:=arrtext
End Sub

This is the result after running:

enter image description here

Check this for the OpenText method and this for the FieldInfo data types. As you can see there, text format is equal to 2.

arrtext is basically read as, for column 1 of text, apply format 2; for column 2 of text, apply format 2..... We then feed this array to FieldInfo. We also set Comma:=True since this is the delimiter in your text file.

Let us know if this helps.

Upvotes: 1

Related Questions