How to copy date from excel to word

[JJabra]

Hi,

I have the below code which opens a word document and find words in column A and replaces it with the text in the adjacent cell in Column B. This works well.

My issue is that when it copies a date from one of the cells to word, it displays the date as a number rather than a date.

Public Sub Wordfindandreplace()

Dim ws As Worksheet, msWord As Object, itm As Range

Set ws = ActiveSheet
Set msWord = CreateObject("Word.Application")

With msWord
.Visible = True
.Documents.Open ("\\Desktop\My Documents\Test Document.docx")
.Activate

With .ActiveDocument.Content.Find
.ClearFormatting
.Replacement.ClearFormatting

For Each itm In ws.UsedRange.Columns("A").Cells

.Text = itm.Value2

.Replacement.Text = itm.Offset(, 1).Value2

.MatchCase = False
.MatchWholeWord = False

.Execute Replace:=2
Next
End With
SaveChanges = False
End With
End Sub

Any help would be appreciated.

 

[jmacleary]

Hi there. Try using .Text rather than .Value2

 

[JJabra]

Hi there. Try using .Text rather than .Value2

Hi,

That doesn't seem to be working, any other ideas?

 

[JJabra]

Hi,

That doesn't seem to be working, any other ideas?

No Worries, that is working I just changed the wrong bit, thank you