Excel + Word + popup screen before entering data (mail merge)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

fredduc@gmail.com - 26 Mar 2008 20:20 GMT

Dear all,

I want to do these things via Word:

1. before creating a letter via mail merge, Word has to ASK to user to
select a certain field from an Excel sheet

QUESTION "Select the recipient of this letter:" and then a number you
have to fill in / based on this answer (the number), Word can fill in
the recipient data (which Word should take from this Excel sheet)

2. then, again before creating this letter, Word also has to ASK once
again a certain field from an Excel sheet and THEN based on the
answer, Word should be able to fill in automatically some other fields

"What family will the person visit?" => based on this answer, Word
will select certain fields (e.g. the family's name and the number of
children they have)

Is this possible? All data is on ONE Excel sheet already (which should
be more easy as Word has one datasource then)...

Thank you in advance!

Fred;

Reply to this Message

Doug Robbins - Word MVP - 26 Mar 2008 20:59 GMT

It is possible, but I would not use mailmerge for it.

Rather, I would use a userform that contained a couple of comboboxes that
are loaded with the data from the Excel spreadsheet and from which the user
makes their selection of the recipient of the letter and then the recipient
of the visit.

See the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

Here is some code that loads a couple of combobox with data from a named
ranges in Excel (It requires that a reference be set to the Microsoft DAO
3.6 Object Library

Dim i As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
' Open the file containing the Office Locations
Set db = OpenDatabase(ThisDocument.Path & Application.PathSeparator &
"MyData.xls", False, False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `Offices`")
' Determine the number of retrieved records
With rs
.MoveLast
i = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
cmbOfficeLocations.ColumnCount = rs.Fields.Count
' Load the combobox with the retrieved records
cmbOfficeLocations.Column = rs.GetRows(i - 1)
rs.Close
Set rs = db.OpenRecordset("SELECT * FROM `Liability`")
' Determine the number of retrieved records
With rs
.MoveLast
i = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
cmbLiability.ColumnCount = rs.Fields.Count
' Load the combobox with the retrieved records
cmbLiability.Column = rs.GetRows(i - 1)
' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

And here is a snippet of the code that you use to get the data from the
selected item in a combobox

'Create Address
Dim strOffAddress as String
strOffAddress = ""
cmbEntities.BoundColumn = 3
strOffAddress = strOffAddress & cmbEntities.Value
cmbEntities.BoundColumn = 4
If Len(cmbEntities.Value) > 0 Then
strOffAddress = strOffAddress & vbCr & cmbEntities.Value
End If
cmbEntities.BoundColumn = 5
If Len(cmbEntities.Value) > 0 Then
strOffAddress = strOffAddress & vbCr & cmbEntities.Value
End If
cmbEntities.BoundColumn = 6
If Len(cmbEntities.Value) > 0 Then
strOffAddress = strOffAddress & vbCr & cmbEntities.Value
End If

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Dear all,
>
[quoted text clipped - 21 lines]
>
> Fred;

Reply to this Message

fredduc@gmail.com - 26 Mar 2008 22:42 GMT

OK, thank you for this answer already. I've now done this tutorial on
that website you mentioned and that worked (quite easy).

I'll try these other things in the coming days and I'll let you know
the result.

Reply to this Message