Charts in a mail merge doc
Heather Allen
chart. I have set the statements up as a mail merge form
letter mailing using an excel spreadsheet as my data
source. Is there a way to set up one main chart and have
it use the corresponding mail merge field for each merged
document?
Peter Jamieson
area recently - she's not around as far as I know, but I don't think she
will mind if I post her two replies:
--------------------------
With an Excel data source, there are three ways, basically, you can go. None
of them are particulary easy :-)
1) Create all the charts in Excel (preferably as separate sheets, but they
can
also be objects on sheets). Add a column to the data source that contains
the
name of the appropriate chart sheet/object. Insert this into a Word LINK
field, to bring the chart across into the merge result.
2) Use a DATABASE field to bring the table of data for the chart into the
merge document. Create an MSGraph linked to that table. Don't execute the
merge; just preview the data, "walk" through the records, and print out each
preview result.
3) As above, but execute the merge to a new document, then create the
MSGraphs
All of these can be automated using VBA. The fourth possibility is to simply
not use mail merge at all and generate the documents entirely with VBA.
--------------------------
followed by some clarifications...
--------------------------
A couple of remarks:
1) Be sure to set the bookmark to enclose the entire DATABASE field
that holds the table. (This includes a paragraph mark, just above the
table, but that won't affect the data table in MS Graph)
2) Do a Ctrl+A, F9 to force the database field (and graph) to update
as you move from record to record. (Sometimes, I have to double-click
the graph to get it to react)
--------------------------
--
Peter Jamieson
MS Word MVP
"Heather Allen" <all...@kochind.com> wrote in message
news:038801c2d21f$0b40a400$a201...@phx.gbl...
Jennifer Roos
showing up; please excuse the possible duplication.)
I'm trying to do a similar project: mail merge in Word
with an Excel data source. I've read Cindy's posts which
are very helpful. However, I have a concern: My merge
project has 750 records; each record has 80 fields PLUS 3
graphs (different for each record). Cindy suggests
creating all charts in Excel as separate sheets and then
merging them in. But that gives me over 2,000 sheets (&
charts) in Excel -- is this possible? Will Excel handle
that many sheets in one spreadsheet???
Thanks in advance for your help,
Jennifer
>.
>
Cindy Meister -WordMVP-
It's not absolutely necessary to create each chart on a
separate sheet. What you do need, however, is the object
NAME for each and every chart that's not on a separate
sheet. These tend to be a bit cryptic (and not obvious) if
Excel has generated the chart objects on worksheets. That's
why I recommend a separate sheet :-)
BTW, the charts don't necessarily even have to be in the
same workbook file as the data. The key to getting
everything to work is having the exactly correct path in the
LINK field Word uses to bring the Chart info across.
> I have a concern: My merge
> project has 750 records; each record has 80 fields PLUS 3
> graphs (different for each record). Cindy suggests
> creating all charts in Excel as separate sheets and then
> merging them in. But that gives me over 2,000 sheets (&
> charts) in Excel -- is this possible? Will Excel handle
> that many sheets in one spreadsheet???
>
Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan
24 2003)
http://www.mvps.org/word
This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail
:-)
Cindy Meister -WordMVP-
You'd need to ask this in an Excel group; I'd be skeptical,
too :-)
FWIW, the charts can be in separate workbooks. In this case,
you'd need a merge field in the LINK field for the workbook
name (try to keep the same path, otherwise things get much
too complicated), as well as one for the chart name. Two
additional fields in the data source, rather than one.
> Hi -- I am trying to do the same thing: use Excel as my
> data source, bringing over text, numbers AND (hopefully)
> graphs. I've been reading through this Newsgroup and found
> the very helpful posts from Cindy.
>
> However, I have a concern: I will have 750 records for
> this mail merge. Each record has about 80 fields of data
> PLUS 3 charts per record. Cindy's method #1 suggests
> creating all the charts in Excel as separate sheets and
> then pulling them over. 750x3=2,250 charts, thus 2,250
> separate sheets. Is this feasible for Excel? (I'm not an
> Excel power user!)
