Copy link to clipboard
Copied
Good morning. I'm hoping someone here can help. I have a pie graph, which uses data merge. The pie graph requires labels, not in a key, but on the outside of the graph. The labels use the same data merge fields as the graph. As the values in the pie graph change/move, we want the labels on the outside to move also. I've tried grouping the text boxes, but the values remain stationary and I have researched for a few days and haven't found a solution that works. Is this possible in InDesign?
jleigh987 wrote
… Uwe, I started reading about text-to-paths and to see if I can separate the fields. I didn't have enough time to accomplish anything, but will hopefully be able to return to that on Monday also.
Hi Jodi,
you need to do nothing with the converted text-to-paths.
My script will convert text to path without changing the original text formatted with Chartwell Pie.
And it will throw away the group of paths after the calculation of labels is done.
And more: You even need not provide any te
...Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
Hello! Thank you for the reply. The graph was created in InDesign using the Chartwell font. I moved my graph into a new document and created some fake data to represent what we are attempting to do. I don't see an option to attach the indd file and the data? I have added screenshots below. Thank you for the "insert image" help too
Before using preview:
Preview, record 1, the labels (6, 18, 76) are accurate with the pie sections:
Preview, record 2, the labels are not accurate with the pie sections:
Preview, record 3, the labels are accurate with the pie sections, but only by chance:
Copy link to clipboard
Copied
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:
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.
Copy link to clipboard
Copied
Ahh this is what happens when you get distracted and post a reply late!
Copy link to clipboard
Copied
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...
Copy link to clipboard
Copied
Thank you for your response and apologies for not responding right off. I kept receiving a redirect error when logging into the forum. I will try your suggestion! It has to be less painful than moving the fields for every unit, which is where the path is leading to currently.
Copy link to clipboard
Copied
Hi together,
to the OP: Thank you for posting your screenshots and to mention that the charts were done with Chartwell.
Vinny's approach is looking good.
But it comes with some problems and some plus points as well !
Problem 1: RGB colors perhaps.
Problem 2: Pixel graphics. But only perhaps. Maybe there is a way to save to PDF and get vector ones?
Can resolution be controlled? A big question mark here, because the labels would also be rasterized.
On the plus side:
Plus 1: Controllable labels.
Plus 2 ( and that's a very big plus for a lot of users 😞 Support of floating numbers.
Chartwell will not support floats as far as I know. Just integers. And that's a big minus on the Chartwell side of things.
So back to the Chartwell solution if missing floats are no issue:
Would it be possible to transform a chart done by Chartwell to paths using InDesign's text-to-path feature?
Doing a duplicate on top of the selected text maybe could be done by holding the alt key when using the menu command.
Did you ever tried this?
I'm looking for a chance, that a script is able to do the labels in a post-processing step after datamerge.
For that we could need—maybe not necessarily?—the individual segments as individual objects.
The text contents for the individual labels would be clear from looking at the range of text Chartwell is forming the graph from.
And also is provided by the datamerge.
Don't know, but maybe someone already tackled the problem somewhere on the net?
It seems, that doing a proper legend with Chartwell generated graphs is a pressing need for all people using the font.
Regards,
Uwe
Copy link to clipboard
Copied
Uwe, you are correct. I played around with Vinny's suggestion, but we realized this will not work for us either. In Excel, the percentage values moved to the inside of the pie on occasion and the quality isn't as crisp as we need it to be for print production.
I tried grouping the four text boxes and also anchoring, but neither worked for this issue. I should also mention that I am teaching myself ID. I have a lot of experience with Photoshop and Illustrator, so not a huge learning curve, but enough that Google and I are best friends. I have not tried the text-to-path feature and duplicating on top of the selected text. I will do some reading.
Copy link to clipboard
Copied
Hi,
just test the following to convert text to path as duplicates.
First have a test on text not formatted with Chartwell to get used to it…
If you are comfortable:
[1] Select the text that is rendering the pie chart.
[2] Go to menu Type > Convert To Path but do not immediately release the menu command.
[3] Hold down the alt key ( when on a Mac )—I see you are not, so press the modifier key for making duplicates—and click the menu action.
If all goes well the selected text should be converted to paths as duplicates.
You now should have access to a combined path object with several path segments of the pie chart.
If that would not work you may end up with something like this converted to paths: 76 + 6 +18
( Don't know where Chartwell starts with the pie segments. At 12 o' clock position perhaps and building the pie segments clockwise… )
Then a script would need other means to identify the center of the chart and its diameter.
Regards,
Uwe
Copy link to clipboard
Copied
Hello Uwe,
Thank you for your suggestion and instructions. You are correct. The first field, using the Chartwell pie font, begins at 12 o'clock. I'm not exactly sure what you're talking about, but I will follow your instructions and report back!
Jodi
Copy link to clipboard
Copied
Hi Jodi,
please ask specifics if you did not understand something.
What I really want is to explore some options with doing a script that could run after the datamerge is done and applies the labels to all merged charts in one go. And as a start it could be of opportunity to get access to pie chart segments of the actual charts without calculating too much. I do not have Chartwell fonts available to me so I am relying a bit on guesswork and some videos on the web that are showing Chartwell at work.
Regards,
Uwe
Copy link to clipboard
Copied
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:
Copy link to clipboard
Copied
Hi Vinny,
great! Unfortunatey I'm on Mac OSX so VB seems to be no option for me.
Or is Excel on a Mac supporting enough VB to test your script? Provided the file paths are changed of course.
Behind the scenes Jodi provided one chart converted from Chartwell formatted text as duplicate to outlines. That made me confident enough to start writing a script in ExtendScript for InDesign that is doing the labeling for Chartwell pie graphics …
And I'm making good progress with that.
Just a feasibility study for now.
Regards,
Uwe
Copy link to clipboard
Copied
Hi uwe.
I have absolutely no idea on how Excel macros work on a Mac... What I know is that the formulas use the French functions, so they must be replaced with local ones.
Keep on study the chartwell solution... That's cool!
Copy link to clipboard
Copied
Vinny, that is awesome! I exported images as gif and jpg a couple of days ago. I toyed with trying to export the maps as PDF, but I decided not to because I thought that would bring me back to one-offing using "place" functionality right?
I may not be able to return to working on this until Monday, but I want to see if I can export PNG (the map needs to remain on a transparent background) images and will check the quality. French functions might be an issue also, but maybe my buddy Google can help with that as well.
Uwe, I started reading about text-to-paths and to see if I can separate the fields. I didn't have enough time to accomplish anything, but will hopefully be able to return to that on Monday also.
Thanks to both of your for your help.
Copy link to clipboard
Copied
Hi Jodi
If you want to export a transparent PNG, don't forget to remove both default white background and stroke of your chart.
I've tested it, and it works fine
Copy link to clipboard
Copied
jleigh987 wrote
… Uwe, I started reading about text-to-paths and to see if I can separate the fields. I didn't have enough time to accomplish anything, but will hopefully be able to return to that on Monday also.
Hi Jodi,
you need to do nothing with the converted text-to-paths.
My script will convert text to path without changing the original text formatted with Chartwell Pie.
And it will throw away the group of paths after the calculation of labels is done.
And more: You even need not provide any text frames before the datamerge process for the labels in the template.
The script would create them on-the-fly using an object style one could provide with the InDesign template document.
Below just a sequence of screenshots showing a sample that is working with a selection of the text frame holding the chart.
The contents of the frame is: 76+6+18+B
Means, the chart is build with pies from values of 76%, 6% and 18% of a circle. B means make a "ring" form. This is not showing here, because after doing text to path with the text the inner circle is stacked behind the pie segments. All paths are grouped.
I'm showing the converted paths only, because I have no Chartwell font at hand and doing all calculations from the formatted text contents provided.
The typography of the label and the look of the labels' text frames is dictated by an object style provided in the document:
"ChartwellLabels". The applied paragraph style for this style is named "ChartwellLabel". If the object style is not there the script will build a default style you can later change to your needs.
1. Before doing the script action
To run the script select a text frame holding the necessary information: 76+6+18 for calculating the angle of the label text frames. You cannot see the contents here for demonstration purposes. Usually it would be in color to dictate the fill color of the pie segements.
Note: To make the script action work, the selected text frame must be not overset.
2. After running the script.
I assume, that the label text frames should be positioned to the half of the angle of every individual piece of pie:
3. The label frames are formatted by the object style.
in my German InDesign you can see the Object Styles panel where object style "ChartwellLabels" is selected.
4. The text is formatted with a paragraph style that comes with the object style: "ChartwellLabel".
5. As you can see below, the text frames of the labels are all centered around the pie chart.
The center of each text frame is on an imaginary outer circle.
6. After changing the text size the center of text frames is still on the outer circle.
This effect is possible because the object style is coming with a fit to center setting.
But please do not hold your breath, the script is not ready yet.
Regards,
Uwe
Copy link to clipboard
Copied
Hi Uwe.
The script you mentioned in comment was released? Or still in progress?
Copy link to clipboard
Copied
Hi Ahmet,
you can contact me through private message regarding the script.
Just hover over my name, wait a second and then press the Message button.
Regards,
Uwe
Copy link to clipboard
Copied
Hi.. Is the script still available?
Copy link to clipboard
Copied
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