VBA to export CSV not saving dates in UK format

  • Hi

    I've got a relatively simple task of exporting selected columns into a CSV ready to be used in another program.

    The macro seems to work as advertised with the exception that the dates do not keep their UK format but are resolved into USA format.

    The locales are set correctly on both my PC and the PC where the original xls file originates.

    Anyone know what tweak I need to make to the macro to make it export the date in UK format?

    I've attached a simplified version of the excel spreadsheet along with the VBA coding.

    Many thanks


  • Hi Smudge,

    I was facing the same issue today. Update the below settings and give a try.

    Windows settings -> Time & language -> Date & time -> Time Zone -> UK location and save the settings.

    Go to excel sheet-> renter the date. You'll see the difference.

  • Try:

    wsNew.SaveAs vFile, FileFormat:=xlCSV, Local:= True

    As per the help in the developer reference:

    True saves files against the language of Microsoft Excel (including control panel settings).

    False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!