Bookmark applied to drop-down list doesn't do the job

[thecreaser]

Hi Folks,

I am using an Excel macro to populate a Word 2010 document with data. I'm struggling with some aspects and have hit a brick wall. The Word document is opened from a template .dotx which has a bunch of table cells for the user to enter data. Users can use the template in two ways:

1. They can manually create a new blank document to edit via File - New - My Templates
2. Or they can run a macro from within Excel to create and populate a new file automatically

If the user choses option 1, to speed up the data entry some of the cells have drop-down list content controls which contain lists of names to pick from.

The word template has a bunch of bookmarks which the excel macro uses to paste data into. Most of the bookmarks apply to empty table cells and the macro populates these with data perfectly. So far so good.

However, a couple of bookmarks are applied to a couple of drop-down lists and this is the problem: they won't pick up the values pasted to their bookmarks by the macro. I can't get my head around this. Bookmarked date picker content controls are picking up the values from the macro, but bookmarked drop-down list content controls do not pick up the values from the macro.

Am I attempting the impossible?



Enormous thanks for any help!

[macropod]

Hi thecreaser,

When working with table cells and content controls, you shouldn't need to use bookmarks. For tables, simply use the table number and the cell's row/column address. For content controls, simply give each a title and refer to them by that.

As for the dropdowns, are you trying to populate them, or simply choose from one of the available values?

[thecreaser]

Thanks macropod

Originally Posted by macropod

Hi thecreaser,
When working with table cells and content controls, you shouldn't need to use bookmarks. For tables, simply use the table number and the cell's row/column address. For content controls, simply give each a title and refer to them by that.

I have tried removing the bookmark attached to the drop-down list and then referring to the drop-down list title or tag but it doesn't seem to work, I might not be referring to it correctly in the macro- how should I do this correctly?

How would I refer to table cells row/column address in a macro?

Originally Posted by macropod

As for the dropdowns, are you trying to populate them, or simply choose from one of the available values?

The excel macro is currently trying to paste a value in the word table cell that contains the drop down list. In reality, we're trying to pick a value from the list (the excel file has identical values to the word list).
Thanks again,
Simon

[macropod]

You can populate a Word table cell from Excel with code like:
wdDoc.Tables(1).Rows(1).Cells(2).Range.Text = "Hello"
or
wdDoc.Tables(1).Cell(1, 2).Range.Text = "Hello"

And, since you're populating the document directly, you may as well first delete the dropdown there, which you could do with code like:

With wdDoc.Tables(1).Cell(1, 2).Range
  .ContentControls(1).LockContentControl = False
  .ContentControls(1).Delete
  .Text = "Hello"
End With

After all, having updated the document from Excel, you presumably wouldn't want someone to go and change the values.

[thecreaser]

I like the idea of deleting the content control. You're right, we don't have a need to change the values later.

I'm getting an error using the .ContentControls (1):
The requested member of the collection does not exist
Any ideas?

As a total vba novice I'm inching slowly forward. What is the significance of the (1) ?

My hideously badly written code is:

Sub Populate_Word_Template()
'create copy of Word in memory
Dim doc As Word.Document
Dim iResponse As VbMsgBoxResult
Dim JRNumberRange As Range
wd.Visible = True


'get the Job Request number from the excel table
On Error Resume Next
    Application.DisplayAlerts = False
        Set JRNumberCell = Application.InputBox(Prompt:= _
            "Please click cell of JR number", _
                Title:="SPECIFY JOB REQUEST NO.", Type:=8)
                'Type=8 means cell reference is expected
On Error GoTo 0
    Application.DisplayAlerts = True
    If JRNumberCell Is Nothing Then
        Exit Sub
    Else
        MsgBox "JR number " & JRNumberCell & " was selected"
    End If
    
'check whether the Job Request file exists already
If Dir(FilePath & JRNumberCell.Value & ".docx") <> "" Then
    'ask what to do
    iResponse = MsgBox(FilePath & JRNumberCell.Value & ".docx already exists. OK to overwrite or Cancel to stop macro.", _
                vbOKCancel + vbQuestion + vbDefaultButton2 + vbMsgBoxSetForeground, _
                Title:="OVERWRITE EXISTING FILE?")
    If iResponse = vbCancel Then Exit Sub
        'otherwise OK carry on with macro
    MsgBox "You've chosen to overwrite an existing file"
End If
    
Set doc = wd.Documents.Open(FilePath & "job request form.dotx")
    
'go to each bookmark and type in details by calling sub CopyCell
CopyCell "JRNumber", 0 '0 columns offset from 1st column
CopyCell "MSSHrsEstimate", 9
CopyCell "ProjectCode", 2
CopyCell "DateOfRequest", 7
CopyCell "DateRequiredBy", 8
'delete the content control from the word file
With doc.Tables(1).Cell(4, 2).Range
    .ContentControls(1).LockContentControl = False
    .ContentControls(1).Delete
End With
CopyCell "Requestor", 6
CopyCell "AuthorisedBy", 5
CopyCell "Project", 3
CopyCell "Product", 4

'save the Word document
doc.SaveAs FilePath & JRNumberCell.Value & ".docx", FileFormat:=wdFormatXMLDocument
MsgBox "Saved file " & FilePath & JRNumberCell.Value & ".docx and left it open for you to edit"
'close the Word document
'doc.Close

'quit word
'wd.Quit

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 JRNumberCell.Offset(0, ColumnOffset).Value
End Sub

Any words of wisdom would be appreciated.

[macropod]

The (1) refers to the first item of its kind. So doc.Tables(1) refers to the first table in the document and Cell(4, 2).Range ... .ContentControls(1) refers to the first content control in cell B4. The error you're getting sugests there isn't a content control there.