Microsoft Excel - Word date converts to American format when added to Excel via VBA

Asked By John Wirth on 17-Jun-13 04:46 AM
I am transferring data from Word reports. This includes date information. The word date is ddd dd-mm-yyyy (e.g. MON 10-06-2013
I run code to extract the month, and the correct month is extracted (from example, June).
However, when I get to extracting the actual date it doesn't work and converts to American format- form the example, the cell contents become 06/10/2013. I am aware of that problem and have built in code to handle it, but it is only doing so intermittently. Even if I then manually change the cell formatting, it remains the same.

Dim WordDate As Variant
    WordDate = wdDoc.FormFields("Date").Result
        WordDate = Right(WordDate, 10)
            WordDate = CDate(WordDate)
                WordDate = Format(WordDate, "dd/mm/yyyy")
                    Cells(NextRow, "r") = Format(WordDate, "dd/mm/yyy")

Thanks in advance.
Harry Boughen replied to John Wirth on 17-Jun-13 05:36 AM
Hello John,
This is a bit of a guess (I haven't tried it at all) but perhaps you could try
Cells(NextRow, "r").Value = CDate(WordDate)
Cells(NextRow, "r").NumberFormat = "dd/mm/yyyy"
to replace lines 3 to 6.
Regards
Harry

PS Crudely tested, seems to work.  H
John Wirth replied to Harry Boughen on 18-Jun-13 06:24 AM
Thank you Harry. I did try that but exactly the same thing happened.

I tried several different versions and finally this works so far:

Dim WordDate As Variant
    WordDate = Replace(wdDoc.FormFields("Date").Result, "-", "/")
        WordDate = Right(WordDate, 10)
            Cells(NextRow, "r").Value = CDate(WordDate)
                Cells(NextRow, "r").NumberFormat = "dd/mm/yyyy"
Harry Boughen replied to John Wirth on 18-Jun-13 07:39 AM
Hi John,
As I said, I only tested crudely by entering a string (MON 10-06-2013) to match your example as a variable to Excel, not by importing from Word so I guess there must be some sort of subtle difference in what you see and what you get.
Regards
Harry
John Wirth replied to Harry Boughen on 27-Jun-13 04:54 AM
Thanks Harry, all seems to be fine now and have used for importing a date from several hundred documents so far.