none
Access and Excel Chart Title using VB in Visual Studio throught an excel object RRS feed

  • Question

  • I have seen many examples, but none of them seem to work with the new Visual Basic in 2009 Visual Studio

    I have open an excel spreadsheet in VB by using the Office Interop objects

    Imports Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop

    I do have access to the spreadsheet and can change cells in it, but this spreadsheet has a chart called Chart 1 in it.

    Here is some of the code I have

      

    Dim objExcel As Excel.Application   
    Dim objBook As Excel._Workbook   
    Dim objSheets As Excel.Sheets   
    Dim xlWorkbookOld As Worksheet
    Dim xlWorkbookNew As Worksheet
        objBook = objExcel.Workbooks.Open("C:\temp\DBPOP\DBPOP Report Tracking.xlsx")
       xlWorkbookOld = objBook.Worksheets(strLastDBPOPTab)
       xlWorkbookOld.Select()
       xlWorkbookOld.Copy(Before:=xlWorkbookOld)
       strOldSheetName = strLastDBPOPTab & " (2)"
       xlWorkbookNew = objBook.Worksheets(strOldSheetName)
       xlWorkbookNew.Select()
       xlWorkbookNew.Name = "DBPOP"& LTrim(RTrim(tbDate.Text))
      xlWorkbookNew.Range("H5").Value  = arrDSInfo(1, 2)  ' CFMEM
      xlWorkbookNew.Range("I5").Value = arrDSInfo(1, 3)
    
    

       All this works, now I want to modify the title in the chart

    Here are my DIMs

    Dim mySheet As Sheets
    Dim xlCharts As ChartObjects
    Dim mychart As ChartObject
    Dim chartPage As Chart
    Dim strDate As String

    This statement works since I can see the chart activate when watching Excel

    xlWorkbookNew.ChartObjects("Chart 1").Activate

    But I try to set a chart object by doing this

    mychart = xlWorkbookNew.ChartObjects("Chart 1").Activate

    I can't seem to figure out how to set my object or the right object to the chart. Here is some more code, but none of it works

     
     mychart = xlCharts.Select
    chartPage = mychart.Chart
    chartPage.ChartTitle.Text = " Database Population (Offline Report) - <50% Available for the Week Ending "&
     Chr(13) & "01/12/20"

    I can't find a good example of how to set the chart object so I can modify the file. I even tried

    xlWorkbookNew.ChartObjects("Chart 1").ChartObjects.ChartTitle.Text=
     "title"

    and that failed.

    Hopefully someone can help me with these chart objects.


        


    Stephen Ostrye


    • Edited by Helpmesoso Friday, January 17, 2020 2:22 PM
    Thursday, January 16, 2020 9:07 PM

Answers

  • I figured it out. This is my code

     xlWorkbookNew.ChartObjects("Chart 1").Activate
      For Each mychart In xlWorkbookNew.ChartObjects
          ChartName = mychart.Chart.Name
          strChartName = "DBPOP" & LTrim(RTrim(tbDate.Text))   & " Chart 1"
          If ChartName = strChartName Then
                    chartPage = mychart.Chart
                    chartPage.ChartTitle.Text = "Database Population (Offline Report) - <50% Available for the Week Ending " & Chr(13) & "01/12/20"
          End If
    Next
    
    There is only one chart, but I do verify the name to make sure I did get the right chart.


           


    Stephen Ostrye

    • Marked as answer by Helpmesoso Tuesday, January 21, 2020 1:18 PM
    Tuesday, January 21, 2020 1:18 PM

All replies

  • Hello Stephen,

    Please edit your post and place code into code blocks using the code block button.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, January 16, 2020 9:51 PM
    Moderator
  • Thank you. I didn't realize this.

    Stephen Ostrye

    Friday, January 17, 2020 2:23 PM
  • Although the following is mean to run in Excel it does show setting the title.

    https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.tools.excel.chart.charttitle?view=vsto-2017

    In some case you may need to set the chart property HasTitle = true.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 17, 2020 2:31 PM
    Moderator
  • Unfortunate that code doesn't work with VB.Net, I tried all kind as examples on the internet, but the newest VB has the DIM as Sheet, DIM as ChartObjects, Dim as ChartObject and Dim as Chart, but I can never seem to assign them to the chart. They always have a null value when I try to assign them, so I am missing that and the SET command is no longer in VB. Thanks for trying though.

    Stephen Ostrye

    Friday, January 17, 2020 2:38 PM
  • Unfortunate that code doesn't work with VB.Net, I tried all kind as examples on the internet, but the newest VB has the DIM as Sheet, DIM as ChartObjects, Dim as ChartObject and Dim as Chart, but I can never seem to assign them to the chart. They always have a null value when I try to assign them, so I am missing that and the SET command is no longer in VB. Thanks for trying though.

    Stephen Ostrye


    I dont know the excel side but I will try to help.

    One problem I see in your code is you need to use the exact chart name (or the index)


    You have

        Dim chartPage As Chart

    then you:

        mychart = xlWorkbookNew.ChartObjects("Chart 1").Activate


    but the name of the chart is chartPage so it should be:

        mychart = xlWorkbookNew.ChartObjects("chartPage ").Activate


    or by index:

        mychart = xlWorkbookNew.ChartObjects(0).Activate


    Plus, in Karen's link example it shows:

        Dim Chart1 As Microsoft.Office.Tools.Excel.Chart = _
            Me.Controls.AddChart(Me.Range("D2", "H12"), "Chart1")


    but you use this?

       Dim chartPage As Chart

    Try to make a simple chart example first. Try Karen's link example. Make sure you have new code not 10 year old code.  :)


    • Marked as answer by Helpmesoso Tuesday, January 21, 2020 1:12 PM
    • Unmarked as answer by Helpmesoso Tuesday, January 21, 2020 1:12 PM
    Saturday, January 18, 2020 10:17 AM
  • I figured it out. This is my code

     xlWorkbookNew.ChartObjects("Chart 1").Activate
      For Each mychart In xlWorkbookNew.ChartObjects
          ChartName = mychart.Chart.Name
          strChartName = "DBPOP" & LTrim(RTrim(tbDate.Text))   & " Chart 1"
          If ChartName = strChartName Then
                    chartPage = mychart.Chart
                    chartPage.ChartTitle.Text = "Database Population (Offline Report) - <50% Available for the Week Ending " & Chr(13) & "01/12/20"
          End If
    Next
    
    There is only one chart, but I do verify the name to make sure I did get the right chart.


           


    Stephen Ostrye

    • Marked as answer by Helpmesoso Tuesday, January 21, 2020 1:18 PM
    Tuesday, January 21, 2020 1:18 PM