Editing an existing Bible text in Microsoft Word

Creating footnotes for the first occurrence of each of the words in the list is fairly straightforward, but doing the same for each page may not be possible because as you add the footnotes, the page lengths change and thus what is the first reference on the page will change. I can't think of a simple way to do this.

If you want to create a footnote for each first occurrence then  you can use the following function to turn your worksheet data to an array. Add the function (and the macro which follows) to a new module. Change the bold text to reflect your worksheet. http://www.gmayor.com/installing_macro.htm   

Option Explicit
Private RS As Object
Private CN As Object
Private i As Long, iRows As Long
Private iFN As Long
Private Arr() As Variant
Private sFindText As String
Private sReplaceText As String
Private oRng As Range
Private oFN As Footnote
Private iPage As Long
 Const strWorkbook As String = "C:\Path\WordList.xlsx"        'The path of the workbook
Const strSheet As String = "Sheet1"        'The name of the worksheet

Public Function xlFillArray(strWorkbook As String, _
                            strWorksheetName As String) As Variant
    strWorksheetName = strWorksheetName & "$]"
    Set CN = CreateObject("ADODB.Connection")
    CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                              "Data Source=" & strWorkbook & ";" & _
                              "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

    Set RS = CreateObject("ADODB.Recordset")
    RS.Open "SELECT * FROM [" & strWorksheetName, CN, 2, 1

    With RS
        .MoveLast
        iRows = .RecordCount
        .MoveFirst
    End With
    Arr = RS.GetRows(iRows)
    If RS.State = 1 Then RS.Close
    Set RS = Nothing
    If CN.State = 1 Then CN.Close
    Set CN = Nothing
End Function

Then the following macro will use that array to replace the words and create the footnotes.


Sub ReplaceFromArray()
    xlFillArray strWorkbook, strSheet
    For i = 0 To UBound(Arr, 2)
        iFN = 0
        sFindText = Arr(0, i)
        sReplaceText = Arr(1, i)
        Set oRng = ActiveDocument.Range
        With oRng.Find
            .ClearFormatting
            .Replacement.ClearFormatting
            Do While .Execute(FindText:=sFindText, _
                              MatchWholeWord:=True, _
                              Forward:=True, _
                              Wrap:=wdFindStop) = True
                oRng.Text = sReplaceText
                iFN = iFN + 1
                If iFN = 1 Then
                    With oRng
                        With .FootnoteOptions
                            .Location = wdBottomOfPage
                            .NumberingRule = wdRestartContinuous
                            .StartingNumber = 1
                            .NumberStyle = wdNoteNumberStyleArabic
                        End With
                        .Footnotes.Add Range:=oRng, Reference:="", Text:=sFindText
                        oRng.Font.Underline = wdUnderlineSingle
                    End With
                End If
                oRng.Collapse wdCollapseEnd
                DoEvents
            Loop
        End With
    Next
    MakeGlossary
End Sub

The following macro called by the previous macro will list all the footnotes on a new page at the end of the document with the pages they occur on. You can format that last page as you wish.

Sub MakeGlossary()
   Set oRng = ActiveDocument.Range
    oRng.Start = oRng.End
    oRng.InsertBreak wdPageBreak
   
    For Each oFN In ActiveDocument.Footnotes
        iPage = oFN.Range.Information(wdActiveEndPageNumber)
        ActiveDocument.Range.InsertAfter vbCr & oFN.Index & vbTab & _
        oFN.Range.Text & vbTab & "Page " & iPage
    Next oFN
End Sub

If Graham's macro doesn't cover everything you need, take a look at this online book. He has macros to do a lot of UNIQUE editing tasks.

Macros for Writers, Editors and Proofreaders

by Paul Beverley, LCGI

http://www.archivepub.co.uk/book.html

You do want to use the footnote feature to mark your "translations" so they stay on the correct page. If you create manual pseudo-footnotes, then it will be a PITA to maintain.