VBA CSV File Import Date Issue

[Katy Jordan]

Hi, i have a code which imports CSV file data into excel, the code imports the data successfully but the dates in the date column change to US format, not all but most of them, how can i get around this.

Sample CSV Data

<TABLE style="WIDTH: 129pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=171 border=0 x:str><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=66 height=17>Date</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 79pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=105>Amount</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="43012">4-Oct-17</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="9976364"> 9,976,364.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="42798">4-Mar-17</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="11772997"> 11,772,997.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40847">31-Oct-11</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="1884984"> 1,884,984.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="44196">31-Dec-20</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="-20000000">- 20,000,000.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="41125">4-Aug-12</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="-250000000">-250,000,000.00 </TD></TR></TBODY></TABLE>

rngFile = C:\Test\RG

This is the code i have

Sub Test()
 
Dim strFileName          As String
Dim lstRow               As Long
Dim lstRowSrc            As Range
Dim lstSrcCellRwNum      As Long
Dim shtDst               As Excel.Worksheet
Dim shtSrc               As Excel.Worksheet
Dim wkbThis              As ThisWorkbook
Dim wkbSrc               As Workbook
 
strFileName = Range("rngFile") & ".csv"
    If Dir(strFileName) = "" Then
            MsgBox prompt:="The File does not exist", _
            Buttons:=vbOKOnly + vbInformation
        GoTo ExitRoutine
      Else
         Set wkbSrc = Workbooks.Open(strFileName)
         Set shtSrc = wkbSrc.Worksheets(1)
         Set wkbThis = ThisWorkbook
         Set shtDst = wkbThis.Sheets("Test")
    End If
     
    With shtSrc
        Set lstRowSrc = .Cells(.Rows.Count, "A").End(xlUp)
                lstSrcCellRwNum = lstRowSrc.Row
         With .Range("A2:M" & lstSrcCellRwNum)
             .Copy
             shtDst.Range("A6").PasteSpecial xlPasteValues
         End With
    End With
End Sub

 

[Katy Jordan]

Xenou, the format in Text file is dd/mm/yyyy

This is hatchet job but should work:

Dim iLineCount As Long  '//Add this to your variable declarations
While Not EOF(1)

    Line Input #1, WholeLine
[COLOR=Blue]    iLineCount = iLineCount + 1
    If iLineCount > 4 Then[/COLOR]
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
[COLOR=Blue]    End If[/COLOR]

Wend

I should still like to know what format the text file dates are in. It's really impossible to give good advice otherwise. Your dates in post 17, by the way, look fine. All you should really need to do is format the cells in Excel (a rather simple adjustment, whether in the code or after it finishes)

 

[Katy Jordan]

Thanks that worked. How do i change the active cell to A6, so when the code imports it imports to A6?

Also when importing and then manually doing Text to columns to Col A separated by Comma, the dates are fine but then i record my step and add to the below Code those dates which are highlighted are reverted back to MDY format, why does this happen?

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

This is hatchet job but should work:

Dim iLineCount As Long  '//Add this to your variable declarations
While Not EOF(1)

    Line Input #1, WholeLine
[COLOR=Blue]    iLineCount = iLineCount + 1
    If iLineCount > 4 Then[/COLOR]
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
[COLOR=Blue]    End If[/COLOR]

Wend

I should still like to know what format the text file dates are in. It's really impossible to give good advice otherwise. Your dates in post 17, by the way, look fine. All you should really need to do is format the cells in Excel (a rather simple adjustment, whether in the code or after it finishes)

 

[xenou]

Use the Date() function (XL) or DateSerial() function (VBA) to create the dates if you have the d/m/y in separate columns.