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
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
s_TypedBy = .Cells.Values2(Pt, 19)
d_TypedDate = .Cells.Values2(Pt, 20)
'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