PDA

View Full Version : Cell format changing when file open manually and VBA



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

Kenneth Hobs
10-09-2013, 11:41 AM
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.

dawidtokyo
10-09-2013, 01:15 PM
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!

Aflatoon
10-09-2013, 02:44 PM
You might try just adding the Local:=True argument to the workbooks.open statement.