OLE Automation and Chart Location

[Jim Redel]

I'm inserting a chart into a spreadsheet (from Matlab or
VB) but no matter the arguments, it always embeds the
Chart into the first page in the workbook

Here's some VB code

' the chart
Set Charts = Workbook.Charts
Set newChart = Charts.Add
newChart.Location 2, "Sheet3"

It ends up in "Sheet1"

 

[Jon Peltier]

Jim -

First of all, you shouldn't use Charts as a variable name (or Workbook
either), since it's also a reserved name in Excel, and already refers to
the collection of chart sheets in a workbook.

Try this:

Dim oChtOb as ChartObjects
dim oChart as Chart
Dim oWkBk as Workbook
Dim oWkSht as Worksheet
' define oWkBk here first
Set oWkSht = oWkBk.Worksheets("Sheet3")
Set oChtOb = oWkSht.ChartObjects.Add(ptsLeft, ptsTop, ptsWide, ptsHigh)
' ptsXxx are dimensions within sheet in points
Set oChart = oChtOb.Chart
With oChart
' set all your parameters in here
.SetSourceData yada yada
.Axes(2,1).MaximumScale = Whatever
' etc.
End With

More VBA-Chart hints:

http://www.geocities.com/jonpeltier/Excel/Charts/chartvba.html

- Jon