Visual Basic 4 / 5 / 6 RSS Visual Basic 4 / 5 / 6 RSS

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

Reply

•
•
Join Date: Jul 2007
Posts: 113
Reputation: tgifgemini is an unknown quantity at this point 
Solved Threads: 0
tgifgemini tgifgemini is offline Offline
Junior Poster

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

 
0
  #1
Jul 6th, 2007
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) 
  1. Columns("A:A").Select
  2.     xlApp.Selection.NumberFormat = "###"
  3.     xlApp.Selection.Columns.AutoFit
<==This works fine.

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. Columns("L:L").Select
  2.     xlApp.Selection.NumberFormat = "mm/dd/yyyy"
  3.     xlApp.Selection.Columns.AutoFit
<==This does not work.
Thanks.
tgifgemini.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2007
Posts: 166
Reputation: AV Manoharan is an unknown quantity at this point 
Solved Threads: 9
AV Manoharan AV Manoharan is offline Offline
Junior Poster

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

 
0
  #2
Jul 7th, 2007
•
•
•
•
Originally Posted by tgifgemini View 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) 
  1. Columns("A:A").Select
  2.     xlApp.Selection.NumberFormat = "###"
  3.     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.
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2007
Posts: 113
Reputation: tgifgemini is an unknown quantity at this point 
Solved Threads: 0
tgifgemini tgifgemini is offline Offline
Junior Poster

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

 
0
  #3
Jul 9th, 2007
Thanks for your input. I will re-run my module with "DateFormat" and get back to you.
Have a wonderful day.
tgifgemini
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2007
Posts: 113
Reputation: tgifgemini is an unknown quantity at this point 
Solved Threads: 0
tgifgemini tgifgemini is offline Offline
Junior Poster

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

 
0
  #4
Jul 9th, 2007
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) 
  1. 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) 
  1. xlWksht.Range("G:G,H:H,K:K,L:L").Select
  2. 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
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2007
Posts: 166
Reputation: AV Manoharan is an unknown quantity at this point 
Solved Threads: 9
AV Manoharan AV Manoharan is offline Offline
Junior Poster

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

 
0
  #5
Jul 10th, 2007
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2006
Posts: 848
Reputation: QVeen72 is on a distinguished road 
Solved Threads: 120
QVeen72's Avatar
QVeen72 QVeen72 is offline Offline
Practically a Posting Shark

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

 
0
  #6
Jul 10th, 2007
Hi tgif,

use this Code:

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


Regards
Veena
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2007
Posts: 113
Reputation: tgifgemini is an unknown quantity at this point 
Solved Threads: 0
tgifgemini tgifgemini is offline Offline
Junior Poster

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

 
0
  #7
Jul 10th, 2007
Thanks a million. After much tweaking, your code worked.
Have a great day.
tgifgemini.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the Visual Basic 4 / 5 / 6 Forum
Thread Tools Search this Thread



* 6 429 2007 access activex add age application basic beginner birth bmp calculator cd cells.find click client code college component connection connectionproblemusingvb6usingoledb copy creat ctrl+f data database datareport date delete dissertations dissertationthesis dissertationtopic edit error excel excelmacro file filename form hardware header iamthwee image inboxinvb internetfiledownload keypress label listbox listview liveperson login looping machine microsoft movingranges number objectinsert open oracle password prime program prompt range-objects readfile reading record refresh remotesqlserverdatabase report save search sendbyte sites sort sql sql2008 sqlserver subroutine tags textbox time urldownloadtofile vb vb6 vb6.0 vba visual visualbasic visualbasic6 web window windows
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC