CODEWITHSUNDEEP
vbaexcelms-word
Tim Wilkinson
Tim Wilkinson

Reputation: 3801

Copy date from Excel paste to Word in different format

I have a macro that copies certain data from Excel and pastes them into Word, however i'm looking for a way to modify the date format before pasting.

This currently copies the date as it appears in Excel, i.e. 16/01/2015

Sub NoFormatPaste()
    wdFind.Replacement.Text = ""
    wdFind.Forward = True
    wdFind.Wrap = wdFindContinue
    wdFind.Execute
    appWd.Selection.PasteSpecial DataType:=wdPasteText
    CutCopyMode = False
End Sub

Cells(Application.ActiveCell.Row, 14).Select
Selection.Copy
wdFind.Text = "DATE"
Call NoFormatPaste

However I would like to paste it in a different format. Something like this, but its not working. i.e. Friday 16th January 2015

Sub NoFormatDatePaste()
    wdFind.Replacement.Text = ""
    wdFind.Forward = True
    wdFind.Wrap = wdFindContinue
    wdFind.Execute
    appWd.Selection = Format(date_example, "dddd d mmmm yyyy")
    CutCopyMode = False
End Sub

wdFind.Text = "DATE"
Call NoFormatDatePaste

I can paste the entire code if needed, these are just the bits that aren't working.

It seems you can't just tell Word what values to print, and the values need actually copying and pasting.

Upvotes: 0

Views: 1262

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Store your date value in a string and pass it to the other procedure:

Dim dateValue as String

dateValue = Cells(Application.ActiveCell.Row, 14).Value

wdFind.Text = "DATE"

Call NoFormatPaste(dateValue)

In the other procedure, don't use the .Paste method, instead you can just insert the string.

Sub NoFormatDatePaste(dateValue as String)

wdFind.Replacement.Text = ""
wdFind.Forward = True
wdFind.Wrap = wdFindContinue
wdFind.Execute
appWd.Selection.InsertAfter Format(dateValue, "dddd d mmmm yyyy")


End Sub

Upvotes: 1

Related Questions