Putting unique graphs from excel 2010 into word 2010

[Oldschool]

Hi Everyone

I have a slight predicament. I have a series of worksheets in an excel workbook. One of the worksheets contains columns of activities whilst the rows have unique students identified by student number, this is then formatted as a table. I have created a word mail merge of their results so that each student gets a page with their details compared to the cohort.

I read on the forum that you 'just make a chart of each row, create a column with the name of the unique chart in it, and use LINK with an embedded merge' to insert the chart. But HOW do you do this. I called my Chart 'Deens', put a column called charts and in Mr. Deens' row, put the name 'Deens' in the column.
Then I tried everything I could find to write the code for the LINK command, but to no avail!

Any help would be exceptional, but I am severely limited in the coding department having only found out yesterday that you could actually code!! TReat me as a Dummy!!!

Thanks in advance for your help.

Carl

[macropod]

You can't use LINK fields in a mailmerge unless the links are all for the same item - which in this case they're not. If you were to save the charts as separate image files, and had the filenames in your data (or implied in your data by using, say, the student ID), you could use INCLUDEPICTURE fields to import the charts.

[Andrew Lockton]

Have you made all the charts yet? If so, copy one of them and then go to Word and choose Paste Special as Link. Now reveal the field code (select it and press Shift F9) and have a look at the entry. This is the pattern that needs to be rebuilt using the merge field entry.

The basic principle is explained here http://www.computorcompanion.com/LPMArticle.asp?ID=126
Your code will be slightly more complex since you need to link to a graphic within a file rather than a standalone file. The syntax revealed by the field code.

You should end up with a nested merge field that looks something like this
{LINK Excel.Sheet.12 "C:\\Folder\\Path\\To\\ExcelCharts.xlsx" "Sheet1![ExcelCharts.xlsx]Sheet1 {Mergefield ChartName1}" \a \p}

Hint: You can name the charts in Excel by going to Chart Tools > Layout > Chart Name

[macropod]

Hi Andrew,

There's nothing in that link to suggest a LINK field can be used. It all relates to INCLUDEPICTURE fields, as per my post. You cannot use LINK fields because they immediately resolve their code to a literal string as soon as they're updated.

[Andrew Lockton]

Thanks for correcting me Macropod. Obviously, I didn't test this before posting.

In that case, as you stated, the output charts will need to be exported to a graphic file format before they can be included in the merge using IncludePicture. I don't see a reasonable way to do this short of exporting the chart as a PDF and then using third party tools to convert the PDF to something that can be used with IncludePicture.

Is there a command somewhere that lets us export a chart as an EMF or PNG? I was thinking you could use Copy As Picture and then save the clipboard to a file but I'm not so sure that is possible.

[macropod]

The following Excel macro will create an image file of every chart on the active sheet:

Sub ExtractGraphs()
Dim Cht As ChartObject, StrPath As String
With ActiveSheet
  StrPath = ActiveWorkbook.Path
  For Each Cht In .ChartObjects
      Cht.Chart.Export Filename:=StrPath & "\" & Cht.Name & ".gif", FilterName:="GIF"
  Next
End With
End Sub

I chose the GIF format to avoid the pixelation, etc you can get with JPG images. If the charts have have the student IDs, for example, as their names, the saved names will be rather more meaningful than Chart1.gif, etc. Even without that, it should be possible to obtain a relevant name by referencing something in the chart or the range it refers to. Without a sample workbook, though, this is as far as I can take it.