• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

navic

Member
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
Code:
  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?
 

Attachments

chirayu

Well-Known Member
change the sheets line to this

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

Marc L

Excel Ninja
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

Member
Hi @chirayu
It works, the key is this line of code below
Code:
.Sheets("PRODUCT").Range("C6").Value = DateSerial(2019, 10, 31)
Thank you.

Hi @Marc L
Thank you for your brief education.

My respect for both
 
Top