[Walter Briscoe]

In message of Mon, 8 Sep 2008
07:56:49 in microsoft.public.word.mailmerge.fields, macropod
writes

macropod,
Thanks for a prompt reply. The 9 hours difference between the UK and
Western Australia slows communication. I assume spring for you is dry.
Autumn in the UK is depressingly wet. (That's allowed me to confirm the
stereotype of Poms' weather obsession.)

Hi Walter,

I am unable to create my own document to mimic "Mailmerge Main Document.doc".

The procedures for creating your own mailmerge main document, linked to
your own data source, are set out in section headed 'Set Up the Main
Mailmerge Document'.


I confess I had followed those instructions without giving them much
attention. (I am using Word 2003 SP1 on Windows XP SP2) I read them
again. I set Tools/Options/General/Confirm conversion at Open" to get
the "Confirm Data Source" dialog (sic) where "OLE DB Database Files"
defaulted and "MS Excel Worksheets via DDE (*.xls)" and "Excel Files via
ODBC (*.xls)" were also offered. A "Show all" checkbox causes many more
offerings to be made. I decided to restrict my testing to those 3
offerings and to record macros to give some impression of what was
happening under the hood.
On reflection, I won't report my macros. I assume the work has already
been reported and would appreciate a reference.

"Excel Files via ODBC (*.xls)" caused a "Select Dialog" dialog to be
opened with nothing to select. I had to check "Tables", "Views", and
"System Tables" in the "Table Options" dialog selected by clicking the
"Options..." button to cause "Sheet1$" to be selectable and I duly
selected it.

The following were displayed:
Confirm Data Source Sales Selection from
OLE DB Database Files 2100 [Sheet1$] in "a.xls"
MS Excel Worksheets via DDE (*.xls) $2,100 [a.xl] in "a.xls!Entire Spreadsheet"
Excel Files via ODBC (*.xls) 2100. [Sheet1$] in "a.xls"

The connection to "Catalogue Merge Data.xls" from "Mailmerge Main
Document.doc"
uses a technique which is hidden to me.

"MS Excel Worksheets via DDE (*.xls)" seems to do what you do.


2. The 'Main Mailmerge Document' attached to the tutorial has already
been through the process described at 1, above.

The connection also allows money amounts like "$8,000" to be passed
where I see "8000"

3. The $ symbols in the tutorial's merged output appear in the
tutorial's data file. If they don't appear in your data file and you
want them in your output, you'll need to format the relevant
mergefields with the appropriate numeric picture switch. The field
coding under the heading 'Format the Output and/or Insert Additional
Text Before the Repeated Data' has such a switch (see also Word's help
file for more details).

You use "{MERGEFIELD Sales \# $,0.00}". (I typed that. I have been
foolish enough not to note a recent posting on using a VBA macro to copy
field codes and now can't find it. ;( )


Much less significant is the display of "Coffs Harbour" on a line
which has "Justify" set. On my example, Harbour is thrown to the right
by the justification; you avoid it. How?

4. In the tutorial, the paragraphs containing the mergefields were
formatted with left & decimal tabs at 5 and 10cm, respectively. That's
what controls the paragraph layout. Obviously, if you paste the code
into a paragraph with a different layout, the results will differ.


I am sorry to say I think something else applies. Neither "Mailmerge
Main Document.doc" nor my document have tabs set but the tabs in field
codes in "Catalogue Mailmerge Tutorial.doc" are passed through to the
output document when trying "Merge Records By Category". As I said, I
think this is less important.

Thanks again for your response. It caused me to learn a lot and leaves
me with more to learn from your work.
--
Walter Briscoe