0

I have a Word.Chart whose underlying worksheet I am populating with data from C#. My problem is however, that the selected dataset in the worksheet contains just the default selection (eg. 5x4 cells or sg like that), and not ALL the data which I entered.

If the object were an Excel chart, I could do

Excel.Range rangeBegin = ws.Cells[1, 1];
Excel.Range rangeEnd = ws.Cells[xAxisContents.Count + 1, feeds.Count + 1];
Excel.Range chartRange = ws.get_Range(rangeBegin, rangeEnd);

wordChart.SetSourceData(chartRange);

However, the Word.Chart's SetSourceData method only accepts a string, and if I call it with an arbitrary range (just for testing), eg. wordChart.SetSourceData("A1:C3"), it fails with a ComException(E_FAIL).

I have also found this code on a Microsoft blog:

Excel.Range tblRng = dataSheet.get_Range("A1", "B5");
Excel.ListObject tbl = dataSheet.ListObjects["Table1"]; 
tbl.Resize(tblRng);

which I think is meant to resize the selected dataset to the size of the worksheet. This would also be perfect for me, however, "Table1" is reported as an unknown index (maybe because I am using a non-English version of Word.)

What should I do to select the appropriate dataset?

Peter
  • 715
  • 1
  • 12
  • 23
  • FWIW the impression I get is that calling Word's SetSourceData method only works when you are creating a new Chart object, and even then it pops up a an Excel worksheet that appears to ignore the range you just specified. But you do seem to be able to use, say "Sheet1!A1:B5" or "=Sheet1!A1:B5 etc. There is an article at http://msdn.microsoft.com/en-us/library/office/ff821389(v=office.15).aspx which is slightly ambiguous at the critical point, and the almost complete lack of examples on the web suggest to me that others have had difficulty with this. –  Dec 21 '14 at 19:54
  • Hi! Yes, in the meantime I have figured out that the =Sheet1!A1:B5 syntax works, and as you can query Worksheet.Name, it is also not locale dependent. Do you know if there is a way to convert an Excel numeric cell index (eg. 1,1) into an alphanumeric representation(eg. A1)? I had to write a function for this, but not having a built in conversion is somewhat..hmm..unelegant... – Peter Dec 22 '14 at 10:14
  • For the alpha representation conversion, if you can use Excel, there's http://stackoverflow.com/questions/6262743/convert-cells1-1-into-a1-and-vice-versa . If it's Word, I think you'll have to roll your own. –  Dec 22 '14 at 13:39

0 Answers0