Thread: Input different ranges from excel to a word template (letter)

This is an example of code you could place into an excel spreadsheet with the data in Column B - if you are clever you can name your bookmarks in such a way that you could iterate through the bookmarks without having to name each bookmark. That being said this will place the Doctors Name and 1st Line of the address into the word template. You could also pass formatting to the routine that inserts the text however see how you go with this as a simple guide

Sub CreateWordDoc()
    Dim wdApp As Object, a
    Dim wdDoc As Object
    Dim rCell As Range, rRng As Range
       
Set rRng = Sheet2.Range("B1:B2") 'Column B contains the data in the same order as the bookmarks
a = rRng 'Place values into array - you could use a loop to go through the array and
'if the bookmarks are named correctly you place all this into a loop (e.g. bmk1, bmk2, bmk3 etc)
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Open("C:\temp\ScriptRequest.doc") ' location of word document
rplBmk wdDoc, a(1, 1), "DocFull"
rplBmk wdDoc, a(2, 1), "Add1"
wdApp.Visible = True
End Sub

Sub rplBmk(ByRef wdDoc As Object, _
    ByVal vValue As Variant, _
    ByVal sBmName As String)
    Dim wdRng As Object
   Set wdRng = wdDoc.Bookmarks(sBmName).Range
  wdRng.Text = vValue
End Sub

You could iterate through the array and then use clever bookmark names bmk1 bmk2 etc that way in the loop you pass sBmName as a string
e.g.

For k = 1 to 20 'number of bookmarks
sBmName = "bmk" & Cstr(k)
rplBmk wdDoc, a(k, 1), sBmName
Next k

That would save you typing the name of each bookmark individually - that means you would have to redo the Word Document and change the bookmark names. Make sure the order of the bookmarks corresponds to the data in Column B in excel.
Hope this makes sense.

Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

Can you post the letter you are using and an example workbook for me as well and I will take a look for you. The code is in the Excel workbook...yes?? and not in the Word document.? The path to the letter is correct and you have not missed any spaces etc - this is a file not found error generally

The error that you reported was a File Not Found Error - in your code you have the file name as ScriptRequest.doc however the file you uploaded is Script Request.doc (notice the space!) - Error 5174 is File not found. I have placed an error handler in the code. When an error like this occurs the Word application will be in your task manager - if the process is not quit then you will have a Word process running in the backgroud. The error handler will check to see if there is a Word process running and if so it will stop it.

Sub CreateWordDoc()
On Error GoTo errHandler
    Dim wdApp As Object, a
    Dim wdDoc As Object
    Dim rCell As Range, rRng As Range
    
Set rRng = Worksheets("Request").Range("B1:B2") 'Column B contains the data in the same order as the bookmarks
a = rRng 'Place values into array - you could use a loop to go through the array and
'if the bookmarks are named correctly you place all this into a loop (e.g. bmk1, bmk2, bmk3 etc)
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Open("C:\Users\Fred\Documents\Pharmacy Business Documents\Webster Packs\Script Request letter\Script Request.doc") ' NOTICE THE SPACE IN WORD DOCUMENT
rplBmk wdDoc, a(1, 1), "DocFull"
rplBmk wdDoc, a(2, 1), "Add1"
wdApp.Visible = True
Exit Sub
errHandler:
MsgBox "Error in generating letter - error number - " & Err.Number & _
" - " & Err.Description
If Not wdApp Is Nothing And Err.Number = 5174 Then
wdApp.Application.Quit
Set wdApp = Nothing
End If
End Sub

Sub rplBmk(ByRef wdDoc As Object, _
    ByVal vValue As Variant, _
    ByVal sBmName As String)
    Dim wdRng As Object
   Set wdRng = wdDoc.Bookmarks(sBmName).Range
  wdRng.Text = vValue
End Sub