Pie graph with labels using data merge

Hi Vinny, I didn't have time to play yesterday, so I haven't tested the script with PNG output output yet. I will try to fit this into my schedule this week so I can report back on the results because I think this can be a workable solution if the printed output looks okay.

Hi Uwe, the screenshots are amazing and your assumptions are correct. The script calculates the position, in the outer circle, on-the-fly? Mind blown.

I apologize to you both for not being able to focus on this yet!

Jodi

Hi guys

I'd like to come back to the Excel workaround.

Possibly Uwe's solution will work great for you but maybe other users - not using Chartwell - may be interested in an Excel workaround.

So basically, we had two issues: (not talking about RGB... not an issue really)

- Quality/rasterization:

Well, afaik you can't save a chart only in PDF but you can place the chart in an empty sheet and export the sheet as PDF. This requires quite a few adjustments, so I won't detail it. You can find many macros Googling "Excel VBA export PDF". Here's one: Excel VBA to Save As PDF

However, you can very easily save a high quality image increasing the zoom in the VBA macro. In the example below, I used PNG instead of JPG.

- Label positioning:

That was the tricky part. However, everything is explained in this great tutorial: https://teylyn.com/articles/excel-doughnut-chart-with-leader-lines/

Don't forget to set the lines as invisible if you don't want them.

The idea is to create a single chart, and to write a macro that does:

1- change the source data of the chart

2- export the chart in high-quality PNG

3- copy the name of the file on an "export" sheet

4- loop to 1 as long as there are existing data

5- save the xlsx file

6- Finally export "export sheet" as tabbed .txt for data merging

Here it is:

Sub test()
'
' test Macro
'
' Set a non-empty rows counter (based on column data1):
Dim rows As Integer
rows = (Application.WorksheetFunction.CountA(Columns(2)) - 1)
' Set a variable for looping through chart series formula:
' Set to 2 in this example because 1 is the header
Dim i As Integer
i = 2
'While loop: non-empty rows
While rows > 0
    
    '   Select graph and change range values:
        ActiveSheet.ChartObjects("Graph").Activate
        ActiveChart.SeriesCollection(2).Select
        Selection.Formula = "=SERIES(,,datas!R" & i & "C2:R" & i & "C4,2)"
        ActiveChart.SeriesCollection(1).Select
        Selection.Formula = "=SERIES(,,datas!R" & i & "C2:R" & i & "C4,1)"
    '   Export graph as image:
    '   Increase next line zoom for higher size
        ActiveWindow.Zoom = 175
        ActiveChart.Export "C:\Users\vs\Desktop\graphs\" & range("E" & i & "").Value, "PNG"
        ActiveWindow.Zoom = 100
    
    ' Paste and copy image name to export sheet
        Sheets("datas").Select
        range("E" & i).Select
        Selection.Copy
        
        Sheets("export").Select
        range("A" & i).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        
        Sheets("datas").Select
    '   iterate:
        i = i + 1
        rows = rows - 1
Wend
'save file
    ActiveWorkbook.Save
'Now lets select "export" Sheet and save a tabbed .txt file for data merging. Then close it
    Sheets("export").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\vs\Desktop\graphs\graph.txt", _
    FileFormat:=xlText, CreateBackup:=False
    ActiveWorkbook.Close savechanges:=False
    
End Sub

Don't forget to name your chart (see line 22)

Here's a link to the excel file (but... French version!) : https://we.tl/7g1i15g2DM

Final result:

Hi

If I had to do it, I would forget FF Chartwell and do it all in Excel:

Then create a custom Macro. Something like this one would save all graphics, then save "Export" sheet as .txt or .csv for data merging.

Sub Test()
Dim sht As Worksheet, cht As ChartObject
Dim x As Integer
    For Each sht In ActiveWorkbook.Sheets
        x = 1
        For Each cht In sht.ChartObjects
            cht.Chart.Export "C:\Users\vs\Desktop\test\" & sht.Name _
                              & "_" & x & ".jpg", "JPEG"
            x = x + 1
        Next cht
    Next sht
    
    Sheets("export").Select
    ActiveWorkbook.SaveAs Filename:="yourpath\graph.txt", _
        FileFormat:=xlText, CreateBackup:=False
        
        ActiveWorkbook.Close savechanges:=False
End Sub

I fully understand that:

- this does not answer the OP's request

- this would rasterize everything

- this is not an Excel forum ^^

- and that I forgot to add the .JPG extension in my example. I should correct this, but I got to go...

As Laubender​ touched on - I think this would also require use of custom script to do the way we have understood you.

Otherwise - as I'm sure you are aware - you are looking at a manually updating the charts either by:

• Directly copy and pasting the file into InDesign and repeating whenever needing updates if there are only a one or two in use.
• Copying and pasting to Illustrator if you want to make the chart prettier then placing that into InDesign.
• Exporting the charts from Excel to PDF using the "Save as Adobe PDF" feature and then selecting all sheets that have charts... Then placing this into InDesign. You would need to remember that each data sheet from e.g. Excel would act as a flattened image. Therefore only include one chart per sheet.

However without a script you would have to repeat this every time. So scripting is again the best method for now.

You could submit a feature request if you think that you should be able to link and place charts directly from excel into InDesign.

Feature Request/Bug Report Form

Hi,

I cannot think of a procedure where you can achieve what you want with data merge.

If I understood your request right, only a custom script would be able to do that.

How is the pie chart done? Adobe Illustrator?

Can you show a screenshot of that pie chart with attached labels?

Before the merge. After the merge. Hidden characters and frame edges showing.

Use the forum's "Insert Image" to do an inline graphic with your post. JPEG and PNG file formats.

Regards,
Uwe