dawidtokyo
10-09-2013, 10:07 AM
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:
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!!!
10676
Regards
Dawid
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:
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!!!
10676
Regards
Dawid