Creating one document in word from excel for Stata coding

[kenglish22]

Hi

I have an excel spread sheet with 500 variables/rows. Each row corresponds to a specific question in a questionnaire. There are columns for the question number, the question label, and then the labels for each of possible responses.

I want to auto generate the code in word that I need to correctly name each variable and the response to each question, so that I can import it directly into stata.

In word I have the following paragraph, which is the code for naming and labeling each variable. In my version of this code I have put bookmarks in place of 'column'. I've just included the columns to demonstrate where I need the excel data to be placed in my actual word document.

Word doc:

Rename columnA columnB
label var columnB "columnC"
label define columnBL 0 "columnD" 1"columnE " 2 “columnF” 3 “columnG ” etc.
label val columnB “columnBL”


What I would like is to have one word document with this paragraph repeated, ~500 times, with each paragraph including the data from a successive row in my excel document. So far I have only managed to write the VBA code to export data specific to individual cells and make just one paragraph, see below:

Sub test()
Dim objWord As Object
Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Code")
    Set objWord = CreateObject("Word.Application")
    objWord.Visible = True
    objWord.Documents.Open "S:\labelgen.docx"
    With objWord.ActiveDocument
        .Bookmarks("X").Range.Text = ws.Range("A2").Value
        .Bookmarks("Y").Range.Text = ws.Range("B2").Value
        .Bookmarks("YY").Range.Text = ws.Range("B2").Value
        .Bookmarks("Z").Range.Text = ws.Range("C2").Value
        .Bookmarks("YYY").Range.Text = ws.Range("B2").Value
        .Bookmarks("A").Range.Text = ws.Range("D2").Value
        .Bookmarks("B").Range.Text = ws.Range("E2").Value
        .Bookmarks("C").Range.Text = ws.Range("F2").Value
        .Bookmarks("D").Range.Text = ws.Range("G2").Value
        .Bookmarks("E").Range.Text = ws.Range("H2").Value
        .Bookmarks("YYYY").Range.Text = ws.Range("B2").Value
        .Bookmarks("YYYYY").Range.Text = ws.Range("B2").Value
    End With
    Set objWord = Nothing
End Sub

Is there a way to use VBA to achieve this? I am new to VBA but would like to learn.

Thanks