Merge data into existing tables

[John Cutler]

Hi, all ---

I originally posted this request on another newsgroup and
was sent to the "Mail Merge" group.

-------------

The reports I write are composed of a series of chapters,
each chapter containing 5 pages, each page with 3
identical tables. There is no other text (except headers
and footers) on a page.

Cells in a table are of varying size and content, but each
cell has a caption(such as "Date:") -- either followed
by data on the same line, or data on the next line down.
One cell in each table contains a photo.

To make this all clearer, SAMPLES of the page showing
tables (including captions and data) are available on
request...

All the data that go in these tables are currently being
typed in by hand, but data for most of the cells
(including the file name of the photo) aready exist in a
database.

Being able to automatically send the database content to
the correct table cells would speed up the project and
save a lot of typing errors!

I am working with Word97, Win 2000 Pro.

The database containing the data is ODBC compliant but is
not Access. My database will directly export to dBASE
format if that is useful.

If necessary, I can probably figure out a way to get the
data into an Access database, but I have no prior
experience with either Access or Mail Merge in Word... (I
have done mailmerge with other database/wordprocessor
software).

Thanks

John Cutler
Mapping Director / Ozark Regional Land Trust
(e-mail address removed)

 

[Peter Jamieson]

Roughly speaking, there are a few ways you could approach this.

Word mailmerge would probably only be useful if all the data for a single
report exists in a single row in your database, and if all the data
(including any long text fields) is made available to Word via the ODBC
driver that comes with your database software. In that case you could
consider setting up your database as the data source for a merge and using
{ MERGEFIELD } fields to insert text data, and a nested { INCLUDEPICTURE {
MERGEFIELD picturepathnamefield } } to include the picture. Then merge to a
new document. You might also want to select the resulting document and
"unlink" all its fields as well.

You could also consider using { DATABASE } fields (use Word Tools|Customize
to enable the DATBASE toolbar and see if you can retrieve data from your
database using the Insert|Database icon. Ensure that in the last step, you
insert the data as a field). You may find that you simply can't insert data
using this approach. But if you can, as long as you only select one column
and specify that you do not want headings, Word will return plain text
rather than a table. As long as you know where to get each piece of data in
your report from you should be able to make multiple copies of your DATABASE
field, modifying the SQL in each copy to get the data you need. Inserting
the pictures would also involve a nested field using

{ INCLUDEPICTURE { DATABASE ...whatever... } }

Then select the entire document and press F9 to update all the fields. The
challenge in this approach would probably be to make it easy to change which
database, table(s) or record(s) the data would need to come from for each
report. However, there are several possible ways to do that, including the
straightforward Edit|Replace approach.

The third way would be to use VBA to get your data, and to populate each
part of the document as necessary. Since your database supports ODBC it
ought to be possible to use ADO to open it and retrieve data (using the
OLEDB provider for ODBC data sources. If your database also supports OLEDB,
it should be more straightforward). Although going along this route might
give you a lot more control (and in fact it might be the only simple way to
automate your task) there's quite a lot to learn if you haven't done this
kind of stuff before and have no resource to do it for you.

 

[John Cutler]

Peter --

Thanks for the reply.....

Wow! It's a mind-boggler....! I don't even know how to
respond to your suggestions, or where to begin....

The mailmerge I've done with other database/wordprocessor
packages was pretty straight forward -- "Send" (export)
the selected data fields from selected records in the
database to a comma-delimited text file. The "Send"
command also opened the wordprocessor. The user had to
load the document to receive the data, and hit "Merge".
(All of these commands could be issued under program
control as well as manually -- my job was writing the
programming...).

The wordprocessor automatically knew where to find
the "sent" data file and plugged it into the mailmerge
fields in the document -- in this case "late letters" that
were sent to delinquent accounts detailing balance due,
etc.

--------------------------
Do you have any suggestions for resources to guide me
through this kind of job? People? Knowledgebase documents?
Books? I realize it could be a long, steep learning
curve, but in the long run, I think the time savings and
accuracy in producing the reports will be worth it...

Thanks

John

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi John,

Send me an example so I can get a better idea of what your report looks
like. Also probably and typical record which I assume you can get into
Excel from the dBase format

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP