when import EXCEL to ACCESS, the date format excel column title changed...

[Amolin]

when linking EXCEL to ACCESS, the date format excel column tilt
changed in ACCESS table.

For example:

Excel column title :
May-04,Jun-04,Jul-04....

changed to
F09,F10,F11.....
In ACCESS linked table

Why and how can I keep the date tile as linked table fields name?

Thank you..

Attachment filename: try.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=57659

 

[Frank Kabel]

Hi
are your Excel columns real date values (just formated as 'MMM-YY')?

 

[Amolin]

Hi
are your Excel columns real date values (just formated as 'MMM-YY')?

--
Regards
Frank Kabel
Frankfurt, Germany

Yes, the Excel column titles are formatted as date, you can see th
attached file, When linked to ACCESS the date title couldn't b
recognize by ACCESS and changed to F1,F2,F3......(May be "F
represents "fields"), When I format the date title to "Text" , th
date value change to Numbers!...how can I linked excel table wit
date title to ACCESS correctly

 

[Frank Kabel]

Hi
problem is, Excel stored these values as n umbers internally. You could
use a helper column with a formula like
=TEXT(A1,"MMM-YY")
and import this row as heading row.

--
Regards
Frank Kabel
Frankfurt, Germany

Hi
are your Excel columns real date values (just formated as 'MMM-YY')?

--
Regards
Frank Kabel
Frankfurt, Germany

Yes, the Excel column titles are formatted as date, you can see the
attached file, When linked to ACCESS the date title couldn't be
recognize by ACCESS and changed to F1,F2,F3......(May be "F"
represents "fields"), When I format the date title to "Text" , the
date value change to Numbers!...how can I linked excel table with
date title to ACCESS correctly?

 

[Amolin]

why the following is not work?

the first report "syntax error"; the second report report "applicatio
define or object define error..."
the difference is 'MMM-YY' and "'MMM-YY"

Workbooks("MRS.xls").Worksheets("MRS").Cells(1, intMaxCol
1).FormulaR1C1 = "=TEXT(RC[ -15],"MMM-YY")"


Workbooks("MRS.xls").Worksheets("MRS").Cells(1, intMaxCol
1).FormulaR1C1 = "=TEXT(RC[ -15],'MMM-YY')"

Thank you for any help...

*Hi
problem is, Excel stored these values as n umbers internally. Yo
could
use a helper column with a formula like
=TEXT(A1,"MMM-YY")
and import this row as heading row.

--
Regards
Frank Kabel
Frankfurt, Germany

Hi
are your Excel columns real date values (just formated a 'MMM-YY')?

--
Regards
Frank Kabel
Frankfurt, Germany

Yes, the Excel column titles are formatted as date, you can se the
attached file, When linked to ACCESS the date title couldn't be
recognize by ACCESS and changed to F1,F2,F3......(May be "F"
represents "fields"), When I format the date title to "Text" the
date value change to Numbers!...how can I linked excel table with
date title to ACCESS correctly?

 

[Jamie Collins]

when linking EXCEL to ACCESS, the date format excel column tilte
in ...

are your Excel columns real date values (just formated as 'MMM-YY')?

I think your column headers are formatted as dates (Frank: the OP is
talking about column headers rather than data values).

You may see May-04 but the underlying value is 38108 and that is not
a valid column name for Jet. When Jet finds an invalid or non-existent
column headers it substitutes its own names F1, F2, F3 and so on.

If you format your column header row as text (will change the values
to 38108 etc) and re-type the headers as May-04 etc then the column
names should be valid and recognized by Jet.

Jamie.

--

 

[Amolin]

How can I change the underlying value ( e.g. 38108) to "May-04"
By function ?

Thank you.

I think your column headers are formatted as dates (Frank: the OP is
talking about column headers rather than data values).

You may see May-04 but the underlying value is 38108 and that is not
a valid column name for Jet. When Jet finds an invalid o
non-existent
column headers it substitutes its own names F1, F2, F3 and so on.

If you format your column header row as text (will change the values
to 38108 etc) and re-type the headers as May-04 etc then the column
names should be valid and recognized by Jet.

Jamie.

-- [/B

 

[Jamie Collins]

Amolin wrote ...

How can I change the underlying value ( e.g. 38108) to "May-04"
By function ?

Using VBA in the VBE Immediate Window:

If you data looks like this:

Sheet1.Range("A1:C1").Value = Array("May-04", "Jun-04", "Jul-04")

i.e. formatted as dates, then this to format as text (best to format
the entire row if possible):

Sheet1.Range("1:1").NumberFormat = "@"

but doing so converts to the underlying date value (a Double). This to
converts the numeric values to the text format of your choice:

For Each rng in Sheet1.Range("A1:C1").Cells : rng.Value =
Format$(rng.Value, "mmm-yy") : Next

Jamie.

--

 

[Jamie Collins]

Amolin wrote ...

How can I change the underlying value ( e.g. 38108) to "May-04"
By function ?

Using VBA in the VBE Immediate Window:

If you data looks like the following:

Sheet1.Range("A1:C1").Value = Array("May-04", "Jun-04", "Jul-04")

i.e. formatted as dates, then the following formats the *cells* as
text (best to format the entire row if possible):

Sheet1.Range("1:1").NumberFormat = "@"

But doing so converts the date *values* to their underlying numeric
values (of type Double). The following converts the numeric values to
text values using the format of your choosing:

For Each rng in Sheet1.Range("A1:C1").Cells : _
rng.Value = Format$(rng.Value, "mmm-yy") : _
Next

Jamie.

--