I have a word document with 78 bookmarks strategically placed throughout the document. There lettered
bookmark 1 - A
bookmark 2 through bookmark 27- AA -> AZ
bookmark 28 - B
Bookmark 29 through bookmark 54 - B -> BZ
Bookmark 55 - c
Bookmark 56 through 78 - CA -> end
The excel spreadsheet is laid out the following
col 7|col 8 | (a number of columns here)| Col 19 | Col 20 |col 21
Name | var 1| .. |Ans to Q1| Ans to Q2|info
Basically there is a number of different rows, and a column is either like column 8 and 21 (meaning it contains information such as agency name, start date) or column 19 and 20 (which is a binary yes/no answer to a question).
In the microsoft word document VBA editor the following code is used
Sub WriteExtension()
'initialize excel variables
Dim oExcel As Excel.Application
Dim oWorkbook As workbook
Dim oWorksheet As worksheet
'initialize excel object
Set oExcel = New Excel.Application
Set oWorkbook = oExcel.Workbooks.Open("C:\file\path\here")
Set oWorksheet = oWorkbook.Worksheets(Sheets("Extensions").Index)
'setup loop variables
Dim tempString As String
Dim i As Long
Dim bkMark As Bookmark
Dim bmrange As Range
i = 7
Dim cnt As Long
cnt = 0
'i is the index that tracks the column supposed to be copied from the excel file
'insert check boxes
On Error Resume Next
For Each bkMark In ActiveDocument.Bookmarks
If bkMark.Name = "AL" Or bkMark.Name = "AM" Or bkMark.Name = "AN" Or bkMark.Name = "AO" _
Or bkMark.Name = "AP" Or bkMark.Name = "AQ" Or bkMark.Name = "AR" Or bkMark.Name = "AS" _
Or bkMark.Name = "BA" Or bkMark.Name = "BB" Or bkMark.Name = "BC" Or bkMark.Name = "BD" _
Or bkMark.Name = "BE" Or bkMark.Name = "BF" Or bkMark.Name = "BH" Or bkMark.Name = "BK" _
Or bkMark.Name = "BI" Or bkMark.Name = "BJ" _
Or bkMark.Name = "BL" Or bkMark.Name = "BM" Or bkMark.Name = "BN" Or bkMark.Name = "BO" _
Or bkMark.Name = "BR" Or bkMark.Name = "BS" Or bkMark.Name = "BT" Or bkMark.Name = "BU" Then
ActiveDocument.ContentControls.Add(wdContentControlCheckBox, bkMark.Range) = True
else
bkMark.range.insertafter(cstr(cells(2, i)))
i = i + 1
End If
Next
oExcel.Quit
End Sub
Basically the goal is to go through each bookmark, and insert either the value that is in the ith column (so for instance, the value of cell(2, 50) should be inserted after the 50th bookmark). If the 19th row is a checkbox question though, I don't want to insert a value; I want to insert a checkbox. But the way that the word document is laid out, is that there are two checkboxes for each question; one checkbox if the person wants to check the 'yes', and the other if they want to check no.
The problem comes in that I do not know how to keep track of the i. The spreadsheet has one column for two checkboxes that need to be inserted. I would love to do something like.
ActiveDocument.ContentControls.Add(wdContentControlCheckBox, bkMark.Range.next)
but that doesn't place the checkbox in the next bookmark in the collection; it just places it in the next range in the collection.
I could collapse the spreadsheet, so that each column which contains a checkbox gets its own column; so it would be
Question 1 - yes | Question 1 - no
1 | 0
but that makes the user interface so bulky.
Aucun commentaire:
Enregistrer un commentaire