The following code, written for use with Word 2007 and an Excel 2007 data
source requires:
1. In the Visual Basic Editor - a reference must be set to the Microsoft
Office 12.0 Access database engine Object Library
If using earlier versions of Office, the reference should be set to
the Microsoft DAO 3.6 Object Library and the following line of the code
Set db = OpenDatabase(DataSource, False, False, "Excel 12.0;
IMEX=1;")
must be replaced with
Set db = OpenDatabase(DataSource, False, False, "Excel 8.0;
IMEX=1;")
I do not know the equivalent of these setup steps with a Mac. If you
need assistance with that aspect of it, I would suggest that you post to one
of the Microsoft.Public.Mac.Office newsgroups.
2. In the Excel spreadsheet that is selected when the code is run, there
is a two column list with "field names" in the first column and the
corresponding values in the second column. It assumes that the first row is
a header row - probably containing the column titles "field name" and
"value" (but what is actually in the cells of that row does not really
matter. The list, including the header row must be named "DataItems"
When the code runs, it will create a document variable for each record in
the datasource with the name of the variable being the item in the first
column of the spreadsheet and the value of the variable being the item in
the second column of the spreadsheet.
To facilitate the insertion of the Docvariable fields in the document, I
would create a userform with a listbox that was populated with the field
names from the first column of the spreadsheet and a button that when
clicked would insert a docvariable field into the documnet for the field
that was selected in the listbox. It would in fact be possible to construct
a user interface for this that had virtually the same functionality as the
mail merge interface.
Dim Msg, Style, Title, Response
Dim i As Long
Dim j As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varname As String
Dim varvalue As String
Dim fd As FileDialog
Dim DataSource As String
Dim avar As Variable
For Each avar In ActiveDocument.Variables
avar.Value = " "
Next avar
'Display the file selection dialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
.Title = "Select Data Source"
.InitialFileName = ""
If .Show = -1 Then
DataSource = .SelectedItems(1)
Else
Exit Sub
End If
End With
Set fd = Nothing
Msg = "Please ensure that Excel is closed before Continuing. Do you want to
continue?"
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Refresh Data" ' Define title.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
' Open the file containing the Data
Set db = OpenDatabase(DataSource, False, False, "Excel 12.0; IMEX=1;")
Set rs = db.OpenRecordset("SELECT * FROM `DataItems`")
With rs
.MoveFirst
For i = 0 To .RecordCount
ActiveDocument.Variables(Format(.Fields(0).Value)) =
..Fields(1).Value
.MoveNext
Next i
End With
' Cleanup
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Else
Exit Sub
End If
--
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
Would it be asking too much for some sample code to get me started? I