Imported access file(date values) into excel sprdsheet converts to numbers(integer)

[Giftx]

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

[dglienna]

Record a macro in Excel, and see what needs to be done.

[RobDog888]

Make sure your columns are not already formatted with an incorrect formatting type for what you are expecting.

Since you are using the .CopyFromRecordset method there isnt a whole lot you can do because its done internally and a macro wont show its process.

You can do like you have done and code in the proper formatting of the columns.

I would go with this code instead though in order to avoid using the Selection method in an effort to improve resource useage and speed.

xlApp.YourWorkbookObject.Sheets("Sheet1").Columns("A:A").NumberFormat = "###"
xlApp.YourWorkbookObject.Sheets("Sheet1").Columns("A:A").AutoFit

xlApp.YourWorkbookObject.Sheets("Sheet1").Columns("L:L").NumberFormat = "mm/dd/yyyy"
xlApp.YourWorkbookObject.Sheets("Sheet1").Columns("L:L").AutoFit

[Giftx]

Thanks for your imput. I'll try it out on Monday.
Meanwhile, you all enjoy your weekend.
GiftX.

[Giftx]

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

[dglienna]

Do your cells look like this?

123107 7/8/2007 112206 8/8/2007

I had no problem formatting that as Date