Date value from Excel becomes serial number

July 25, 2011 at 01:03:53
Specs: Windows 7
Dear all,

I write a program based on Visual Basic 2008 Express. The program connects to a Access MDB and opens a table as recordset via ADODB conection object. Then a date value from the excelsheet is going to add into the opened recordset table. However, I got the following error message

Error "Conversion from type 'Double' to type 'Date' is not valid"

I used Msgbox to catch up and find out the retreived value of date from Excel becomes a serial number of the date. how,can i retreive a date from excel instead of the serial number. Or convert back the serial number to a date value.

Thanks very much!!

Looking forward for reply
E Lau

Library referenced as follows
"Microsoft ActiveX Data Objects 2.8 Library"
"Microsoft ADO Ext. 2.8 for DDL and Security"
Microsoft Excel 14.0 Object Library

Start of Code:
Dim mdbRst As ADODB.Recordset
Dim xlsSht As Microsoft.Office.Interop.Excel.Worksheet

'defining field variables to hold the excel columns' values
Dim s_TypedBy As String
Dim d_TypedDate As Date

'define Pt as counter to loop down from the excel table rows
Dim Pt as Integer

'Open excel spreadsheet
xlsSht = ggxlsWbk.Worksheets(0)

'Open the Access table
mdbRst = New ADODB.Recordset
mdbRst.Open("NewTable", ggmdbCon, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdTable)

'Retreive row values from excel and add to Access Table
Pt = 1
It Pt > 100 Then Exit Sub
With xlssht
s_TypedBy = .Cells.Values2(Pt, 19)
d_TypedDate = .Cells.Values2(Pt, 20)
End With

'Add New to the Access Table
mdbRst("TypedBy").Value = s_TypedBy
mdbRst("TypedDate".Value = d_TypedDate

Pt = Pt + 1

mdbRst = Nothing
xlsSht = Nothing

End of code

See More: Date value from Excel becomes serial number

Report •

July 25, 2011 at 01:38:29
The serial number is the number of days since 1st January 1900.
Hope that helps.

Report •

July 25, 2011 at 01:40:30
System.Convert.ToDateTime(string) As Date

Report •

July 25, 2011 at 03:16:49
Thanks IROC-Z. clear explanation! I tried and it comes closer to the result. Still some problem

The date value in the excel spreadsheet "6/1/2011" which is month, day / year.

The retrieved value in VB is 40695

I changed the code for
"System.Convert.ToDateTime(string) As Date"

-- new code ----
Dim test_x as string 'Add this line

test_x = System.Convert.ToString(xlsSht.Cells.Value2(Pt, 21))
d_TypedDate = System.Convert.ToDateTime(test_x)

--- end code----

But it gives an error as "String was not recognized as a valid DateTime"

So I changed again to directly read from the cell value of the excel sheet

----- new code----
d_TypedDate = System.Convert.ToDateTime(xlsSht.Cells.Value2(Pt, 21))

-- end code

It gives an error "Invalid cast from Double to 'DateTime'."

The syntax is logical but the result is unlogical. The Msgbox reveals that the date number is retreived as "40695".

Any idea? Many thanks!

E Lau

Report •

Related Solutions

July 25, 2011 at 21:32:58
As IROC-Z stated, it's the number of days since the start of the year 1900. More or less. Unless it's Mac Office.
'Note: Gives wrong answer on Macs and dates before 1900.3.1
'It's an old Lotus 1-2-3 bug, you see
Dim excelEpoch As DateTime = New DateTime(1899, 12, 30)  
Dim someDate As DateTime = excelEpoch.AddDays(someValue)

How To Ask Questions The Smart Way

Report •

July 26, 2011 at 02:36:27
Thanks a lot! Razor2.3 It works to convert back to date and successfully insert into the recordset.

E Lau

Report •

Ask Question