Can anybody see what I have left out? The following code extracts succeed in successfully placing a ChartObject on the right Worksheet, which appears to have picked up the data series and, if the First Failure is commented out, the correct Chart type. However, nothing I have managed to do allows me to set the Chart Title or to make the beastly thing actually appear!
xlApp is the Excel application
xlWB is the Excel Workbook
xlWS has been declared as Object
xlChartSheet has been declared as object and set as the active sheet
oRange has been declared as Object
sCalcSheetName, sRowAddress, sLeftRowCell, sRightRowCell, sRowName have been declared as a string
Relevant lines of code are as follows:
<br />
xlChartSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
(Commented out) Code below reads the data series of the Chart, one series at a time, finds the Worksheet on which the row appears, reads its address, gets the left and right cells of the range, creates a Range object which is added to the Charts SeriesCollection, as follows
While sContent.Length is greater than 0<br />
<br />
...<br />
<br />
xlApp.Goto(sRowName)<br />
xlWS = xlWB.ActiveSheet<br />
sCalcSheetName = xlWS.Name<br />
xlRange = xlWS.Range(sRowName)<br />
sRowAddress = xlRange.Address<br />
sLeftRowCell = xxx (string to the left of the colon)<br />
sRightRowCell = yyy (string to the right of the colon)<br />
<br />
oRange = xlWS.Range(sLeftRowCell, sRightRowCell)<br />
<br />
xlChartSheet.ChartObjects(1).Chart.SeriesCollection.Add(oRange)<br />
<br />
...<br />
<br />
End While
(Question) All of which seems to be working, but it never reaches either of the two snippets below; Can anyone spot what have I left out?
(First failure) With the following, throws up Unable to set the HasAxis property of the Chart Class
With xlChartSheet.ChartObjects(1).Chart<br />
.HasAxis(1, 1) = False<br />
.HasAxis(2, 1) = False<br />
End With
(Second failure) Without the above, throws up Unable to set the HasTitle property of the Chart Class
With xlChartSheet.ChartObjects(1).Chart<br />
.ChartType = 4<br />
.HasLegend = False<br />
.HasTitle = True<br />
End With
(NB) xlConstants in the two snippets above have been replaced with their numeric values
...