Anchoring a chart to a certain area

[enak]

I have an application that I use WinForms.net and VB.NET as the
frontend, MS Access 2000 as the backend. I use Excel to create reports.
On the biggest report I I have tables and charts.

I create the charts in the spreadsheet as an object. However, when the
chart is created it is not put where I would like to be. I have tried
to select a cell prior to creating the chart but this only puts it in
the general area.

Here is the code that I use to create the chart:

xlSheet.Cells(((nChartOffset) + 9) + i, 1).Select()
'this puts the chart in the general area
Dim oChart As Excel.Chart
Dim xlsAxisCategory, xlsAxisValue As Excel.Axes

oChart = xlSheet.Application.Charts.Add
Dim xlsSerie As Excel.SeriesCollection =
oChart.SeriesCollection
oChart.ChartType = Excel.XlChartType.xlLine
oChart.SetSourceData(Source:=xlSheet.Range("M" &
(nChartOffset) + 13 & ":N" & ((nChartOffset) + 13) + i),
Plotby:=Excel.Constants.xlColumn)

oChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Quarterly
Portfolio Review")
'oChart.ChartArea.Height = 150
'oChart.ChartArea.Width = 300
'oChart.HasTitle = True
'oChart.ChartTitle.Text = "ASSET GROWTH SINCE
INCEPTION"
'oChart.ChartTitle.Font.FontStyle = "Bold"
'oChart.ChartTitle.Font.Size = 9.75
'oChart.SeriesCollection = xlSheet.Range("M" & (nrow *
26) + 13 & ":M" & ((nrow * 26) + 13) + i))
'xlsSerie.Item(1).Name = "Market Value"
'xlsSerie.Item(2).Name = "Net Cash Flow"

How can I accomplish this?

BTW, all of the code that is commented out causes an error.

I get an error on the first commented line above that states:

"Exception from HRESULT: 0x800401A8."

Each of the commented lines cause the same error.

I don't know what this error means. I can not find anything that helps
on the internet.


Thanks,
enak

 

[enak]

I can't believe the no one knows how to fix this. I really need som
help. Can anyone please help

 

[Jon Peltier]

What's your question? There's not much detail in the subject of this post,
and none in the body.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[enak]

I don't know how much more detail I can provide than what I have already
provided. Please read my first post. I need to be able to put the charts
at certain locations on the spreadsheet.

I have tried to create a macro and then use that code to put in my code
but I get errors when I run it.

How can I put the chart at a certain location (anchore it to a cell) in
the spreadsheet. I am trying to do this through vb.net code in a windows
application.

 

[Jon Peltier]

When you create the chart, don't use Workbooks("Book1.xls").Charts.Add, use

Workbooks("Book1.xls").Worksheets("Sheet1").ChartObjects.Add _
(Left, Top, Width, Height)

where Left etc., allow you to specify a precise location and size for the
chart object, in points. To have a chart cover the range "B2:F15", use
something like this:

With Workbooks("Book1.xls").Worksheets("Sheet1").Range("B2:F15")
Workbooks("Book1.xls").Worksheets("Sheet1").ChartObjects.Add _
(.Left, .Top, .Width, .Height)
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[enak]

Thanks for the reply. That fixed that problem. I need to be able to
format the chart, i.e. set the x axis, set the legend, put in data
points along the lines of the chart, etc.

Here is what I have so far:

'Anchore the chart to a particular area.
With xlSheet.Range("A" & nChartOffset + 5 & ":E" &
nChartOffset + 15)
xlSheet.ChartObjects.Add(.Left, .Top, .Width,
..Height)
nChartObj += 1
End With

oChartObj = xlSheet.ChartObjects(nChartObj)
oChart = oChartObj.Chart
oChart.ChartType = Excel.XlChartType.xlLine
oChart.SetSourceData(Source:=xlSheet.Range("M" &
nChartOffset + 13 & ":N" & ((nChartOffset) + 13) + i),
Plotby:=Excel.Constants.xlColumn)
'Dim xlsSerie As Excel.SeriesCollection =
oChart.SeriesCollection(1)

oChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Quarterly
Portfolio Review")
oChartObj.Width = 250
oChartObj.Height = 175

Everything that I have tried has given me an error. I have looked
through the chartobject but do not see what I need. How can I do this?

Thanks,
enak