This is useful when doing a mail merge where you may require a subsequent mail merge. For instance, you may have a large list of senders and recipients. Creating a normal mail merge based on the senders would mean that subsequent mail merge to the recipients cannot be achieved (in Word 97). The method I have used creates text entries based on the selections of the template user and has no ties to the data source, leaving the user free to perform a further mail merge as required.
--------- STEP 1 ---------
The first step is to download two files from Microsoft and install them on each machine. These can be found on the URLÆs below.
If you do not install the same version then you will receive a ôCompile Error: CanÆt Find Project Or Libraryö
--------- STEP 2 --------- The second step is to create the database table in Access. No other coding or setup is required in Access.
--------- STEP 3 ---------
The third step is to use the code below in Word:
Put This code in the form. -------------------------------
' For the ADODB object to work you must set a reference ' Select TOOL, REFERENCES from the visual basic menu bar ' Select the MICROSOFT ACTIVE X DATA OBJECTS 2.1 LIBRARY
' Once set within the template they do not need setting again Dim oConn As ADODB.Connection Dim oRS As ADODB.Recordset
Now you need to create an array to store the details
Private Sub UserForm_Activate()
Dim nRowCount As Integer Dim nColCount As Integer Dim sArray() Dim ArrayRowCounter As Integer Dim ArrayColCounter As Integer
' Create a new ADO connection Set oConn = CreateObject("ADODB.Connection") ' Open our connect (NOTE the OLEDB.4.0 depends on what version of DCOM is installed) oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFileName & ";Persist Security Info=False" ' Execute a SQL statement to retrieve the information Set oRS = oConn.Execute(" SELECT SenderName, ID, SenderTitle, SenderDivision, SenderAddress1, SenderAddress2, SenderAddress3, SenderAddress4, SenderAddress5 FROM Test")
' Store column count to variable nColCount = oRS.Fields.count
' move to first record in file oRS.MoveFirst
' count the number of columns to size the array nRowCount = 0 Do nRowCount = nRowCount + 1 oRS.MoveNext Loop Until oRS.EOF = True
' Build the array x rows long and y columns wide ReDim sArray(nRowCount, nColCount)
' Move to the top of the recordset oRS.MoveFirst
' Put information into the array For ArrayRowCounter = 1 To nRowCount For ArrayColCounter = 0 To (nColCount - 1) ' Put the value of the field in the array cell sArray(ArrayRowCounter, ArrayColCounter) = oRS.Fields(ArrayColCounter) Next ArrayColCounter ' Move to the next recordset oRS.MoveNext Next
' Specify the number and size of columns for the dropdown cboSender.ColumnCount = nColCount ' The size of all the columns, except the name, are hidden by having a 0 width cboSender.ColumnWidths = "2.3in;0in;0in;0in;0in;0in;0in;0in;0in;0in" ' Populate the combobox with the array cboSender.List() = sArray
' close the recordset oRS.Close
End Sub
--------- STEP 4 ---------
The final stage is to populate the document with the details, depending on the selection. Put the code in below:
Private Sub cmdPopulate_Click()
If IsNull(cboSender.Value) Then MsgBox "You must select a person from the list before the details can be copied to the letter" Else If Trim(cboSender.Value) = "" Or IsNull(cboSender.Value) = True Then MsgBox "You must select a person from the list before the details can be copied to the letter" Else 'Now create a new recordset to search for the value
' Create a new ADO connection Set oConn = CreateObject("ADODB.Connection") ' Open our connect oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFileName & ";Persist Security Info=False" ' Execute a SQL statement to retrieve the information Dim sqlstr As String sqlstr = " SELECT SenderName, ID, SenderTitle, SenderDivision, SenderAddress1, SenderAddress2, SenderAddress3, SenderAddress4, SenderAddress5 FROM Test WHERE ID=" + Str(cboSender.Value) + "" Set oRS = oConn.Execute(sqlstr)
Talk To Other Members