kenglish22
New Member
- Joined
- Jun 17, 2015
- Messages
- 1
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:
Is there a way to use VBA to achieve this? I am new to VBA but would like to learn.
Thanks
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:
Code:
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