Cell format changing when file open manually and VBA

Hi guys,

I have a weird thing going on with my CSV files... When i open them manually the dates in column A are displayed correctly, but when i open the files with VBA macro the dates in the column A are all over the place (should be dd/mm/yyyy, but the dates are displayed in many different formats... yyyy/dd/mm, mm/dd/yyyy and so on...). Changing the date format doesn't help. Would love to know how too resolve that issue. Here is some of the VBA code:

Code:

---

Sub test()Dim file As Variant
Dim rng As Range
Dim rng2 As Range
Dim cell2 As Range
Dim cell As Range
Dim fname As String
Dim wrk As Workbook
Dim dt As String
Workbooks("devideJP.xlsm").Activate
Set rng = ActiveWorkbook.Sheets("devide").Range("A:A")
file = dir("C:\myfolder\")
  While (file <> "")
      If InStr(file, "") > 0 Then
            For Each cell In rng
            fname = cell.Value
            dt = cell.Offset(0, 8).Value
            Workbooks.Open ("C:\myfolder\" & fname & ".csv")
            Set rng2 = Range("A:A")
            For Each cell2 In rng2
            If cell2.Value = dt Then
            cell2.EntireRow.Select
            Range(Selection, Selection.End(xlUp)).Select
            With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
            End With
           
            End If
            Next
           
            Next
            End If
           
      Wend




End Sub

---

I'm attaching a sample file as well!
Thank you in advance!!!
Attachment 10676
Regards

Dawid

That file shows m/d/yyyy and general in column A whether I open it "manually" or by a macro. You might want to look into how your CSV files are being created.

Hi Kenneth,

Thank you for the reply. I fixed the dates with a macro and few formulas. Now running through all the csv files to fix them all. Cheers again!

You might try just adding the Local:=True argument to the workbooks.open statement.