Date format XL to CSV

[Ayato]

Hello @ll,

I have an excel file with a date using the format yymmdd,
when I save this file to .csv my date is now yyyymmdd

How can keep the same date format when saving to .csv?

Please advise,

Thx

Ayato

 

[RobBroekhuis]

I can't reproduce your problem. If it's important enough, you can create your own .csv save macro, using VBA's file handling commands. Then you can apply whatever formatting you'd like. But first I would describe the details of your problem a little more, because the built-in capability really should be able to do this properly.
Rob

 

[Nikita6003]

Hiya Rob

thread705-421274 has some more info (crosspost in Access forum)

Ayato, as Rob sugests you could build your own csv creator - this'll give you full control of how (& what part) you export your data

Cheers
Nikki

ps - Rob this gives me an opportunity to say thanks for all the tips ... some have come in v. useful indeed

 

[RobBroekhuis]

This may or may not be related to a problem I reported some time ago (thread707-406809) where Excel would mysteriously (and temporarily) convert a chart axis date format to a four-digit year. Again, if it's important enough, there are ways around it.
Rob

 

[Ayato]

Hello both,

Basically that's what I am doing, the structure is in fact a bit more complex.

I have 3 files: 1 databook (main file), 1 template (yymmdd.xls) and 1 refresh file.

I use the refresh file that will open databook and close it, then open the template file which contains only links to the databook and then save that template to csv (and not alter the template)

Everything works perfect, except that my customer wants his date like (yymmdd), I can set the format in the databook or in the template but when I expotr to csv it goes to yyyymmdd (and not when I do it manualy)

??????

Ayato

 

[RobBroekhuis]

This may sound trivial, but have you recorded the macro while doing it manually, to see if there is any difference, however small, between the generated code and your macro?
Rob

 

[Ayato]

Yes of course no difference at all !!!

Btw, the date is a formula, TODAY()-1, but I have tried to paste values in macro before saving to csv (no result)

Is possible that the regional settings of the machine are involved ?

regards,


Ayato

 

[Ayato]

HO HO HO

PROBLEM SOLVED !!!!!

THX to Nikki you saved me again )


regards from the Netherlands

Ayato

 

[Nikita6003]

yw ...

which of the solutions did you use ? (useful for other ppl perhaps ;-))

And groeten uit Holland to u 2

Cheers
Nikki

 

[RobBroekhuis]

If all else fails, you can use the following sub to create a duplicate sheet of just text values, export that, and delete the sheet afterwards.

Sub MakeTextSheet()
Dim Sh As Worksheet, cell As Range
Set Sh = ActiveSheet
ActiveSheet.Copy ActiveSheet
For Each cell In Sh.UsedRange
Range(cell.Address) = "'" & cell.Text
Next cell
End Sub

Rob