Convert Column Dates to Text VBA

[Skybluekid]

Hi All,

I have in column D a list of dates:

[TABLE="width: 102"]
<colgroup><col></colgroup><tbody>[TR]
[TD]43728[/TD]
[/TR]
[TR]
[TD]43710.35417[/TD]
[/TR]
[TR]
[TD]43732[/TD]
[/TR]
[TR]
[TD]43689.62014[/TD]
[/TR]
[TR]
[TD]43698.44236[/TD]
[/TR]
[TR]
[TD]43727[/TD]
[/TR]
[TR]
[TD]43708.5[/TD]
[/TR]
[TR]
[TD]43699[/TD]
[/TR]
</tbody>[/TABLE]

I would like to convert these dates to Text and then format them as dd/mm/yyyy. I hear you cry "this is easy and why are you using VBA???"

Well this is part of a wider chain that copies data from on sheet to a Template. Once this template is complete, it is then exported to a CSV file to be uploaded.

I have used some code from VoG:

Set ColumnFormat = Range("D3:D" & Count)


For Each c In ColumnFormat
c.NumberFormat = "@"
c.Value = Format(c.Value, "dd/mm/yyyy")
Next c

Which does the trick, except when the file has been exported to CSV, it then reverts back to the date number format, as above rather than text. To Transfer the values to Template, I am copying the column and then pasting values

In the Template the Values have to be in the dd/mm/yyyy text format, that can not change.

I would very much welcome some pointers.

 

[Joe4]

Which does the trick, except when the file has been exported to CSV, it then reverts back to the date number format, as above rather than text. To Transfer the values to Template, I am copying the column and then pasting values

Are you sure? Where are you seeing this?

I see this happen quite a bit. What they do not understand is that opening a CSV file directly in Excel re-converts the values. And then if you re-save the file, you save those conversions.
Hence, if you want to see what is REALLY in a CSV file, you should NEVER use Excel to view it, as Excel does not simply open it, it also may convert the values.
If you really want to see what is in a CSV file after creating it, you should use a Text Editor (like NotePad, WordPad, or any 3rd party Text Editor) to view it.

One of my biggest pet peeves with Microsoft is that they decided that Excel should be the default program to open CSV files. That was a horrible decision that can lead to confusion and issues, since it does whatever field conversions it deems necessary. I always edit that and change the setting to use a Text Editor as the default program to view CSV files.