Excel в Word (VB) цикл по строкам

Question

Макрос, который у меня есть в Excel, считывает данные из каждой строки и создает файл слов для каждой строки на основе этих данных. Файл слов, используемый в качестве шаблона, имеет закладки (значения в столбцах сопоставляются с закладками).

Я заставил его работать для одной строки, но он не будет проходить по всем строкам. Код, который я использую, я получил от:http://www.wiseowl.co.uk/blog/s199/word-bookmarks.htm

Вот мой код:

Option Explicit

'change this to where your files are stored

Const FilePath As String = "C:\Files\"

Dim wd As New Word.Application

Dim SOPCell As Range

Sub CreateWordDocuments()

    'create copy of Word in memory

    Dim doc As Word.Document

    wd.Visible = True

    Dim SOPRange As Range

    'create a reference to all the people

    Range("A1").Select

    Set SOPRange = Range(ActiveCell, ActiveCell.End(xlDown)).Cells


    'for each person in list �

    For Each SOPCell In SOPRange

        'open a document in Word

        Set doc = wd.Documents.Open(FilePath & "template.doc")

        'go to each bookmark and type in details

        CopyCell "sop", 0
        CopyCell "equipment", 1
        CopyCell "component", 2
        CopyCell "step", 3
        CopyCell "form", 4
        CopyCell "frequency", 5
        CopyCell "frequencyB", 5

        'save and close this document

        doc.SaveAs2 FilePath & "SOP " & SOPCell.Value & ".doc"

        doc.Close

    Next SOPCell

    wd.Quit

    MsgBox "Created files in " & FilePath & "!"

End Sub

Sub CopyCell(BookMarkName As String, ColumnOffset As Integer)

    'copy each cell to relevant Word bookmark

    wd.Selection.GoTo What:=wdGoToBookmark, Name:=BookMarkName

    wd.Selection.TypeText SOPCell.Offset(0, ColumnOffset).Value

End Sub

Answer 1

Вы, кажется, пропускаете параметр диапазона в Sub Sub CopyCell

Sub CreateWordDocuments()

    'create copy of Word in memory

    Dim doc As Word.Document
    wd.Visible = True
    Dim SOPRange As Range
    'create a reference to all the people

    Set SOPRange = Range(Range("A1"), Range("A1").End(xlDown)).Cells

    'for each person in list 

    For Each SOPCell In SOPRange
        'open a document in Word
        Set doc = wd.Documents.Open(FilePath & "template.doc")
        'go to each bookmark and type in details
        CopyCell SOPCell, "sop", 0
        CopyCell SOPCell, "equipment", 1
        CopyCell SOPCell, "component", 2
        CopyCell SOPCell, "step", 3
        CopyCell SOPCell, "form", 4
        CopyCell SOPCell, "frequency", 5
        CopyCell SOPCell, "frequencyB", 5
        'save and close this document
        doc.SaveAs2 FilePath & "SOP " & SOPCell.Value & ".doc"

        doc.Close

    Next SOPCell

    wd.Quit

    MsgBox "Created files in " & FilePath & "!"

End Sub

Sub CopyCell(rg As Range, BookMarkName As String, ColumnOffset As Integer)

    'copy each cell to relevant Word bookmark

    wd.Selection.GoTo What:=wdGoToBookmark, Name:=BookMarkName

    wd.Selection.TypeText rg.Offset(0, ColumnOffset).Value

End Sub