Load Data from ListBox into Word Doc. BoundColumn?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ryguy7272 - 13 Oct 2007 20:53 GMT

From Word’s help menu, I can tell that the BoundColumn identifies the column
of associated data values stored for the control. There doesn’t seem to be a
whole lot of documentation on this subject, but I am pretty sure it does what
I want it to do.

Thanks to some slick code provided by Doug Robbins, I can get data from an
Excel workbook to populate my ListBox, but I can’t seem to get the code form
the ListBox into my Word document.

The code that I am currently working with is shown below:
Sub CommandButton1_Click()
Set NewDoc = ActiveDocument
With NewDoc
cmbFirstName.BoundColumn = 1
.Variables("varFirstName") = cmbFirstName.Value

End With
UserForm1.Hide
End Sub

Private Sub Click()

Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
"DOCVARIABLE FirstName ", PreserveFormatting:=True

Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
"DOCVARIABLE LastName ", PreserveFormatting:=True

End Sub

In my word document, I have several fields such as this:
Error! No document variable supplied.
Error! No document variable supplied.

I keep getting this message when I hit Ctrl+F9:
Error! No Document variable Supplied.

What am I doing wrong?

Cordially,
Ryan---

Signature

RyGuy

Reply to this Message

ryguy7272 - 14 Oct 2007 21:19 GMT

For those interested, here is the final arrangement of code:
Code in Module:
Sub Userform_Initialize()
UserForm1.Show
End Sub

Code in UserForm (there are two subs here):
Sub Userform_Initialize()
Dim i As Integer, Addressee As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' Open the database
Set db = OpenDatabase("C:\Documents and Settings\Contcts.xls", False,
False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `List`")
' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)
'ListBox1.List = rs.GetRows(NoOfRecords) 'Transposed List
' Cleanup
rs.Close
db.Close
'AddressBlock = db
Set rs = Nothing
Set db = Nothing
End Sub

Private Sub CommandButton1_Click()
ListBox1.BoundColumn = 1
ActiveDocument.Variables("FirstName").Value = ListBox1.Value
ListBox1.BoundColumn = 2
ActiveDocument.Variables("LastName").Value = ListBox1.Value
ListBox1.BoundColumn = 3
ActiveDocument.Variables("Company").Value = ListBox1.Value
ListBox1.BoundColumn = 4
ActiveDocument.Variables("BusinessStreet").Value = ListBox1.Value
ListBox1.BoundColumn = 5
ActiveDocument.Variables("BusinessCity").Value = ListBox1.Value
ListBox1.BoundColumn = 6
ActiveDocument.Variables("BusinessState").Value = ListBox1.Value
ListBox1.BoundColumn = 7
ActiveDocument.Variables("BusinessPostalCode").Value = ListBox1.Value
' etc.
ActiveDocument.Fields.Update
UserForm1.Hide
End Sub

In your Excel file, name your range (my range is called 'List' and the Excel
file is called 'Contacts.xls'). Finally, on the UserForm, you need to have a
CommandButton, named CommandButton1 and you also need a ListBox, named
ListBox1. The last step is to go to Word, click Insert > Field > DocVariable

> (assign a name in the ‘New Name’ box) > Ok. Assign the links to

‘DocVariable’ wherever required throughout your document, fiddle with it a
little if it doesn’t work after the first attempt…you will get it to work.

Kind Regards,
Ryan—

PS, I extend a warm thanks to Jay Freedman and Doug Robbins for the
assistance. You two are amazing; I tip my hat to you guys.

Signature

RyGuy

> From Word’s help menu, I can tell that the BoundColumn identifies the column
> of associated data values stored for the control. There doesn’t seem to be a
[quoted text clipped - 37 lines]
> Cordially,
> Ryan---

Reply to this Message