![]() |
|
#1
|
|||
|
|||
|
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:
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! |
|
#2
|
||||
|
||||
|
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?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
#3
|
|||
|
|||
|
Thanks macropod
Quote:
How would I refer to table cells row/column address in a macro? Quote:
Thanks again, Simon |
|
#4
|
||||
|
||||
|
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: Code:
With wdDoc.Tables(1).Cell(1, 2).Range .ContentControls(1).LockContentControl = False .ContentControls(1).Delete .Text = "Hello" End With
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
#5
|
|||
|
|||
|
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: Code:
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
|
|
#6
|
||||
|
||||
|
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
How to import list from Excel into drop-down list into word
|
ahw | Word VBA | 43 | 02-28-2020 08:11 PM |
| Drop down list, Can it be done??? | garethreid | Outlook | 0 | 08-09-2012 06:08 AM |
Drop Down List using SYMBOLS
|
sm5948 | Word | 2 | 09-28-2011 05:05 AM |
| Create Drop Down List Box | hbradshaw | Word VBA | 0 | 09-27-2010 06:24 AM |
| Attachments list doesn't print | juicejug | Outlook | 0 | 10-30-2009 09:00 AM |