Leading Zeros Get Dropped Off from Excel Conversion

I have a VB Script which converts a tilde delimited file to Excel, version 2003. All seems fine but discovered that leading zeros are dropping off from certain fields. Is there a way to include a component in the script to not drop leading zeros? Below is the script being used to convert the tilde delimited file. Thanks in advance.

    Const xlDelimited  = 1
    Const xlWorkbookNormal = -4143

    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True

    objExcel.Workbooks.OpenText "C:\Scripts\Test.txt", _
    , , xlDelimited, , , , , , , Tru开发者_StackOverflow中文版e, "~"

    objExcel.Workbooks("Test.txt").SaveAs _
   "C:\Scripts\Test.xls", xlWorkbookNormal

    objExcel.Workbooks("Test.xls").Close

    objExcel.Quit

You can use the FieldInfo parameter to force Excel to interpret the data as text. According to the documentation for OpenText, FieldInfo is:

An array containing parse information for individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.
...
If the source data has fixed-width columns, the first element in each two-element array specifies the position of the starting character in the column (as an integer; character 0 (zero) is the first character). The second element in the two-element array specifies the parse option for the column as a number between 0 and 9, as listed in the preceding table.

Modifying your example slightly:

Option Explicit

Const xlDelimited  = 1
Const xlWorkbookNormal = -4143
Const xlGeneralFormat = 1
Const xlTextFormat = 2

Dim objExcel
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

objExcel.Workbooks.OpenText "C:\Scripts\Test.txt", _
    , , xlDelimited, , , , , , , True, "~", _
    Array(Array(1, xlTextFormat), Array(2, xlTextFormat), Array(3, xlGeneralFormat))

objExcel.Workbooks("Test.txt").SaveAs _
    "C:\Scripts\Test.xls", xlWorkbookNormal

objExcel.Workbooks("Test.xls").Close

objExcel.Quit

This is the Test.txt I used:

"001234"~"000543"~"000555"

In this example, the first two columns are interpreted as text (leading zeroes preserved), while the third column is interpreted as a number (the way Excel does normally).

Leading 0s get dropped in Excel because it stores anything that looks like a number as a number. If you really need to display leading 0s you have to trick Excel to store the number as a string of characters. Depending on the version of Excel you're using (and, it seems to me, the alignment of the planets) you can usually accomplish this by wrapping your fields in quotes or by preceding the field with a single quote. That should trick Excel into storing the number as text. Example:

"001234"~"005678"

or

'001234~'005678

But in my experience this is rarely necessary. If you're performing calculations on the values, you don't need the leading 0s. If you're storing and/or transporting data Excel isn't really appropriate anyways - use a csv or some other flat file. The only good reason I can think of for doing this is that you're using Excel to display or print data for an end user and your dataset includes some sort of numeric string like SSNs or SKU numbers. If that's the case, the above workaround is the only way I've found to accomplish it.

EDIT: Should have said at the top: I don't think there's a way to do this in the script you're using. The workaround involves changing the way your data file is produced, ie; adding characters to the fields. Alternately, you could write a script that would read in your data file line by line and field by field and write the fields to your Excel file including the wrapping quotes/leading single quote.