ADODB Excel Database - Date Format Issue

Question

I am currently using the below script below to retrieve data from another Excel workbook. I retrieve the proper date in proper format. The problem occurs when the workbook that i am querying is "OPEN" the retrieved data "DATE" returns a NUMBER "49802"(something like that) but as soon as i close the workbook that i am querying the returned "DATE" become normal format.

Sub Pull_Data_from_Excel_with_ADODB()

Dim cnStr As String
Dim rs As ADODB.Recordset
Dim query As String
Dim var1
Dim var2
var1 = Now() - 1
var2 = Now()
Dim fileName As String
fileName = "C:\Signin-Database\DATABASE\Signin-Database.xlsm"

cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
       "Data Source=" & fileName & ";" & _
       "Extended Properties=Excel 12.0"


query = "SELECT * FROM [Sheet1$D:H] WHERE [Time_in] < '" & var2 & "' AND [Time_in] >  '" & var1 & "' AND [Time_out] is null"

Set rs = New ADODB.Recordset
rs.Open query, cnStr, adOpenUnspecified, adLockUnspecified

Cells.Clear
Range("A2").CopyFromRecordset rs

Dim Cell As Range, i As Long
With Range("A1").CurrentRegion
For i = 0 To rs.Fields.Count - 1
    .Cells(1, i + 1).Value = rs.Fields(i).Name
Next i
.EntireColumn.AutoFit





End With

Answer 1

Try closing your recordset and see if that "makes" the dates changes to what they should be.

After you autofit command

.EntireColumn.AutoFit
rs.Close
Set rs = Nothing

If that doesn't work, try formatting the columns that have a date.

ws.Range("A:A").NumberFormat = "yyyy-mm-dd"
ws.Range("E:E").NumberFormat = "yyyy-mm-dd"

If you have a header row and you want to not format that you can define start and end rows in your range

Dim lRow as Long
lRow = ws.UsedRange.Rows.count

ws.Range("A2:A" & lRow).NumberFormat = "yyyy-mm-dd"
ws.Range("E2:E" & lRow).NumberFormat = "yyyy-mm-dd"

Answer 2

Excel stores its date values as a number- the integral part is the number of days counting up from January 1900, and the fractional part is the hours. So for example, 49802.25 would be 5/7/2036 at 6am. So, the issue is likely due to cell formatting which could be corrupted by the .CopyFromRecordSet method. I have had this problem in a few of my workbooks too, where if it is pulling a date field from Sql it will affect the cell formatting on other worksheets.

Excel table loses number formats when data is copied from ADODB recordset

https://dutchgemini.wordpress.com/2011/04/21/two-serious-flaws-with-excels-copyfromrecordset-method/ (flaw #2 on this one)

One workaround that worked for me is to have your macro format the effected cells back to the correct format, which can be done using the .NumberFormat method.

What are .NumberFormat Options In Excel VBA?