none
vb.net data labels on export to excel spreadsheet RRS feed

  • Question

  • I have vb.net code that creates an excel spreadsheet, populates some cells with data and then creates a line chart from the data.  I would now like to add labels to one of the data series which reads from a range of cells.  I have found this:

    chartPage.SeriesCollection(x).ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowLabel, True, True, True, True, True, True, True, True, True)

    Which seems to do every option except allow selecting the data label values from a range that I can specify.  Is this possible?

    I can post code if it would help

    Thanks
    Garry
    Thursday, December 17, 2015 10:01 PM

All replies

  • Hi Garry,

    >>Which seems to do every option except allow selecting the data label values from a range that I can specify.  Is this possible?<<

    Yes, it is possible. We can set the labor options via format the data label like figure below:

    And here is a demo code for your reference:

        ActiveChart.SeriesCollection(1).DataLabels.Format.TextFrame2.TextRange. _
            InsertChartField msoChartFieldRange, "=Sheet1!$F$2:$F$11", 0

    In addition, the recording macros feature is very helpful for us to get familiar with Office object model.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 18, 2015 3:29 AM
    Moderator
  • Hi Fei, sorry for the delayed reply.

    I already have the vb code to achieve this in a macro by recording in Exel but I need to do this in vb.net.  msoCharFieldRange is not recognized in vb.net; how do I pass this over to Excel?

    Thanks
    Garry

    Saturday, January 2, 2016 8:39 PM
  • Hi Garry,

    The msoCharFieldRange is the type of MsoChartFieldType which under the Microsoft.Office.Core. To use this objects under this namespace, please add the Microsoft Office {15.0} object library first.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 4, 2016 3:14 AM
    Moderator
  • Hi Fei,

    I now have:

                       

    chartPage.SeriesCollection(1).ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowLabel, False, True, False, False, True, False, False, False, False)

    to add data labels which seems to work and then:

    chartPage.SeriesCollection(1).DataLabels.Format.TextFrame2.TextRange.InsertChartField(MsoChartFieldType.msoChartFieldRange, "=Sheet1!$B$1:$G$1", 0)

    to add the range for the labels but the 'Value from cells' check box is not being checked.  Have I got the syntax wrong somewhere?

    Thanks
    Garry

    Saturday, January 9, 2016 11:33 PM