Error message received when inserting chart in Excel using vb6

[bebe429]

I have a vb application that populates two columns of data, actual production and budgeted production for each day of the current month, in a spreadsheet then creates a line-column chart from that data. I originally recorded a macro in Excel to insert the chart, etc. It runs fine in Excel, but when I run the code copied from the macro and put into the application, I get the following error "Run-time error '1004': Method 'Axes' of object '_Chart' failed.

Any help would be greatly appreciated.

Below is the code from the macro to insert the chart, with my changes for the range of data to be used. The lines giving the error message are hightlighted in red.

myRow is an integer
intDay is the integer portion of yesterday's date

myrow = intDay + 1
MyRange = "A2:B" & CStr(myrow)
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
ActiveChart.SetSourceData Source:=Sheets("ARP").Range(myrange), PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "ARP Hardwood Production"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "ADMT"
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = False
End With
ActiveChart.HasLegend = False

 

[RoyVidar]

When automating, every object, property and method of the automated application, needs to be "anchored" to it's parent object (the Excel application object), else you'll be gettings those automation errors, having the automated application staying in memory afterwards...

You probably have an Excel object, and a workbook object instantiated, perhaps like this

[tt]set xl = createobject("excel.application")
set wr = xl.workbooks.open(<path and name of workbook>)

' then, you'd anchor the objects in that,
' and perhaps something like the below

set ch = wr.charts.add
with ch
.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
.SetSourceData Source:=wr.Sheets("ARP").Range(myrange), PlotBy:= xlColumns
...[/tt]

Note also that if you want to use the Excel constants (xlColumns, xlBuiltIn...), you will need to have a reference to Excel. I like late binding and no references, and would therefore replace each constant by their literal value

[tt]..., PlotBy:= 2 ' xlColumns[/tt]

Here's some more reading

http://support.microsoft.com/default.aspx?kbid=178510,

http://support.microsoft.com/?kbid=244264

Roy-Vidar