This is not an importing problem it is more the way excel handles numbers.
To havve leading zeroes either the data has to be stored as textt or the
number needs to be formated to include leading zeroes.

You need to fromat the worksheet as either text or or with a format with
leading zeroes.

Here is a macro that may help.


Sub Getdata1()

Const MyPath = "C:\temp\test"
Const ReadFileName = "test.txt"
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Const StartCol = 1
Const Colwidth = 2
Dim ColWidths(7, 2)
Dim Data(7) As String



ColWidths(1, StartCol) = 1
ColWidths(1, Colwidth) = 21
ColWidths(2, StartCol) = 22
ColWidths(2, Colwidth) = 21
ColWidths(3, StartCol) = 43
ColWidths(3, Colwidth) = 14
ColWidths(4, StartCol) = 57
ColWidths(4, Colwidth) = 23
ColWidths(5, StartCol) = 80
ColWidths(5, Colwidth) = 15
ColWidths(6, StartCol) = 95
ColWidths(6, Colwidth) = 16
ColWidths(7, StartCol) = 111
ColWidths(7, Colwidth) = 16


Set fs = CreateObject("Scripting.FileSystemObject")
'open file
ReadPathName = MyPath & "\" & ReadFileName
Set fread = fs.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

RowCount = 1
Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

Call GetDataField(InputLine, ColWidths, Data)
Call WriteSheet(Data, RowCount)
Loop
tsread.Close

End Sub

Sub GetDataField(InputLine, ByRef ColWidths, ByRef Data)
Const StartCol = 1
Const Colwidth = 2
For DataField = 1 To 7
Data(DataField) = Trim(Mid(InputLine, _
ColWidths(DataField, StartCol), _
ColWidths(DataField, Colwidth)))
Next DataField

End Sub
Sub WriteSheet(ByRef Data, ByRef RowCount)
For DataField = 1 To 7
If IsNumeric(Data(DataField)) Then
If Left(Data(DataField), 1) = "0" Then
Cells(RowCount, DataField). _
NumberFormat = "0####"
End If
Cells(RowCount, DataField) = _
Val(Data(DataField))
Else
Cells(RowCount, DataField) = Data(DataField)
End If
Cells(RowCount, DataField) = Data(DataField)
Next DataField

RowCount = RowCount + 1

End Sub




" wrote:

Hello,

Is it possible to import a txt file into Excel and keep the leading
zero's? I am using vbscript to import the text file, the text file
contains a user reference which sometimes contains a number with a
leading zero i.e. 0111. This number is imported as 111. The user
reference varies sometimes so I cannot really use a custom field i.e.
"0"#.

Below is an example of the import procedure I am using, is it possible
to specify that for example column L is displayed as text?

Range("FileOrig").FormulaR1C1 = FILEtoOPEN

Workbooks.OpenText Filename:=FILEtoOPEN, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array _
(5, 4), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1),
Array(10, 1), Array( _
11, 1), Array(12, 1), Array(13, 1))
Range("A1:AZ1500").Select
Selection.Copy

Thanks,

Brett