Export Flexgrid/VBFlexgrid to Excel Americansing Dates

[logley]

I have some code that copies a column in a grid, be it a MSFlexgrid or a VBFlexgrid:

        With myGrid
            .Col = iCol
            .Row = 0
            .ColSel = iCol
            .RowSel = .Rows - 1
            Clipboard.SetText .Clip
        End With

And then pastes it into Excel

        With xlObject.ActiveWorkbook.ActiveSheet
            .Range(sTmp).Select
            .Paste
        End With

Any dates where the month is <12 Excel is converting the date to mm/dd/yy format instead of keeping dd/mm/yy format. So 01/07/24 (1-July-24), shows in Excel as 07/01/24 (7-Jan-24)

I have tried the following but does not help:

        If myGrid.ColDataType(iCol) = K_D Then
            xlObject.Columns(aCols(iCol) & ":" & aCols(iCol)).NumberFormat = "dd/MM/yyyy"
        End If

Strange thing is that if I paste the clipboard to notepad, it shows correctly (01/07/24), and then if I manually paste into the same Excel sheet, Excel again continues to show correctly (01/07/24), but the VB6 pasted column is messed up. Because manual pasting is working, I don't think the issue is a Regional Settings Issue (which is set to English (United Kingdom) dd/mm/yyyy).

I have tried to solve this issue several times over the last few months, trying different NumberFormat settings etc., before giving up and moving onto something else. The only way I always get it to work is by showing the date as dd-mmm-yy within the grid before doing the export, but this is a workaround and would like to fix this issue once and for all.

Any help would appreciated.

Lee.

[wqweto]

> . . . would like to fix this issue once and for all.

Obligatory xkcd for *the* correct way to write numeric dates: https://xkcd.com/1179/

cheers,
</wqw>

[SamOscarBrown]

Not sure why that is doing that...but why are you using clipboard? Why not just 'assign' the date to the cell.

Like this example (early Binding ex):

Private Sub Command3_Click()        Dim oEx As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oWS As Excel.Worksheet
        Set oEx = New Excel.Application
        Set oWB = oEx.Workbooks.Add
        Set oWS = oWB.Worksheets("Sheet1")
        MSFlexGrid1.Col = 0
        MSFlexGrid1.Row = 0
        oWS.Cells(1, 1).Value = MSFlexGrid1.Text  'grid text = "01/07/24"  (Produces same in excel spreadsheet)
        oEx.Visible = True
        Set oWS = oEx.ActiveSheet
        Set oWB = oEx.ActiveWorkbook
        oWB.SaveAs FileName:=App.Path & "\datesInExcelFromVB6.xlsx"
End Sub

[xiaoyao]

ado for setvalue on range()""A1

[bahbahbah]

One way that might work for you is:

    With xlSheet
        .Range("A:A").Select
        .Range("A:A").NumberFormat = "@" 'paste the data as text (not as a date)
        .Paste
        .Range("A:A").NumberFormat = "dd-mm-yyyy"
        .Range("A:A").TextToColumns DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        FieldInfo:=Array(1, xlDMYFormat) 'force Excel  to update the columns
    End With

Thanks to pgc01 for the TextToColumns solution.
https://www.mrexcel.com/board/thread...9/post-4278149

Before (using your code)


After: