Import TXT file into Excel and keep first "0" on numbers

B

brettmanning24

Hi,

I am importing a text file into Excel but any numbers that start with
"0" lose the "0" during the import. For example 0123 becomes 123.
It is vital that I can keep the "0", is there anyway to resolve this
issue?

Thanks,

Brett
 
B

brettmanning24

The code I am using to import the text file is as follows:

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
 
T

Tom Hutchins

I don't know which field is the one you are asking about, but you need to
import that field as text. Most of your fields are being imported as the
'general' format, which assumes that anything that looks like a number is a
number. Each field is denoted in the macro by something like Array(x, y),
where x is the number of the field (first field is 1, second field is 2,
etc.) and y is what kind of data it is. Most of your fields are Array(x, 1)
which is the general format. Find the field you are concerned about and
change the data type to 2, for text. For example, if the field you are
concerned about is the third field, you would change Array(3, 1) to Array(3,
2). Then it should import properly.

Hope this helps,

Hutch
 
Top