Enter Values from a ListBox

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ryguy7272 - 11 Oct 2007 15:20 GMT

I am almost done (I think) with a project for my supervisor. I am now trying
to get values, that show in a ListBox, to enter into a Word document. The
document has many MergeField elements in it, some of which include
First_Name, Last_Name, Address, City, Zip, Number (which is a unique ID
number.) These are located throughout the document and I think MergeFields
are the best choice for formatting purposes (as far as I know Bookmarks don’t
give users much control over formatting). The data comes from an Excel
sheet, which has several named ranges to identify data in each column. Doug
gave me code to get most of this running (thanks a lot). Now, I am just
trying to get the data, that is displayed in the ListBox, into the
appropriate MergeFields in the document. Does anyone here have any idea how
to do this? I’ve seen a few examples online, but not many, and the ones that
I did see don’t seem to be helpful at all.

As an aside, Doug game me some code before, which was great for another
project where I used a Bookmark to reserve a location for a reference that I
used for another project. The code is:

Private Sub CommandButton1_Click()

Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm1.Hide

End Sub

This is pretty much what I want to do with my MergeFields. The code that I
need may be very similar to this, but I’m not sure because I’ve never used
MergeFields before. I tried the Macro Recorder; it didn’t help much. Any
guidance would be greatly appreciated.

Regards,
Ryan---

Signature

RyGuy

Reply to this Message

ryguy7272 - 14 Oct 2007 21:22 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

> I am almost done (I think) with a project for my supervisor. I am now trying
> to get values, that show in a ListBox, to enter into a Word document. The
[quoted text clipped - 34 lines]
> Regards,
> Ryan---

Reply to this Message