Formatting question

[rollingzep]

Hi,

<p> I have a column A, to which I import data from a text file. It misses the leading zeros
For example the value 37833100 should be 037833100 but comes in as 3.78E+07

The column has data with values like 02376RAF9, 912796XP9, 37833100 (mix of both text and numeric) but length is 9

How to format so as to include the leading zeros?
will this help?

Worksheets("SheetName").Range("A:A").Select
Selection.NumberFormat = "@"

</p?

 

[rollingzep]

one last question

If it is just the first field that needs formatting while others are irrelevant, then can I just do

strfilename = Application.GetOpenFilename

With ws.QueryTables.Add(Connection:="TEXT;" & strfilename, Destination:=ws.Range("A2"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes := _
Array(xlTextFormat)
.Refresh

End With

It would be impossible to format the code for all the 36 fields in the array separated by commas!

 

[Joe4]

If it is just the first field that needs formatting while others are irrelevant, then can I just do

I do not know. Try it and find out!

It would be impossible to format the code for all the 36 fields in the array separated by commas!

That is a bit of an exaggeration. It is quite easy to do with Copy/Paste. I was able to do this in less than one minute:

With ws.QueryTables.Add(Connection:="TEXT;" & strfilename, Destination:=ws.Range("A2"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = _
    Array(xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, _
    xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, _
    xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, _
    xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, _
    xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, _
    xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat)
.Refresh

End With

One other thing.
Note the documentation shows a colon in front of the equal sign here:

.TextFileColumnDataTypes :=

but that gave me an error unless I removed it and wrote it like this instead:

.TextFileColumnDataTypes =

 

[rollingzep]

I do not know. Try it and find out!


That is a bit of an exaggeration. It is quite easy to do with Copy/Paste. I was able to do this in less than one minute:

With ws.QueryTables.Add(Connection:="TEXT;" & strfilename, Destination:=ws.Range("A2"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = _
    Array(xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, _
    xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, _
    xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, _
    xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, _
    xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, _
    xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat)
.Refresh

End With

One other thing.
Note the documentation shows a colon in front of the equal sign here:

.TextFileColumnDataTypes :=

but that gave me an error unless I removed it and wrote it like this instead:

.TextFileColumnDataTypes =

Thanks a lot!

My code worked with your suggestions

 

[Joe4]

Thanks a lot!

My code worked with your suggestions

Excellent!
Glad to hear that it worked out.