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

[Helpmesoso]

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








DimobjExcel AsExcel.Application




DimobjBook AsExcel._Workbook




DimobjSheets AsExcel.Sheets




DimxlWorkbookOld AsWorksheet

DimxlWorkbookNew AsWorksheet


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

DimmySheet AsSheets

DimxlCharts AsChartObjects

Dimmychart AsChartObject

DimchartPage AsChart

DimstrDate AsString


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

Continue reading...