Excel macro problem with date format

[axslinger]

Greetings,

I download historical stock prices from yahoo! The format is csv. When I get them I have to modify them slightly, here's what I do:

Open the file, insert a top row for column headings. Add the headings. Select the first column, format the column as date, 15-Mar-95, then I save the file. When I open the file in another program that I use, the date shows up as a 2-digit year, as it should.

However, if I record a macro, taking the exact same steps, when I open the csv in the other program, it shows up with a 4-digit year, which messes up the overall formatting. I have tried messing with my system Date format to no avail. Since I download about 100 files at a time, I really need to do this in a macro. Any ideas why it is ignoring the date format? Here is my macro:

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+f
'

Rows("1:1&quot.Select
Selection.Insert Shift:=xlDown
Range("A1&quot.Select
ActiveCell.FormulaR1C1 = "Date"
Range("B1&quot.Select
ActiveCell.FormulaR1C1 = "Open"
Range("C1&quot.Select
ActiveCell.FormulaR1C1 = "High"
Range("D1&quot.Select
ActiveCell.FormulaR1C1 = "Low"
Range("E1&quot.Select
ActiveCell.FormulaR1C1 = "Close"
Range("F1&quot.Select
ActiveCell.FormulaR1C1 = "Volume"
Columns("A:A&quot.Select
Selection.NumberFormat = "d-mmm-yy"
Application.DisplayAlerts = False
ActiveWorkbook.Save
CreateBackup = False
ActiveWindow.Close
End Sub

 

[Zathras]

Remember that formatting doesn't change the actual cell contents. Here is a version of the macro that does what I think you want:
[blue]

Sub FormatAndSave()
  Rows("1:1").Insert
  Columns("A:A").Insert
  [A1] = "Date"
  [B1] = "Date"
  [C1] = "Open"
  [D1] = "High"
  [E1] = "Low"
  [F1] = "Close"
  [G1] = "Volume"
  With Intersect(ActiveSheet.UsedRange, Range("A:A"))
  .FormulaR1C1 = "=TEXT(RC[1],""d-mmm-yy"")"
  .Copy
  .PasteSpecial xlPasteValues
  End With
  Application.CutCopyMode = False
  Columns("B:B").Delete
  Application.DisplayAlerts = False
  ActiveWorkbook.Save
  CreateBackup = False
  ActiveWindow.Close
  Application.DisplayAlerts = True
End Sub

[/color]