I need help for excel chart vba codigo

[Wellington]

I need help to do a macro to get to generate a graph of the personalized type, with lines - columns in 2 axes.
below it follows part of the macro that I arranged, more it doesn't work

I believe that the problem is in that line
'ObjExcel.activechart.ChartType = 97
ObjExcel.ActiveChart.ApplyCustomType 22, "SentinalColorLineChart"




'Create Object
Set ObjExcel = CreateObject("Excel.Sheet")
set x = createobject("Excel.sheet")


'show or dont show excel to user, TRUE or FALSE
ObjExcel.Application.Visible = True

' populate the cells
ObjExcel.ActiveSheet.Range("B2:k2").Value = Array("Week1", "Week2", "Week3", "Week4", "Week5", "Week6", "Week7", "Week8", "Week9", "Week10")
ObjExcel.ActiveSheet.Range("B3:k3").Value = Array("67", "87", "5", "9", "7", "45", "45", "54", "54", "10")
ObjExcel.ActiveSheet.Range("B4:k4").Value = Array("10", "10", "8", "27", "33", "37", "50", "54", "10", "10")
ObjExcel.ActiveSheet.Range("B5:k5").Value = Array("23", "3", "86", "64", "60", "18", "5", "1", "36", "80")
ObjExcel.ActiveSheet.Cells(3,1).Value="Internet Explorer"
ObjExcel.ActiveSheet.Cells(4,1).Value="Netscape"
ObjExcel.ActiveSheet.Cells(5,1).Value="Other"

' Select the contents that need to be in the chart
ObjExcel.ActiveSheet.Range("b2:k5").Select

' Add the chart
ObjExcel.Charts.Add
' Format the chart, set type of chart, shape of the bars, show title, get the data for the chart, show datatable, show legend

'ObjExcel.activechart.ChartType = 97
ObjExcel.ActiveChart.ApplyCustomType 22, "SentinalColorLineChart"

'xlLineMarkers
ObjExcel.activechart.BarShape = 3
ObjExcel.activechart.HasTitle = True
ObjExcel.activechart.ChartTitle.Text = "Teste"
'ObjExcel.activechart.SetSourceData
'ObjExcel.Sheets("Sheet1").Range("A1:k5"),1
ObjExcel.activechart.Location 1
ObjExcel.activechart.HasDataTable = True
'ObjExcel.activechart.DataTable.ShowLegendKey = True

 

[Andrew Poulsom]

The ActiveChart property applies to Application, Window or WorkBook objects. Your ObjExcel object is a Worksheet object, so the code fails.

Set a reference to the workbook, like this:

Set ObjWB - ObjExcel.Parent

Then use ActiveChart with ObjWB.

 

[Wellington]

When I type is line the following mistake returns

Set ObjWb = CreateObject("Excel.Workbook")
ActiveX component can´t create object



I think the mistake is in the line below:
ObjExcel.ActiveChart.ApplyCustomType 22, "Line - Column on 2 axes"

Because when I put any other type he generates the graph usually
Example: this work

ObjExcel.ActiveChart.ApplyCustomType 97

 

[Andrew Poulsom]

You are using constant xlUserDefined (22). I think it should be xlBuiltIn (21).

 

[Wellington]

Any one of the three of the mistake
21,22,23

Will it be that I am wandering in the name of the graph, what cannot exist?
"Line - Column on 2 axes"

 

[Andrew Poulsom]

When I record it it is:

"Line - Column on 2 Axes"

Note the capital A in Axes. Maybe it makes a difference.

 

[Wellington]

I thank for the help, I managed to do to work, it was really the name of the graph that it was wrong. Thank you