Tempy wrote ...

I am downloading a spreadsheet from SAP in wk1 format. The columns with
dates in, i format so that i can use the spreadsheet in Access; by using
the following code:

The original format is: 20040501

Range("H:H,L:L,M:M,T:T,X:X,AB:AB").Select
Selection.NumberFormat = "d-mmm-yy"

Columns("H:H").Select
Selection.TextToColumns Destination:=Range("H1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True

It then looks like: 01-May-04, which is great.

However my problem is the fields that contain this:
00000000, this then becomes 01-Jan-00 and in the formula bar it looke
like this 1900/01/00.

Try

.NumberFormat = "d-mmm-yy;;"

Because you are using the data elsewhere, I wouldn't recommend
replacing the zeros with empty string. This could cause an undesirable
mixed types situation (see
http://www.dicks-blog.com/excel/2004...l_data_m.html). You
will have to handle the situation where the date is zero which is a
valid date value. BTW note that with the Jet provider the following
query:

SELECT DAY(0), MONTH(0), YEAR(0)

returns 30, 12, 1899 respectively.

Jamie.

--