help with dates and csv file.

[avishain]

hello there,

im using excel 2007 and i got a sheet with a vba code.

i got 2 coulomns with dates ("w","v") in format (dd/mm/yyyy).


once i hit the macro - the xlsx turns into csv file and being saved .

when i open the csv file i notice that the date format has been changed to mm/dd/yyyy.


how can i save the original format (dd/mm/yyyy) and prevent from the csv file changing it to mm/dd/yyyyy


thanks

 

[FishingIsMoreFun]

Interesting date format...

you should post the code.

 

[avishain]

Interesting date format...

you should post the code.

the code:

Sub saveavi()
Dim lr, m
Set m = Nothing
If MsgBox("save"? vbYesNo + vbQuestion) = vbNo Then Exit Sub

lr = Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Set m = Intersect(Range("1:" & lr), Range("o,v:w")).SpecialCells(xlCellTypeBlanks)
If Not m Is Nothing Then

Range("O:O,V:V,W:W").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
m.Interior.ColorIndex = 3

MsgBox "hey there "

Cells(1, 1).Select

Exit Sub

End If


Application.Goto Reference:="R100000C1"
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear
Application.Goto Reference:="R1C1"


ThisWorkbook.SaveAs "c:/save/" & Environ("Username") & "_" & Format(Now, "ddmmyyyyhhmm") & ".csv", xlCSV
ThisWorkbook.Close False
End Sub

 

[FishingIsMoreFun]

Your format is "ddmmyyyyhhmm".

You could try "dd/mm/yyyy hh:mm"

or if you do not want any charcters

"dd mm yyyy hh:mm"

Your current code would return "15/09/2011" as "150920110000"

Also check your Excel date settings or system settings, =now() may be returning the American style date by default.

 

[FishingIsMoreFun]

Additionally try changing the format as a test and see if the code is actually formatting the output at all.

Good chance it isn't.

 

[avishain]

Your format is "ddmmyyyyhhmm".

You could try "dd/mm/yyyy hh:mm"

or if you do not want any charcters

"dd mm yyyy hh:mm"

Your current code would return "15/09/2011" as "150920110000"

Also check your Excel date settings or system settings, =now() may be returning the American style date by default.

i think you missunderstood the problem.

regarding my format (ddmmyyyyhhmm)- its only affecting the file name...and everything is ok with the name of the csv file.

my problem located inside the CSV file in columns "w","v"...i see dates with a wrong format (mm/dd/yyyy instead of dd/mm/yyyy).

so again,the problem is in the file and has nothing to do with the file name..

 

[avishain]

ok i found the solution...


ActiveWorkbook.SaveAs Filename:="C:\Path\to\folder\Book1.csv", FileFormat:=xlCSV, CreateBackup:=False, local:=True
End Sub



i just added the "local:=true".


thanks