Hi all,



I don't have much experience of mail merges but wondered if I might be able to use it to solve a problem,

I work in a school, there are 2 sections to our reports, each one A4 page, that need printing back-to-back. One is an XML report from SIMS that opens in Word, the other an Excel graph. Both documents have a number in the filename that is unique to each student, I wondered if it was possible to use this number to add the graph as a second page to the other document? They go out 6 times a year so it's quite time-consuming at the moment

Adam

Hi Adam,

It would probably be possible to use a Word macro(vba) to add the graph to the document, either at print time (perhaps without even saving the changed document), or at some earlier time. Before commenting further, though, I'd need to know whether there's anything in the Word document that can be used to identify the relevant Excel file, whether the Word and Excel files are stored in the same folders or if there's at least a pattern to identifying the path to the folder(s) in which the Excel files are stored, how the Excel files are structured (eg is the graph on the same page as the data, or on a separate sheet).

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks for the reply Paul,

The Excel file has the graph as the first sheet, with the data that it comes from on the second. There are a couple of text boxes and lines placed over the top of the graph but it would probably be ok without them, I could alter the template to remove them if necessary.

There's something that could probably be used to identify the Excel files in cell A1 of the data sheet, it's a constant [surname] [forename] [admission number]. Nothing in the report to match it at the moment but I could easily set it up to have the same thing on the first line and use white text to hide it.

Currently the Excel and Word documents are kept in completely different locations but that's open to be changed if it makes things easier - the next set of reports aren't due for a couple of weeks so I can just set it to create both documents in the same folder when that comes round.

Unfortunately I don't have much experience with macros/vba beyond using other peoples' code and trying to work out what it's doing (that's why I was hoping a mail merge could do it!), guess 2 weeks gives me some time to look at it though

Hi Adam,

Mailmerge wont work for the following reasons:
• A mailmerge can only accept one worksheet as a data source
• To link to a chart, you'd need to embed a MERGEFIELD inside a LINK field. Unfortunately, as soon as you do that and update the LINK field, it's contents get resolved to text - and the MERGEFIELD gets deleted.
• The fact you have both charts and data in the workbook suggests there's one workbook per student. Mailmerges are designed to work with situations where all the student records are in the same workbook.

Assuming the last point above is correct, then provided your workbook's worksheet and chartsheet names are consistent, a macro can interrogate each of them easily enough without anything to match within each of them. If all the Excel workbooks are in a common folder, a macro could easily enough loop through all files in the folder to produce a report for every student in one go - and save the reports to whatever location you want.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Yes, there is one workbook per student, I'll have a look at using a macro. The chartsheet always has the same name so that sounds like a good way to go, you do get the occasional student with the graph document missing but it's not difficult to check for that before running it Thanks for the help

You could, of course, let the macro do that check and either skip the report or output it without the chart.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]