mardi 22 novembre 2016

Writing a word document from excel spreadsheet

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