Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Putting unique graphs from excel 2010 into word 2010

    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

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    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
    Y
    our 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
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    The following Excel macro will create an image file of every chart on the active sheet:
    Code:
    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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •