#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    1
    Rep Power
    0

    Tab delimited text file to Excel file with text formatted


    Hi,

    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.

    Thanks,
    Satish.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    14
    Rep Power
    0
    A suggestion:
    Try changing the format of the Excel file cells to "Text" before you put the contents of the text file into them. I don't know if that will work for sure, but it's something to try.

    Cheers
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    119
    Rep Power
    158
    just do what you are doing, but build the spreadsheet first and then fill the cells

    that way the cell format will be more appropriate and the leading zeros will be maintained

    here to help

IMN logo majestic logo threadwatch logo seochat tools logo