Date Format, CSV issue

[Chris Acheson]

Hi Guys...

I hope your well.

Ive written some code, however this is a csv file (not sure if that makes any difference), I want to change the format of the date as per below, save the file and close, I then upload it into an inhouse system.

Please Login or Register  to view this content.

The system kicks back advising the date is in the wrong format, and when I re visit the above example file it has reverted back to a diifferent format......

This is bypassed initially as the coded does this and then closes file, so when I then go back and open, I can manually adjust the date, and upload which is accepted fine.

I cannot think why my code does not perform this simple task, any ideas people?

Thank you kindly.

[Bernie Deitrick]

When you open a csv file, the values imported into the cells are really just text initially, and then Excel figures out what they are and applies what it thinks is the best format. Since column R is dates, the best format for dates - at least for Excel - is the default system format. If you want to save the formatting with the file, you cannot just use Activeworkbook.Save, which saves the file as a CSV file - you need to save the activeworkbook as an Excel file, say with

ActiveWorkbook.SaveAs "Example.xlsx", 51

Then the formatting will stick.

[Chris Acheson]

Hey Bernie..... thanks for your feedback....

Can I just ask a question on the back of this....

I have to have the workbook in csv Format as the in house system only accepts csv files..... so by me saving as per your advise above it will be in the wrong format, and I still face the same problem....

Also, whats the '51' at the end of the code above? im intrigued.... im very new to coding all together but eager to learn / understand...

thank you.

[Bernie Deitrick]

The 51 just tells Excel the file format - which is actually a defined constant (xlOpenXMLWorkbook) that means "save the file as an .xlsx file". I just have a harder time typing (and remembering) xlOpenXMLWorkbook than I do with 51.

You could try exporting the values in the workbook into a CSV, but with better control over the values in the string - so try this:

Please Login or Register  to view this content.

[Chris Acheson]

Hey Bernie... Hope you had a cool weekend... just looking at the code you mentioned above ...

Im just having a play around with it,

Please Login or Register  to view this content.

Is in red, as in an error.... I do not see anything wrong with this line of code, however im very new to vba.... do you know what the error could possibly be?

Thanks mate

[romperstomper]

When you save the CSV file, the dates should be stored as they were displayed. However to verify this you should open the file in Notepad or another text editor, not Excel, which will reformat the data automatically when it reopens it.

[Bernie Deitrick]

You need to put a carriage return between .Column and End With :


EndCol = .Cells(.Cells.Count).Column
End With