Chart questions

[David Thielen]

These are more using than programming but this is the forum I know and
I need it to do my programming:

1) How do you tell Word to embed the XLSX datasource for a chart in
the DOCX file?

2) How do you add a second set of data to a chart - and tell it to use
the right side for that set's Y axis?

3) Is there a way to tell Word/Excel that 4 charts should get the same
range for the X or Y axis (usually Y)? I know I can forc the min/max,
what I want though is that it auto-determines the min/max - but does
so against the data in all 4 charts so they scale the same.

??? - thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Colbert Zhou [MSFT]]

Hello Dave,

1). Actually, we do not need to set any special options to tell Word
embeded datasource workbook in it. By default, the data source workbook is
embedded. I can observe this fact no matter doing it manually or
programmatically. The codes I write for this look like,
-------------------------Codes----------------------------------
static void Main(string[] args)
{
object missing = Type.Missing;
Word.Application word = null;
word = new Word.Application();
word.Visible = true;
Word.Document doc = word.Documents.Add(ref missing, ref
missing, ref missing, ref missing);
Word.Chart wdChart =
doc.InlineShapes.AddChart(Microsoft.Office.Core.XlChartType.xl3DColumn, ref
missing).Chart;
Word.ChartData chartData = wdChart.ChartData;
Excel.Workbook dataWorkbook =
(Excel.Workbook)chartData.Workbook;
Excel.Worksheet dataSheet =
(Excel.Worksheet)dataWorkbook.Worksheets[1];
Excel.Range tRange = dataSheet.Cells.get_Range("A1", "B5");
Excel.ListObject tbl1 = dataSheet.ListObjects["Table1"];
tbl1.Resize(tRange);
((Excel.Range)dataSheet.Cells.get_Range("A2",
missing)).FormulaR1C1 = "Bikes";
((Excel.Range)dataSheet.Cells.get_Range("A3",
missing)).FormulaR1C1 = "Accessories";
((Excel.Range)dataSheet.Cells.get_Range("A4",
missing)).FormulaR1C1 = "Repairs";
((Excel.Range)dataSheet.Cells.get_Range("A5",
missing)).FormulaR1C1 = "Clothing";
((Excel.Range)dataSheet.Cells.get_Range("B2",
missing)).FormulaR1C1 = "1000";
((Excel.Range)dataSheet.Cells.get_Range("B3",
missing)).FormulaR1C1 = "2500";
((Excel.Range)dataSheet.Cells.get_Range("B4",
missing)).FormulaR1C1 = "4000";
((Excel.Range)dataSheet.Cells.get_Range("B5",
missing)).FormulaR1C1 = "3000";
wdChart.ChartTitle.Font.Italic = true;
wdChart.ChartTitle.Font.Size = 18;
wdChart.ChartTitle.Font.Color = Color.Black.ToArgb();
wdChart.ChartTitle.Text = "2007 Sales";
wdChart.ChartTitle.Format.Line.Visible =
Microsoft.Office.Core.MsoTriState.msoTrue;
wdChart.ChartTitle.Format.Line.ForeColor.RGB =
Color.Black.ToArgb();

wdChart.ApplyDataLabels(Word.XlDataLabelsType.xlDataLabelsShowLabel,
missing, missing, missing, missing, missing, missing, missing, missing,
missing);
dataWorkbook.Application.Quit();
}
__________________________________

After I save the close the Word document, I can verify from the zipped
openxml file that the data source workbook is embedded.

2). I did not catch this very much. Do you mean you want to re-select the
source data and format the Y axis? If that is case, we can
*. Resize the data source by calling the line in above codes
tbl1.Resize(tRange)
*. To configure the Y axis, we can use the following codes,
-----------Codes-----------------------------------------
Word.Axes axes = wdChart.Axes(Type.Missing,
Word.XlAxisGroup.xlPrimary);
axes[Word.XlAxisType.xlValue,
Word.XlAxisGroup.xlPrimary].HasTitle = true;
axes[Word.XlAxisType.xlValue,
Word.XlAxisGroup.xlPrimary].AxisTitle.Characters.Text = "tttt";
--------------------------------------------------------------
xlValue means the axis we set is the Y Axis.

3). I did not catch this question. Could you please elaborate it? Thanks.


Best regards,
Ji Zhou
Microsoft Online Community Support

 

[David Thielen]

Hi;

I should have been more clear. I am trying to do all of these actions
in Word/Excel, not programatically. I want to do it from Office to
compare the DOCX/XLSX file it creates that way with the one I am
creating programatically.

1) Every way I've posted an Excel chart into Word it stores the
filename of the Excel file and does not embed the Excel file. Is there
a way in Word to tell it to paste the chart with an embedded XLSX file
instead of a link?

2) Example - on one chart I want a bar graph for quantity of product
sold each day and a line graph for total sales each day. So the X axis
is the same for both but I want the Y axis for the bars on the left of
the chart and the Y axis for the lines on the right of the chart. I
know how to do this in Excel 2003 but can't figure out how to do it in
Excel 2007 (so I create it in Excel 2003, save it, then open it in
Excel 2007).

3) Example - I have 4 charts showing sales numbers for quarters 1, 2,
3, & 4 to give a picture of the year broken out by quarter. To make
the charts show the same scale, they need to have an identical minimum
and maximum on the Y axis. I can set that to be a hard-coded value but
I was wondering if there is a way to tell Excel to auto-calculate the
Y axis min/max using all 4 charts datasets and then use that on all 4.

thanks - dave

Hello Dave,

1). Actually, we do not need to set any special options to tell Word
embeded datasource workbook in it. By default, the data source workbook is
embedded. I can observe this fact no matter doing it manually or
programmatically. The codes I write for this look like,
-------------------------Codes----------------------------------
static void Main(string[] args)
{
object missing = Type.Missing;
Word.Application word = null;
word = new Word.Application();
word.Visible = true;
Word.Document doc = word.Documents.Add(ref missing, ref
missing, ref missing, ref missing);
Word.Chart wdChart =
doc.InlineShapes.AddChart(Microsoft.Office.Core.XlChartType.xl3DColumn, ref
missing).Chart;
Word.ChartData chartData = wdChart.ChartData;
Excel.Workbook dataWorkbook =
(Excel.Workbook)chartData.Workbook;
Excel.Worksheet dataSheet =
(Excel.Worksheet)dataWorkbook.Worksheets[1];
Excel.Range tRange = dataSheet.Cells.get_Range("A1", "B5");
Excel.ListObject tbl1 = dataSheet.ListObjects["Table1"];
tbl1.Resize(tRange);
((Excel.Range)dataSheet.Cells.get_Range("A2",
missing)).FormulaR1C1 = "Bikes";
((Excel.Range)dataSheet.Cells.get_Range("A3",
missing)).FormulaR1C1 = "Accessories";
((Excel.Range)dataSheet.Cells.get_Range("A4",
missing)).FormulaR1C1 = "Repairs";
((Excel.Range)dataSheet.Cells.get_Range("A5",
missing)).FormulaR1C1 = "Clothing";
((Excel.Range)dataSheet.Cells.get_Range("B2",
missing)).FormulaR1C1 = "1000";
((Excel.Range)dataSheet.Cells.get_Range("B3",
missing)).FormulaR1C1 = "2500";
((Excel.Range)dataSheet.Cells.get_Range("B4",
missing)).FormulaR1C1 = "4000";
((Excel.Range)dataSheet.Cells.get_Range("B5",
missing)).FormulaR1C1 = "3000";
wdChart.ChartTitle.Font.Italic = true;
wdChart.ChartTitle.Font.Size = 18;
wdChart.ChartTitle.Font.Color = Color.Black.ToArgb();
wdChart.ChartTitle.Text = "2007 Sales";
wdChart.ChartTitle.Format.Line.Visible =
Microsoft.Office.Core.MsoTriState.msoTrue;
wdChart.ChartTitle.Format.Line.ForeColor.RGB =
Color.Black.ToArgb();

wdChart.ApplyDataLabels(Word.XlDataLabelsType.xlDataLabelsShowLabel,
missing, missing, missing, missing, missing, missing, missing, missing,
missing);
dataWorkbook.Application.Quit();
}
__________________________________

After I save the close the Word document, I can verify from the zipped
openxml file that the data source workbook is embedded.

2). I did not catch this very much. Do you mean you want to re-select the
source data and format the Y axis? If that is case, we can
*. Resize the data source by calling the line in above codes
tbl1.Resize(tRange)
*. To configure the Y axis, we can use the following codes,
-----------Codes-----------------------------------------
Word.Axes axes = wdChart.Axes(Type.Missing,
Word.XlAxisGroup.xlPrimary);
axes[Word.XlAxisType.xlValue,
Word.XlAxisGroup.xlPrimary].HasTitle = true;
axes[Word.XlAxisType.xlValue,
Word.XlAxisGroup.xlPrimary].AxisTitle.Characters.Text = "tttt";
--------------------------------------------------------------
xlValue means the axis we set is the Y Axis.

3). I did not catch this question. Could you please elaborate it? Thanks.


Best regards,
Ji Zhou
Microsoft Online Community Support

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Colbert Zhou [MSFT]]

Hello Dave,

Sorry for following up late.

To the question1, in my test, I find that after I paste the Chart from
Excel into Word. Then, there will be smart tag appearing at the right
bottom corner of that chart. If we click the smart tag, we find options,
..Chart (linked to Excel data)
..Excel Chart(entire workbook)
..Paste as Picture
..Keep Source Formatting
..Use Destination Theme

If we choose the Excel Chart(entire workbook), then the chart is pasted as
an entier workbook embedded in the Word application.

For question2 and 3, indeed, I think we'd better post the product usage
cases in the corresponding Excel product newsgroups because these are not
related to the COM AddIn. For chart, it is,
http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.pu
blic.excel.charting&lang=en&cr=US

My opinions:
2. If you can share the Excel 2003 workbook you mentioned in 2 with me, I'd
like to investigate and consult my colleague who do Excel product support
to see if I can help. You can access me via (e-mail address removed). (I just
send you an email about case of GetRibbonButtonLocation)

3. Just as far as I know, I am afraid Excel cannot auto calculate the
Max/Min values on axis. But you may try the Chart newsgroup to see if
anyone can confirm this.

Have a nice day!


Best regards,
Ji Zhou
Microsoft Online Community Support

 

[David Thielen]

Bingo - I never saw that (1) before - thank you.

I got 2 & 3 from asking on the product newsgroup - so all good.

thanks - dave

Hello Dave,

Sorry for following up late.

To the question1, in my test, I find that after I paste the Chart from
Excel into Word. Then, there will be smart tag appearing at the right
bottom corner of that chart. If we click the smart tag, we find options,
Chart (linked to Excel data)
Excel Chart(entire workbook)
Paste as Picture
Keep Source Formatting
Use Destination Theme

If we choose the Excel Chart(entire workbook), then the chart is pasted as
an entier workbook embedded in the Word application.

For question2 and 3, indeed, I think we'd better post the product usage
cases in the corresponding Excel product newsgroups because these are not
related to the COM AddIn. For chart, it is,
http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.pu
blic.excel.charting&lang=en&cr=US

My opinions:
2. If you can share the Excel 2003 workbook you mentioned in 2 with me, I'd
like to investigate and consult my colleague who do Excel product support
to see if I can help. You can access me via (e-mail address removed). (I just
send you an email about case of GetRibbonButtonLocation)

3. Just as far as I know, I am afraid Excel cannot auto calculate the
Max/Min values on axis. But you may try the Chart newsgroup to see if
anyone can confirm this.

Have a nice day!


Best regards,
Ji Zhou
Microsoft Online Community Support

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm