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

Frank Kabel wrote:
[b]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?


---
Message posted from http://www.ExcelForum.com/

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


Frank Kabel wrote:
[b]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?


---
Message posted from http://www.ExcelForum.com/

why the following is not work?

the first report "syntax error"; the second report report "application
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...








Frank Kabel wrote:
*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


Frank Kabel wrote:
[b]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?


---
Message posted from http://www.ExcelForum.com/ *


---
Message posted from http://www.ExcelForum.com/

when linking EXCEL to ACCESS, the date format excel column tilte
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?

"Frank Kabel" wrote 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.

--

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 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.

-- [/b]


---
Message posted from http://www.ExcelForum.com/

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.

--

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.

--