Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Unsolved
Views: 5132

Jul 6th, 2007

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

Expand Post »

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:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
Columns("A:A").Select
    xlApp.Selection.NumberFormat = "###"
    xlApp.Selection.Columns.AutoFit
Columns("A:A").Select
    xlApp.Selection.NumberFormat = "###"
    xlApp.Selection.Columns.AutoFit

<==This works fine.

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
Columns("L:L").Select
    xlApp.Selection.NumberFormat = "mm/dd/yyyy"
    xlApp.Selection.Columns.AutoFit
Columns("L:L").Select
    xlApp.Selection.NumberFormat = "mm/dd/yyyy"
    xlApp.Selection.Columns.AutoFit

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

Similar Threads

How to write files to an Excel application in Visual Basic 4 / 5 / 6

tgifgemini

Reputation Points: 22

Solved Threads: 0

Junior Poster

Offline

113 posts
since Jul 2007

Permalink

Jul 7th, 2007

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

Click to Expand / Collapse Quote originally posted by 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:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
Columns("A:A").Select
    xlApp.Selection.NumberFormat = "###"
    xlApp.Selection.Columns.AutoFit
Columns("A:A").Select xlApp.Selection.NumberFormat = "###" xlApp.Selection.Columns.AutoFit
<==This works fine.
(Toggle Plain Text)
Columns("L:L").Select
    xlApp.Selection.NumberFormat = "mm/dd/yyyy"
    xlApp.Selection.Columns.AutoFit
Columns("L:L").Select xlApp.Selection.NumberFormat = "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

AV Manoharan

Reputation Points: 10

Solved Threads: 9

Junior Poster

Offline

166 posts
since Jun 2007

Permalink

Jul 9th, 2007

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

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

tgifgemini

Reputation Points: 22

Solved Threads: 0

Junior Poster

Offline

113 posts
since Jul 2007

Permalink

Jul 9th, 2007

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

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:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
xlApp.xlWbk.Sheets("Sheet1").Columns("H:H").DateFormat = "mm/dd/yyyy"
xlApp.xlWbk.Sheets("Sheet1").Columns("H:H").DateFormat = "mm/dd/yyyy"

And I also tried this code:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
xlWksht.Range("G:G,H:H,K:K,L:L").Select
Selection.DateFormat = "mm/dd/yyyy"
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

tgifgemini

Reputation Points: 22

Solved Threads: 0

Junior Poster

Offline

113 posts
since Jul 2007

Permalink

Jul 10th, 2007

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

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

Last edited by AV Manoharan; Jul 10th, 2007 at 2:24 am.

AV Manoharan

Reputation Points: 10

Solved Threads: 9

Junior Poster

Offline

166 posts
since Jun 2007

Permalink

Jul 10th, 2007

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

Hi tgif,

use this Code:

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

Regards
Veena

QVeen72

Reputation Points: 84

Solved Threads: 140

Posting Shark

Offline

923 posts
since Nov 2006

Permalink

Jul 10th, 2007

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

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

tgifgemini

Reputation Points: 22

Solved Threads: 0

Junior Poster

Offline

113 posts
since Jul 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: How to change the currect recordset in a Form

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: .:: Web Login Help ::.

DaniWeb Message

Cancel Changes

User Name
Password