Solved: Macro change date format

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

n777k

Thread Starter
Joined
Apr 28, 2009
Messages
19
Hi All

I'm trying to save a selected range of the excel spreadsheet as .csv file with a macro, all works fine but the dates are converted to a number eg 4/4/2004 becomes 38081.

the code i'm using is:

Const Folder As String = "C:\Clients\test"
Dim FileName As String
Dim LastCell As String
FileName = Folder
ThisWorkbook.Worksheets("test1").Range("A1:k500").Copy
Workbooks.Add
ActiveCell.PasteSpecial (xlValues)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=FileName, FileFormat:=xlCSV
ActiveWorkbook.Close Savechanges:=False
Application.DisplayAlerts = False

Anybody knows a workaround?

Thanks
Nick
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
I would think that to change the format from date to text,

But that means that you would have to go thourgh the datevalues and do that, it will not work with the whole range as one block.

The Idea would then have to be that you will need to format the csv file before saving and closing it,
I'll see if I come up with something.I'll put my idea to code and see what I get.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
If added the following line to your code but it does'nt change a thing:

Code:
ActiveCell.PasteSpecial (xlValues)
Application.DisplayAlerts = False
Dim rng As Range
For Each rng In Range("A1:E5")
    If IsDate(rng.Value) Then rng.Value = Format(rng.Value, "m/d/yyyy")
Next rng

BTW you should reset Application.DisplayAlerts = False to True and not again to False

Sorry,can't think of something else
 
Joined
Dec 19, 2008
Messages
744
Try this...

Code:
Const Folder As String = "C:\Clients\test"
    Dim FileName As String
    Dim LastCell As String
    FileName = Folder
    ThisWorkbook.Worksheets("test1").Range("A1:k500").Copy
    Workbooks.Add
    ActiveCell.PasteSpecial (xlValues)
[COLOR=RoyalBlue]Selection.NumberFormat = "d/m/yyyy"[/COLOR]
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:=FileName, FileFormat:=xlCSV
    ActiveWorkbook.Close Savechanges:=False
    Application.DisplayAlerts = [COLOR=RoyalBlue]True[/COLOR]
 

n777k

Thread Starter
Joined
Apr 28, 2009
Messages
19
Works beautifully, thanks Keebellah for your effort and thanks turbodante for the gold piece of code "Selection.NumberFormat = "d/m/yyyy""
Cheers guys
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top