Word VBA Inserting of Bookmarks and Formatting

Question

I'm trying to insert a series of 78 values from an excel sheet into a word document. This is to allow for easy generation of word documents. The following code is allows me to insert:

Option Explicit

Sub WriteExtension()
'
' WriteExtension Macro
'
'
        copyFile

        Dim nWord As New Document
        word.Application.ScreenUpdating = False

        Set nWord = Documents.Open("C:\target\file\here\targetfile", Visible:=False)


        'initialize excel variables
        Dim oExcel As Excel.Application
        Dim oWorkbook As workbook
        Dim oWorksheet As worksheet

        'initialize excel object
        Set oExcel = New Excel.Application
        oExcel.ScreenUpdating = False
        Set oWorkbook = oExcel.Workbooks.Open("source\spreadsheet\here\sourcespreadsheet.xlsx")
        Set oWorksheet = oWorkbook.Worksheets(Sheets("Extensions").Index)

        'setup loop variables
        Dim tempString As String
        Dim i As Long
        Dim bkMark As Bookmark

        'insert items from spreadsheet onto word document
        Dim insertText As String

        For i = 1 To 78
            nWord.Bookmarks("BM" & i).Select
            nWord.Bookmarks.Item("BM" & i).Range.InsertAfter (Cells(4, i + 6))
        Next i

        Dim filePath As String
        Dim fileName As String
        Dim newName As String

        'save the file as a PDF and close the PDF
        filePath = "C:\target\path\here"
        fileName = Cells(4, 13) & Cells(4, 12) & Cells(4, 79) & ".pdf"
        newName = filePath & fileName
        nWord.SaveAs2 fileName:=newName, FileFormat:=wdFormatPDF

        'close things
        nWord.Close False
       ' oWorksheet.
        oWorkbook.Close False
        oExcel.Quit
End Sub

'function takes the current extension template which has this macro in it, and copies it to a new blank word document
Function copyFile()

    Dim fso As Object
    Set fso = VBA.CreateObject("Scripting.FileSystemObject")

    Dim sourceFile As String
    Dim targetFile As String

    sourceFile = "c:\source\file\here\sourcedocument.docx"
    targetFile = "c:\target\file\here\targetfile"

    fso.copyFile sourceFile, targetFile

End Function

In a nutshell what this program does, is it takes information from a spreadsheet and is trying to insert information in a specific cell (or will do specific calculations) at a specific location on the document. In order to do this, it first takes a sample document (sourcefile), makes a new file (targetfile), then copies over the sourcefile to targetfile. This means the placement of text, formating, and bookmarkes are all copied over exactly.

Then it initializes a new excel object, where I am keeping my data that I want to feed into the document. It opens it up, and runs a loop 78 units long for each of the 78 bookmarks. It saves the new document (previously called targetfile) and names it based on the values in the excel spreadsheet. It saves the new document as a PDF. Then it closes the document, closes excel, and closes word.

The issue I am having is one of formating. Basically, I am looking for the insert to happen above some sort of underline or border, rather than it displacing the line. Imagine filling in an application - you write over the line rather than inserted next to it. It doesn't seem like font.underline works, as it just hugs the text rather than makes an underline looking thing. It might, perhaps I haven't fully fleshed it out, but I was hoping that the geniuses of Stackoverflow could assist me on this.

So the questions is: how do I insert things next to bookmarks so I can insert it over the line rather than next to? In other words, how do I use bookmarks/page formatting to make the text appear as #3, and not #1 or #2. Most of the time it appears as #1.

This code works

        Dim i As Long
        Dim bkMark As Bookmark

        'insert items from spreadsheet onto word document
        Dim insertText As String

        Dim startX As Long
        Dim startY As Long


     For i = 1 To 2

        startX = ActiveDocument.Bookmarks.Item("BM" & i).Range.Information(wdHorizontalPositionRelativeToPage)
        startY = ActiveDocument.Bookmarks.Item("BM" & i).Range.Information(wdVerticalPositionRelativeToPage) + 13
        'Dim shp As Shape
         With ActiveDocument.Shapes.AddLine(startX, startY, startX + 200, startY).Line
            .ForeColor.RGB = RGB(0, 0, 0)

         End With
    Next i
'

Answer 1

My original suggestion was to manually add the Drawing Lines in the template file that you're using, which would avoid the need to recreate them in each copied version you're creating with this VBA procedure, and I still think that would be preferable but if you can't for some reason modify the template file, then you should be able to do something like below.

    Dim objLine as Shape   'declare a Shape object to represent the drawing object lines you'll insert

    For i = 1 To 2

        startX = ActiveDocument.Bookmarks.Item("BM" & i).Range.Information(wdHorizontalPositionRelativeToPage)
        startY = ActiveDocument.Bookmarks.Item("BM" & i).Range.Information(wdVerticalPositionRelativeToPage) + 13
        ' at each iteration, assign the return from AddLine method to the objLine variable
        Set objLine = ActiveDocument.Shapes.AddLine(beginX:=startX, beginy:=startY, endx:=startX + 200, endY:=startY)
        ' assign the RGB color to the objLine.Line.ForeColor
        objLine.Line.ForeColor.RGB = RGB(0, 0, 0)
        ActiveDocument.Bookmarks.Item("BM" & i).Range.InsertAfter ("Bookmark" & i)

    Next i

What we're doing here is using an object variable objLine of type Shape (which is the object type for Drawing Objects like Lines, TextBoxes, etc. that you might insert in the Document), and assigning the return value from the AddLine method to this shape object, at each iteration. Subsequently, we use objLine and assign the RGB color to it's Line.ForeColor (lines don't have a Fill property).

Answer 2

From your description it sounds as though you are trying to create a form.

If you were creating your form in Excel you would format each cell containing a response with a single line bottom border.

Now translate that same thinking to Word - set out your form as a table with the cells where input is required formatted with a bottom border. Place your bookmarks in the cells where you need to add data from Excel.

When inserting the value at the bookmark there is no need to select it. So you can simplify that part of your code to:

For i = 1 To 78
    nWord.Bookmarks("BM" & i).Range.InsertAfter (Cells(4, i + 6))
Next i