Jay Williams
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
So are there formats set in Excel on the columns of the other sheets?
ASKER