Excel 2007 Save worksheet as CSV differs to save worksheet as csv in VBA

Question

I have a problem working with some worksheets within a workbook from a 3rd party website source.

• the spreadsheet is available at the 3rd party source website
• the worksheet I am having problems with is the EC worksheet in the all-euro-data-2009-2010.xls in that zipfile

It's a list of football match data.

Manual Save as CSV
Now when this "EC" spreadsheet is in focus and I go to SAVE AS and choose .CSV, then spreadsheet is saved as a CSV and the data is perfect. ie the dates in column "B" and they are as they should be in DD/MM/YYYY format.

EG , taken from last row of the EC worksheet EC,24/04/2010,Tamworth,Ebbsfleet

This is correct, if I go to the menu and choose to "SAVE AS" and chose CSV and accept all the prompts to keep it in CSV format blah blah the CSV file is save as EC,24/04/2010,Tamworth,Ebbsfleet

This is correct as the dates are in UK dd/mm/yyyy

VBA Save as CSV
Now to make things a hell of a lot easier for myself as I need to save each worksheet off as a csv file so I am using the VBA code below to auto save each worksheet in the current directory named as worksheetname.csv ( eg EC.csv) each time the workbook is closed however this is changes the dates in column B to Americam format MM/DD/YYYY.

However using VBA to save as a CSV the data ends up like EC,4/24/2010,Tamworth,Ebbsfleet

Notice the change from UK dd/mm/yyyy to now the American format mm/dd/yyyy

How can I make the change in the VBA to keep the dates in the format shown in the actual spreadsheet ie dd/mm/yyyy when saving in vba to CSV?

I need this CSV option to work in VBA as these and many other similar spreadsheets are saved as CSV files for importation EVERY DAY.

My settings

I am using Excel 2007, on a Win 7 (64bit) PC here in the UK.

I have also tried add the Local:=True to the ws.SaveAs but this made no difference. ie the dates still saved as EC,4/24/2010,Tamworth,Ebbsfleet when saving from VBA

Thanks

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Alerts_Suppress
ThisWorkbook.Save
Save_All_WorkSheets_As_CSV
Alerts_Enable
ActiveWorkbook.Close False
Application.Quit
End Sub


Sub Save_All_WorkSheets_As_CSV()

Dim Name As String
Dim Current_Directory As String
Dim ws As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim i As Integer
i = 0

Current_Directory = ActiveWorkbook.Path & "\"
For Each ws In wb.Worksheets
    Name = Current_Directory & LCase(ws.Name) + ".csv"
    ws.SaveAs Name, xlCSV
    i = i + 1
Next

End Sub

Sub Alerts_Suppress()
Application.DisplayAlerts = False
End Sub

Sub Alerts_Enable()
Application.DisplayAlerts = True
End Sub

Answer 1

This seems to be an inherent 'problem' with vba and I believe the only solution is to format your date field to a text field prior to exporting, obviously change Selection to the actual range

Sub convertToStr()
  Dim c As Range

  For Each c In Selection
    c.Value = "'" + CStr(c.Value)
  Next c

End Sub

Answer 2

I have been able to replicate the issue as described. My regional setting are NZ English. Setup is Excel 2010 32 bit, Win7 64bit

I have used csv files with Excel many times over the years and usually found Excel native csv handling very fickle. I tend to write my own csv handlers.

Here's an example. You will need to modify it to suit your needs. Eg handle what you want and do not want ""'d and include or exclude empty cells etc

Sub SaveAsCsv(ws As Worksheet, Name As String)
    Dim fso As FileSystemObject
    Dim fl As TextStream
    Dim dat  As Variant
    Dim rw As Long, col As Long
    Dim ln As String
    Dim Quote As String

    On Error GoTo EH

    Set fso = New FileSystemObject
    Set fl = fso.CreateTextFile(Name, True)
    dat = ws.UsedRange
    For rw = 1 To UBound(dat, 1)
        ln = ""
        For col = 1 To UBound(dat, 2)
            ' Modify this section to suit your needs
            If dat(rw, col) <> "" Then
                If IsNumeric(dat(rw, col)) Or _
                  IsDate(dat(rw, col)) Then
                    Quote = ""
                Else
                    Quote = """"
                End If
                ln = ln & Quote & dat(rw, col) & Quote & ","
            End If
        Next col
        fl.WriteLine Left(ln, Len(ln) - 1)
    Next rw


EH:
    On Error Resume Next
    If Not fl Is Nothing Then fl.Close
    Set fl = Nothing
    Set fso = Nothing

End Sub

It includes an early bound reference to Microsoft Scripting Runtime, so set the reference or change to late bound if you want.

To use it in your code, relace ws.SaveAs Name, xlCSV with SaveAsCsv ws, Name

Answer 3

A month or so ago I got shot down when I made an aside about Excel sometimes converting UK dates that could be valid US dates to US dates. So 1/4/11 (1 April 11) would become 4/1/11 (4 January 11) but 13/4/11 (13 April 11) would be unchanged. I could not duplicate the problem so had to admit that I must have made a mistake.

I encountered this problem eight or nine years ago and since then I have always passed dates in the format "1Apr11" to avoid the problem. Your question suggested I had been correct but something other than Excel was at the root of the problem.

I set up a worksheet with numbers, dates and strings. It saved as a CSV file just as I would wish.

I went to Control Panel and changed my location to "United States". I saved the worksheet again and this time the dates in the CSV file were "Americanised" as explained above.

I restored my location to "United Kingdom". My third CSV file was again correct.

You claim that your Control Panel settings are correct. I think you need to check again and look for anything else that could be wrong since I now know how to switch this effect on and off.

Answer 4

When you face similar problems, Record Macro and compare the generated code to yours. Note that it uses Workbook.SaveAs instead

For Each ws In wb.Worksheets
    ws.Activate
    wb.SaveAs Current_Directory & LCase(ws.Name) & ".csv", xlCSV ' check this line with Record Macro
Next

Answer 5

This is the case if you are using the SaveAs from the Excel menu but not when using VBA. Changing the regional setting has no effect to VBA flipping date to American format. But adding the below statement fixed it for me as suggested above.

ws.Columns("B:B").NumberFormat = "dd/mm/yyyy"