PDA

View Full Version : [SOLVED] Formatting a date in word document from excel using VBA



kevvukeka
04-30-2015, 07:02 AM
Hi All,

I have a master word document in which there are few bookmarks which should be filled with data from excel. This is looped for all the rows in excel with data and for each row one word document is saved.

I need the date format in word to be d-MMM-YY, but when vba pastes data from excel to word it is in dd-mm-yyyy. I am trying the selection the text and format it but unable to do so.

Kindly help. Below is my code.





Sub test()
Dim objWord As Object
Dim ws As Worksheet
Dim r1 As Range, cl1 As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Open "C:\Users\jamesbond\Desktop\CS\Master.docx"
With objWord.ActiveDocument



.Bookmarks("Date").Range.Text = ws.Range("B2").Value
objWord.ActiveDocument.Selection.MoveRight Unit:=wdCharacter, Count:=10 ('at this line I get the error. I want to select 10 counts and format it as d-MMM-YY but it throws an error)
objWord.ActiveDocument.Selection.InsertDateTime DateTimeFormat:="d-MMM-yy", InsertAsField:=False, _
DateLanguage:=wdEnglishUS, CalendarType:=wdCalendarWestern, _
InsertAsFullWidth:=False
.Bookmarks("MN").Range.Text = ws.Range("N2").Value
.Bookmarks("CN").Range.Text = ws.Range("O2").Value
End With

Set objWord = Nothing
End Sub



Thanks for your help...

jonh
04-30-2015, 08:27 AM
.value is a date object so will be formatted to your regional settings.
Either format the value as string

.Bookmarks("MN").Range.Text = format(ws.Range("N2").Value ,"d-MMM-yy")
or if the cells are already formatted in Excel use the .text property instead.

kevvukeka
04-30-2015, 12:56 PM
Thanks a lot Jonh.. It solved my purpose....:clap: