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...
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...