Date formatting on access export to excel

[nscotty_2000]

Hi, I have been searching through forums and cannot find an answer to my problem. I have all time/date settings on the machine set to dd/mm/yyyy.
I have "Short Date" as the format for all date fields in Access and have also set the MS Office default settings to dd/mm/yyyy. However when creating an excel file with the below code it reverts the date in cells back to mm/dd/yyyy. This is doing my head in so any help would be much appreciated. Thanks in advance.


Sub ExportFilesForImport()
Dim CurrentDate, CurrentFileName, Path
Dim xlObj As Object, xlFile, iCol, iRow

CurrentDate = Format(Now(), "yyyymdd-hhmmss")
CurrentFileName = "ePick Export File - " & CurrentDate & ".XLS"
Path = "C:\Independent Order Console\ePick Import Files\"
DoCmd.OutputTo acOutputQuery, "Sheet1", acFormatXLS, Path & CurrentFileName, False

xlFile = Path & CurrentFileName
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open xlFile
With xlObj

For iRow = 1 To .Sheets(1).usedrange.rows.Count
For iCol = 1 To .Sheets(1).usedrange.Columns.Count
.cells(iRow, iCol).Value = Replace(Replace(.cells(iRow, iCol), "DelivDate", "Deliv.Date"), "DelivDate", "Deliv.Date")
Next

Next
.activeworkbook.Save
End With
xlObj.Quit
Set xlObj = Nothing


End Sub

 

[MarkK]

Software like Access and Excel display dates using Windows settings. Check ControlPanel->RegionalSettings and set date formats there.

 

[nscotty_2000]

Cheers, but I have changed all the regional settings to dd/mm/yyyy already and it still reverts back to mm/dd/yyyy in the exported excel file?

 

[DCrake]

TRy dimming CurrentDate as a Date, leaving it blank means its a variant and allows Access to interpret it as it thinks not always as it should.

 

[nscotty_2000]

Thanks, but CurrentDate is only used to name the excel file. It's not used for the data inside the exported excel file?

 

[DCrake]

What is the replace code doing?

Have you tried opening a preformatted workbook and using CopyFromrecrodset method?

 

[nscotty_2000]

The replace code is updating a column header in the exported file. The header needs to be called "Deliv.Date" for import into another system but Access won't allow "Deliv.Date" as a Field name so it has to be replaced on export.

Can I use something like this to format that specific column? (Column F):

.Cells(F:F).NumberFormat = "mm/dd/yyyy"

I'm just not sure how to incorporate this into the above code to make it work?

The only other option is using a template file (as you suggested above) But that would mean a complete re-design of the database/code.

Cheers

 

[nscotty_2000]

Does anyone know how to imcorporate this into the above code?:

.Cells(F:F).NumberFormat = "mm/dd/yyyy"

 

[nscotty_2000]

Does anyone know how to imcorporate this into the above code?:

.Cells(F:F).NumberFormat = "mm/dd/yyyy"

 

[JANR]

would this work?

Sub ExportFilesForImport()
Dim CurrentDate, CurrentFileName, Path
Dim xlObj As Object, xlFile, iCol, iRow
CurrentDate = Format(Now(), "yyyymdd-hhmmss")
CurrentFileName = "ePick Export File - " & CurrentDate & ".XLS"
Path = "C:\Independent Order Console\ePick Import Files\"
DoCmd.OutputTo acOutputQuery, "Sheet1", acFormatXLS, Path & CurrentFileName, False
xlFile = Path & CurrentFileName
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open xlFile
With xlObj
    [COLOR=red].Columns("F:F").Select
    .Selection.NumberFormat = "mm/dd/yyyy"[/COLOR]
    
     For iRow = 1 To .Sheets(1).UsedRange.Rows.Count
        For iCol = 1 To .Sheets(1).UsedRange.Columns.Count
            .Cells(iRow, iCol).Value = Replace(Replace(.Cells(iRow, iCol), "DelivDate", "Deliv.Date"), "DelivDate", "Deliv.Date")
        Next
    Next
.ActiveWorkbook.Save
End With
xlObj.Quit
Set xlObj = Nothing
End Sub

JR

 

[nscotty_2000]

Thanks, but still no joy!

 

[boblarson]

You have to format the Excel Cells BEFORE you export it. I had a friend who found that if they exported the date as TEXT it would actually work where if they tried to export as date it wouldn't. You might take a look at that.

Or you could, instead of using DoCmd.OutputTo use my function here to send the data to Excel but modify it slightly so that it sets the format for the date cells first before the CopyFromRecordset.