Excel does not display the correct VBA inserted Date to closed Workbook

[navic]

I have about a hundred XLSX files in a folder. I'm using the VBA code below.
VBA correctly inserts a Date into all workbooks but does not display the correct format. It is as if the Enter key function is missing.
All workbooks already have formatted cells by Date "dddd - dd.mm.yyyy".
Attached is one of the closed workbooks that Inserted/Replaced Date.
Part of the VBA code I use in the 'Master' workbook.
btw: I'm a beginner in VBA

  For i = 1 To myN
          With Workbooks.Open(myFiles(1, i) & "\" & myFiles(2, i))
              .Sheets("Product").Range("C6").Formula = ("31.10.2019") 'change to suit SheetNameTab and Value or Formula
              '.Application.SendKeys "{ENTER}" 'not work?????????????
              .Close True 'close and save wbk
          End With
  Next

Does anyone know how to solve the problem?

 

[chirayu]

change the sheets line to this

With .Sheets("Product").Range("C6")
    .Value = DATESERIAL(2019,10,31)
    .NumberFormat = "DDDD - DD/MM/YYYY"
End With

 

[Marc L]

Or use CDate VBA function for any valid 'text' date.​

​

A VBA harcoded date must be written as #10/31/2019# without any double quote …​

​

Via a formula for a non UK / US regional setting 'text' date use the FormulaLocal property as Formula always belongs to UK / US !​

​

Text date cells can be also converted via the Text To Columns Excel feature (Convert) (TextToColumns VBA method).​

​

At least to change the cell format just use the VBA NumberFormat property …​

​

 

[navic]

Hi @chirayu
It works, the key is this line of code below

.Sheets("PRODUCT").Range("C6").Value = DateSerial(2019, 10, 31)

Thank you.

Hi @Marc L
Thank you for your brief education.

My respect for both