Date fields imported from access file to excel spreadsheet is converting to number

[tgifgemini]

Good morning everyone - I am using VB6.
I need your assistance. I used the "CopyFromRecordset" function to populate my spreadsheet. Some of the fields in the access file has date values and some has number values (integers), but after importing into the spreadsheet, the numbers convert to date values(1/1/1900) and the date fields displays number values in the columns that was supposed to display dates.

I have used the code below to try and fix the problem and the numbers displays correctly, but the date values are still displaying numbers (integers).
My code:

Columns("A:A").Select
    xlApp.Selection.NumberFormat = "###"
    xlApp.Selection.Columns.AutoFit

<==This works fine.

Columns("L:L").Select
    xlApp.Selection.NumberFormat = "mm/dd/yyyy"
    xlApp.Selection.Columns.AutoFit

<==This does not work.
Thanks.
tgifgemini.

[AV Manoharan]

Good morning everyone - I am using VB6. I need your assistance. I used the "CopyFromRecordset" function to populate my spreadsheet. Some of the fields in the access file has date values and some has number values (integers), but after importing into the spreadsheet, the numbers convert to date values(1/1/1900) and the date fields displays number values in the columns that was supposed to display dates. I have used the code below to try and fix the problem and the numbers displays correctly, but the date values are still displaying numbers (integers). My code:

Columns("A:A").Select
    xlApp.Selection.NumberFormat = "###"
    xlApp.Selection.Columns.AutoFit

<==This works fine.

Columns("L:L").Select
   <strong> xlApp.Selection.NumberFormat</strong> = "mm/dd/yyyy"
    xlApp.Selection.Columns.AutoFit

<==This does not work. Thanks. tgifgemini.

I have bold faced your problem. The methode of the selection object is wrong. In the earlier case it is correct as it returns the number, where as in the second case it has to be dateformat

AV Manoharan

[tgifgemini]

Thanks for your input. I will re-run my module with "DateFormat" and get back to you.
Have a wonderful day.
tgifgemini

[tgifgemini]

Good morning.

I tried using "DateFormat" in the code below, but I got error msg:
"Object does not support this property".

First, I tried this code:

xlApp.xlWbk.Sheets("Sheet1").Columns("H:H").DateFormat = "mm/dd/yyyy"

And I also tried this code:

xlWksht.Range("G:G,H:H,K:K,L:L").Select
Selection.DateFormat = "mm/dd/yyyy"

In all the codes above, I used both "DateFormat" and "NumberFormat", but the date fields in access file still returns numbers in my spreadsheet.
tgifgemini

[AV Manoharan]

I have answered to you as below:

I have bold faced your problem. The methode of the selection object is wrong. In the earlier case it is correct as it returns the number, where as in the second case it has to be dateformat

AV Manoharan

Now excel has a uniqe way of importing everything into it as GENERAL format. But a date format of excel is little tricky. What the number that represent a date in Acceess need not be the number that represent a date in excell. So the best way is while drawing the date field from the ACCESS you dfraw it as a Characte Date NOT as a Number form of date. Then I think it has to work.

AV Manoharan

[QVeen72]

Hi tgif,

use this Code:

xlApp.Range("L:L").NumberFormat = "mm/dd/yyyy"

Regards
Veena

[tgifgemini]

Thanks a million. After much tweaking, your code worked.
Have a great day.
tgifgemini.