Hi,
So what I'm trying to do is populate bookmarks from an excel workbook to a word template from the click of a command button. I'm not sure where to start with the looping section, the syntax for this type of loop is throwing me. I'm using office 2003. What I have so far is;
Sub copyToWord()
Dim wb As Excel.Workbook
Dim wrdApp As Word.Application
Dim myDoc As Word.Document
Set wb = ActiveWorkbook
Path = wb.Path & "\test.dotm"
Set wrdApp = CreateObject("Word.Application")
With wdApp
.Documents.Open "C:\Documents and Settings\test"
.Viable = True
.ActiveWindow.Window State = 0
.Activte
End With
Range("C66").Select
Selection.Copy
With myDoc
.Bookmarks("costing2").Range.PasteAndFormat (wdPasteDefault)
.Bookmarks("costing2").Range = Replace(.Bookmarks("costing2").Range, vbCrLf, "")
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data Copied Across", vbInformation, "Sample"
End Sub
My old code which doesn't loop looks like this;
Sub copyToWord()
Dim wrdApp As Word.Application
Set wrdApp = CreateObject("Word.Application")
Application.ScreenUpdating = False
Range("C66").Copy
With wrdApp
.Documents.Open "C:\Documents and Settings\test"
.Selection.Goto What:=wdGoToBookmark, Name:="costing1"
.Selection.PasteAndFormat (wdPasteSpecial)
.Visible = True
End With
Range("I66").Copy
With wrdApp
.Selection.Goto What:=wdGoToBookmark, Name:="costing2"
.Selection.PasteAndFormat (wdPasteSpecial)
End With
Range("B3").Copy
With wrdApp
.Selection.Goto What:=wdGoToBookmark, Name:="spec"
.Selection.PasteAndFormat (wdPasteSpecial)
End With
Range("B9").Copy
With wrdApp
.Selection.Goto What:=wdGoToBookmark, Name:="addressline2"
.Selection.PasteAndFormat (wdPasteSpecial)
End With
Range("B10").Copy
With wrdApp
.Selection.Goto What:=wdGoToBookmark, Name:="addressline3"
.Selection.PasteAndFormat (wdPasteSpecial)
End With
Range("B11").Copy
With wrdApp
.Selection.Goto What:=wdGoToBookmark, Name:="addressline4"
.Selection.PasteAndFormat (wdPasteSpecial)
End With
Range("B12").Copy
With wrdApp
.Selection.Goto What:=wdGoToBookmark, Name:="addressline5"
.Selection.PasteAndFormat (wdPasteSpecial)
End With
Range("B13").Copy
With wrdApp
.Selection.Goto What:=wdGoToBookmark, Name:="addressline6"
.Selection.PasteAndFormat (wdPasteSpecial)
End With
Range("B14").Copy
With wrdApp
.Selection.Goto What:=wdGoToBookmark, Name:="postcode"
.Selection.PasteAndFormat (wdPasteSpecial)
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data Copied Across", vbInformation, "Sample"
End Sub
The issue I was having with my old code (that works fine otherwise) is that an unwanted carriage return is added after each bookmark is populated.
Any help with either issue, the looping or the carriage return would be amazing.
Thanks
Bookmarks