Sace as CSV date problem 1

[woodyinoz]

Hi all,

I'm currently running a simple script which saves the current Excel worksheet as a csv file.

Here is the code:

    ActiveWorkbook.SaveAs Filename:= _
    "J:\Projects\Header.csv", FileFormat:=xlCSV, _
    CreateBackup:=False

I am getting a problem with this in that I require all dates to be saved in the csv as they are shown in the Excel sheet (UK format) i.e. 24/09/2005

However, 24/09/2005 is coming out in the csv file as 9/24/2005.

Is there anything I can do to stop this? I use the same code on other spreadsheets and it works fine! Any ideas anyone?

Thanks,

Woody

 

[PBAPaul]

Hi Woody

I have had the same problem and spent ages trying to sort it. Eventually I wrote a simple macro to create the CSV file on the fly.

It is something like this:

If FileExists("C:\MyFile.Dat") Then Kill "C:\MyFile.Dat"
Open "C:\MyFile.Dat" For Output As #1
Sheets("Database").Select
For X = 2 To DBRows
  EntryText = ""
  For Y = 1 To 50
    MyData(Y) = Cells(X, Y).Value
    EntryText = EntryText & MyData(Y) & ";"
  Next
  EntryText = Left(EntryText, Len(EntryText) - 1) & ":"
  Write #1, EntryText
Next
Close #1

This creates a csv file ( I name them as DAT files) for a database with 50 columns of data.

Hope that this helps.

Paul

 

[SkipVought]

Woody,

First read and understand MS Dates -- Why do Dates and Times seem to be so much trouble? faq68-5827

Will this .csv be Opened/IMPORTED into another Excel Woekbook? If so, then just change the FORMAT in the Date columns to GENERAL. Then format as desired on IMPORT.

Otherwise, create a new column using TEXT function to format the DATE STRING however.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[woodyinoz]

Skip,

I'm not importing this file into another Excel sheet no. I'm using it to import info into a database.....

Any ideas?

Woody

 

[SkipVought]

And your database expects dd/mm/yyyy date-string format? The use the TEXT function as suggested.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[woodyinoz]

Unfortunately yeah, it does....

I've not used the TEXT function before... Any chance of a sample?

Woody

 

[SkipVought]

if your date is in A1...
[tt]
=TEXT(A1,"dd/mm/yyyy")
[/tt]
You can copy that formula down thru your data, copy and paste special - values over Column A and then delete the column containing the formula.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[woodyinoz]

Nice one... All sorted!