Pasting Dates into Excel

[ejgb]

I have the code below which pastes the contents of a listview into Excel. A number of the fields are dates which are shown in the system short date format (dd/mm/yyyy).

The data is first copied to the clipbaord and then pasted. On viewing the spreadsheet, Excel converts those dates with a day less than 12 to american format (mm/dd/yyyy), i.e. 07/03/2004 (7th March 2004) becomes 03/07/2004 (3rd July 2004), and places the rest are formated as text.

However once completed, if I then just press Ctrl-V to paste the content of the clipboard again the dates are correctly formatted.

Private Sub Export()

On Error GoTo Err_Handler

Dim iHeaders As Integer
Dim iAnswer As Integer
Dim iData As Integer
Dim strCopy As String
Dim Export_Sheet As Object

'Copy Headers
strCopy = vbNullString
For iHeaders = 1 To lvwReport.ColumnHeaders.Count
strCopy = strCopy & lvwReport.ColumnHeaders(iHeaders).Text & vbTab
Next
strCopy = Mid(strCopy, 1, Len(strCopy) - 1) & vbCrLf

'Retrieve the data
iAnswer = MsgBox("Do you want to export the whole list?", vbYesNo, "Search Export")
For iData = 1 To lvwReport.ListItems.Count
If lvwReport.ListItems(iData).Selected Or iAnswer = vbYes Then
strCopy = strCopy & Replace(Replace(lvwReport.ListItems(iData).Text, Chr(13), " "), Chr(10), " ") & vbTab
For iHeaders = 2 To lvwReport.ColumnHeaders.Count
strCopy = strCopy & Replace(Replace(lvwReport.ListItems(iData).SubItems(iHeaders - 1), Chr(13), " "), Chr(10), " ") & vbTab
Next
strCopy = Mid(strCopy, 1, Len(strCopy) - 1) & vbCrLf
End If
Next

Clipboard.Clear
Clipboard.SetText strCopy


Set Export_Sheet = CreateObject("excel.application")
Export_Sheet.Visible = True
Export_Sheet.Workbooks.Add
Export_Sheet.ActiveSheet.Paste

Set Export_Sheet = Nothing

Exit Sub

Err_Handler:
MsgBox ("Excel is not installed on PC." & Chr(13) & "Open you default speadsheet and use the Paste conmmand")

End Sub

 

[ejgb]

Just to let everyone know, this is a bug with pasting dates into Execl programmable. This is only an issue when the regional short date format is set to dd/mm/yyyy.

Execl does not take into consideration the short date format that has been set, but automatically uses the american format (mm/dd/yyyy).