Run time error '1004'

[Gary]

Hi ,

The following code is running fine for the first time, but when i run it for
the second time, the following error occurs.

Run time error '1004' Method 'Charts' of object '_Global' failed.

the errors occurs at Line

" Charts.Add"

'------------------------------------------------------Option Explicit

Dim xlobj As Excel.Application

Dim xlwkbk As Excel.Workbook

Dim xlSheet As Excel.Worksheet

Private Sub command1_Click()

-

On Error GoTo erb

Set xlobj = CreateObject("Excel.Application")

Set xlwkbk = xlobj.Workbooks.Add

Set xlSheet = xlwkbk.Worksheets.Add

xlSheet.Name = "A1"

xlSheet.Cells(3, 3) = "a"

xlSheet.Cells(3, 4)= "b"

xlSheet.Cells(3, 5)="c"

xlSheet.Cells(4, 3) = 10

xlSheet.Cells(4, 4)= 12

xlSheet.Cells(4, 5)= 11



xlobj.Application.Visible = True

xlobj.Parent.Windows(1).Visible = True

Charts.Add



ActiveChart.ChartType = xlColumnClustered

ActiveChart.SetSourceData

Source:=Sheets("A1").Range("C3:E4"), PlotBy:= xlRows

ActiveChart.Location Where:=xlLocationAsObject,

Name:="A1"

With ActiveChart

..HasTitle = True

..ChartTitle.Characters.Text = "PQR% OVERALL"

..Axes(xlCategory, xlPrimary).HasTitle = True

..Axes(xlCategory,

xlPrimary).AxisTitle.Characters.Text = "Companies"

..Axes(xlValue, xlPrimary).HasTitle = True

..Axes(xlValue,

xlPrimary).AxisTitle.Characters.Text = "PQR%"

End With

end sub

Can some help please.

TIA,

Gary

 

[Patrick Miolloy]

If this is Excel VBA then I'm not clear why you're using
the excel object method. Worked for me.....

Option Explicit
Dim xlwkbk As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Private Sub Create()
Set xlwkbk = Workbooks.Add
Set xlSheet = xlwkbk.Worksheets.Add
xlSheet.Name = "A1"
xlSheet.Cells(3, 3) = "a"
xlSheet.Cells(3, 4) = "b"
xlSheet.Cells(3, 5) = "c"
xlSheet.Cells(4, 3) = 10
xlSheet.Cells(4, 4) = 12
xlSheet.Cells(4, 5) = 11
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData _
Source:=Sheets("A1").Range("C3:E4"), _
PlotBy:=xlRows
ActiveChart.Location _
Where:=xlLocationAsObject, _
Name:="A1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = _
"PQR% OVERALL"
.Axes(xlCategory, _
xlPrimary).HasTitle = True
.Axes(xlCategory, _
xlPrimary).AxisTitle.Characters.Text _
= "Companies"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, _
xlPrimary).AxisTitle.Characters.Text = "PQR%"
End With
End Sub

Patrick Molloy
Microsoft Excel MVP