Set date format in workbook created by VBA

[GonzoB]

Hi Helpers,

I have the following code which creates a new workbook on my computer from a worksheet:

Sub Create_New_File()

    Dim ImportSheet As String
    ImportSheet = ThisWorkbook.Name
  
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    objects = Array("new_file")
    
    For Each Object In objects
        fn = ThisWorkbook.Path & Application.PathSeparator & Object
        On Error Resume Next
        ThisWorkbook.Sheets(Object).Copy
        If ImportSheet <> ActiveWorkbook.Name Then
            ActiveWorkbook.SaveAs Filename:=fn, FileFormat:=xlCSVUTF8, CreateBackup:=False
            ActiveWorkbook.Close
        End If
    Next Object
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

On the source sheet (new_file) there's a date field which contains dates in this format: yyyy-mm-dd. My problem is that in the newly created workbook the date format becomes mm/dd/yyyy. Do you know what modification to do in the VBA to have yyyy-mm-dd in the new workbook?

Thanks

 

[rlv01]

You are saving the new workbook as a csv file.

  ActiveWorkbook.SaveAs Filename:=fn, FileFormat:=xlCSVUTF8, CreateBackup:=False

There is no way to save formatting to a csv file, so when you use excel to open the new CSV file, it will apply the default date format to any cell it interprets as a date. Your choices are are to save the file as an xlsx workbook, change your default date settings, or live with mm/dd/yyyy.