Why would Excel column change to date format after CopyFromRecordSet paste on a different sheet?

Classic Excel to Access refresh and turnaround.  Code runs fine and the data goes back in 4 different sheets just like it's supposed to, but after the last paste in the "WorkingList" sheet, the DaysPastPickDate column on the "PickList" sheet changes to a date format. Actual values are still there, but displaying dates in 1900. Here's the code:
Public Sub RefreshPicks()
    On Error Resume Next
    Dim ObjXL As Excel.Application
    Set ObjXL = GetObject(, "Excel.Application")
        If Not (ObjXL Is Nothing) Then
            Debug.Print "Closing XL"
            ObjXL.Application.DisplayAlerts = False
            Set ObjXL = Nothing
            Debug.Print "XL not open"
        End If
    Dim sPath As String, xlFile As String
    sPath = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\Refresh\"
    xlFile = Dir(sPath & "*.xlsm")
    Dim XL As Excel.Application, wbTarget As Workbook
    Dim qdfResults As QueryDef
    Dim qdfResults2 As QueryDef
    Dim qdfResults3 As QueryDef
    Dim qdfResults4 As QueryDef
    Dim rsResults As DAO.Recordset
    Dim rsResults2 As DAO.Recordset
    Dim rsResults3 As DAO.Recordset
    Dim rsResults4 As DAO.Recordset
    Set XL = New Excel.Application
    Set wbTarget = XL.Workbooks.Open("G:\XE_ECMs\__MOST_REPORT\Reservation Data\PickListTemplate.xlsm")
    Set qdfResults = CurrentDb.QueryDefs("OpenRsrvtnsQ")
    Set rsResults = qdfResults.OpenRecordset()
    Set qdfResults2 = CurrentDb.QueryDefs("TrackingQ")
    Set rsResults2 = qdfResults2.OpenRecordset()
    Set rsResults3 = CurrentDb.OpenRecordset("FactoryDeletedT")
    Set qdfResults4 = CurrentDb.QueryDefs("WorkingListQ")
    Set rsResults4 = qdfResults4.OpenRecordset()
    Debug.Print xlFile
'Import and delete the data file
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM ProcessingT"
    DoCmd.RunSQL "DELETE * FROM WorkingListT"
    DoCmd.TransferSpreadsheet acImport, 10, "ProcessingT", sPath & xlFile, True, "PickList!A2:V5000"
    DoCmd.TransferSpreadsheet acImport, 10, "WorkingListT", sPath & xlFile, True, "WorkingList!A1:V5000"
    DoCmd.OpenQuery "RemoveProcessingDuplicatesQ"
    DoCmd.OpenQuery "UpdatePicksQ"
    DoCmd.OpenQuery "UpdateClosePicksQ"
    DoCmd.OpenQuery "UpdateWorkingListQ"
    DoCmd.OpenQuery "CloseRsrvtnsInWHtransTQ"
    DoCmd.OpenQuery "PurgeClosedInProcessingTQ"
        If DCount("*", "FactoryDeletedQ") > 0 Then
            DoCmd.OpenQuery "FactoryDeletedQ"
            DoCmd.OpenQuery "CloseFactoryDeletedQ"
        Else: End If
        If DCount("*", "TrackedTodayQ") = 0 Then
            Call Snapshot 'Appends TrackingT data
        Else: End If
    XL.Visible = True
    wbTarget.Sheets("PickList").Range("A3").CopyFromRecordSet rsResults
    Set rsResults = Nothing
    XL.Run "SetPickListRowHeight"
    wbTarget.Sheets("Tracking").Range("A2").CopyFromRecordSet rsResults2
    Set rsResults2 = Nothing
    wbTarget.Sheets("FactoryDeleted").Range("A3").CopyFromRecordSet rsResults3
    Set rsResults3 = Nothing
    wbTarget.Sheets("WorkingList").Range("A2").CopyFromRecordSet rsResults4
    Set rsResults4 = Nothing
    'XL.Run "SaveSendResults"
    Set wbTarget = Nothing

    DoCmd.SetWarnings True
    XL.Run "SavePickSheet"

End Sub

Open in new window

Jay WilliamsOwnerAsked:
Who is Participating?
the DaysPastPickDate column on the "PickList" sheet changes to a date format. Actual values are still there, but displaying dates in 1900.

I think you are probably running afoul of not understanding how Access stores datetime.  Access uses a special kind of Double to store dates.  Zero day is 30-Dec-1899.  Excel's zero day is not the same, though.

If, in Access, you do a DateDiff (and one suspects that you are with DaysPastPickDate) and store it in a DateTime field, what you are actually storing is the Double value of the result of your DateDiff from Zero day  And so, in Excel, you are seeing days elapsed since Excel's zero day.

Go ahead and format DaysPastPickDate as dd-mmm-yyyy in Access
I'll give you dollars to doughnuts that you're seeing dates in 1899.

So you have a pretty pickle.  You want to store the data in Access as datetime and format the result as  d hh:mm but when you export to Excel, the datatype goes, but the format doesn't.

Easiest is to simply use VBA to format the column in Excel (I don't know which column! or format!)
wbTarget.Sheets("WorkingList").Range("A:A").NumberFormat = "d 'days' hh:mm 'hours'"
Jay WilliamsOwnerAuthor Commented:
Actually . . . no. The numbers are (+-) whole integers generated by a calculated field in an update query as part of the turnaround.  I did know about Access and Excel datetimes, but using DateDiff did not occur to me.  At least I'm not too far out; writing a macro to reformat the column was (so far, is) my plan "B."  Still curious about why it's acting that way, though.
The numbers are (+-) whole integers generated by a calculated field in an update query as part of the turnaround.
So the field data types in table that the data comes from are Long Integer?
With a name like 'DaysPastPickDate' I am thinking, no.
The numbers are (+-) whole integers generated by a calculated field
So there's a CInt() in there?  Or a Round(,0)  Because unless you specifically coerce the result to integer, you will leave some datetime-iness in there for Excel to notice.

If Excel really is getting straight coerced integers (and not things that are rounded & formatted to look like integers) it may not shift the Excel format to datetimes -- but if you haven't really purged it, Excel is going to try to guess what format you want.

And it may even be using the fieldname in that guess 'DaysPastPickDate'
If it is calculated in the query, and is CInt(), and still mucks up Excel, try naming the calculated field 'Gronk' and see if Excel gets integers.
Jay WilliamsOwnerAuthor Commented:
Ok, all good stuff, thanks!  I'll dig through it and let you know what shakes out.  what I didn't mention is that this all started when I added the "WorkingList" sheet to paste into.  Had been working fine until then.
So are there formats set in Excel on the columns of the other sheets?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.