asked on

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
            ObjXL.Workbooks.Close
            ObjXL.Quit
            Set ObjXL = Nothing
        Else
            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

'ClearProcessingT
    DoCmd.SetWarnings True
    XL.Run "SavePickSheet"
    Access.Quit

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Nick67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jay Williams

ASKER

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.

Nick67

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 Williams

ASKER

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.

Nick67

So are there formats set in Excel on the columns of the other sheets?