Excel Charts Help required through VB

[Cranger]

I have a VB app that creates a custom excel chart. First time I enter the values and run it, it works fine. I try to clear everything out and run it again, but second time I run it I get an error

Run-time error '1004': Method 'Charts' of object'_Golbal' failed

This points to the following line in the code

charts.add

Here is the code...any help appreciated.
----------------------------------
Option Explicit

Dim objExcel As Object ' Excel application
Dim objBook As Object ' Excel workbook
Dim objSheet As Object ' Excel Worksheet
----------------------------------

Private Sub cmdChart_Click()


Dim index As Integer
Dim j As Integer
Dim l As Integer
Dim i As Integer
Dim iloop As Integer
Dim LastOne As Integer

SheetNumber = SheetNumber + 1
Dim Client As String * 75
Dim Campaign As String * 75
Dim BuyingDemo As String * 75

Set objExcel = CreateObject("excel.application&quot
Set objBook = objExcel.Workbooks.Add
Set objSheet = objBook.Worksheets.Item(1)
Client = "Client: " & txtClient
Campaign = "Campaign: " & txtCampaign
BuyingDemo = "Buying Demo: " & txtBuyingDemo

objExcel.Application.Visible = True

For index = 1 To 52
objExcel.Application.Cells(index, 1).Value = index

Next index
index = 1
For j = 1 To 9 Step 4
If j = 9 Then
iloop = 20
Else
iloop = 19
End If

For i = 3 To iloop
objExcel.Application.Cells(index, 2).Value = VSFG.TextMatrix(i, j)
index = index + 1
Next i
Next j

index = 1
j = 0
i = 0
iloop = 0
For j = 2 To 10 Step 4
If j = 10 Then
iloop = 20
Else
iloop = 19
End If

For i = 3 To iloop
objExcel.Application.Cells(index, 3).Value = VSFG.TextMatrix(i, j)
If VSFG.TextMatrix(i, j) <> "" And VSFG.TextMatrix(i, j) <> "0" Then
LastOne = index
End If
index = index + 1
Next i
Next j

If LastOne < 52 Then
LastOne = LastOne + 1
End If

Charts.Add

objExcel.ActiveChart.ChartType = xlBarClustered


objExcel.ActiveChart.SetSourceData Source:=Sheets("Sheet1&quot.Range("A1:C" & LastOne), PlotBy:=xlColumns
objExcel.ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"


.HasTitle = True
.ChartTitle.Text = "MARC USA " & Chr(10) & "Advertising Awareness Model"
.HasLegend = True
.Legend.Position = xlLegendPositionBottom
.Axes(xlValue).MinimumScaleIsAuto = 1
.Axes(xlValue).MaximumScaleIsAuto = 52
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MaximumScale = 1000
.Axes(xlCategory).TickLabelSpacing = 1
.Axes(xlCategory).TickMarkSpacing = 1


End With
ActiveChart.PlotArea.Select
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

ActiveChart.Axes(xlCategory).Select

With ActiveChart
.PageSetup.PaperSize = xlPaperA4

.HasTitle = True
.ChartTitle.Characters.Text = "MARC USA " & Chr(10) & "Advertising Awareness Model"
.Axes(xlCategory, xlPrimary).HasTitle = True
'.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "1st Qtr Budget | 2nd Qtr Budget | 3rd Qtr Budget | 4th Qtr Budget"
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Weeks"
.Axes(xlCategory, xlSecondary).HasTitle = True
.Axes(xlCategory, xlSecondary).AxisTitle.Characters.Text = Client & Chr(10) & Campaign & Chr(10) & BuyingDemo
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "TRPs"
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Awareness"
.SeriesCollection(2).Name = "TRPs"
.SeriesCollection(3).Name = "Awareness"
End With
'objOLE.ActiveChart.Legend.Select
'objOLE.ActiveChart.SeriesCollection(1).Select
ActiveChart.Legend.Select
ActiveChart.Legend.LegendEntries(1).Select
ActiveChart.Legend.LegendEntries(1).LegendKey.Select


Selection.Delete

SheetNumber = SheetNumber + 1
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart" & SheetNumber
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 49
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 49
.MarkerForegroundColorIndex = 49
.MarkerStyle = xlTriangle
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With

'

ActiveChart.PlotArea.Select
Selection.Width = 692
ActiveChart.Legend.Select
Selection.Left = 608
Selection.Top = 115

-------------------------------

Private Sub cmdClear_Click()

objExcel.Quit

Set objExcel = Nothing
Set objBook = Nothing
Set objSheet = Nothing

End Sub

 

[PHV]

Have you tried this ?
objExcel.Charts.Add

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Hi.

Charts needs the workbook object
[tt]
objBook.Charts.Add
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Cranger]

hmmm tried both of the above....both now cause this error on the following line

Method 'Sheets' of object'_global'failed

objExcel.ActiveChart.SetSourceData Source:=Sheets("Sheet1&quot.Range("A1:C" & LastOne), PlotBy:=xlColumns

 

[SkipVought]

ActiveChart ALREDY has an implied object

Either

objBook.Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Sheet1&quot.Range("A1:C" & LastOne), PlotBy:=xlColumns

or

Set objExChart = objBook.Charts.Add
objExChart.SetSourceData Source:=Sheets("Sheet1&quot.Range("A1:C" & LastOne), PlotBy:=xlColumns

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

And this ?
objExcel.ActiveChart.SetSourceData Source:=objExcel.Sheets("Sheet1&quot.Range("A1:C" & LastOne), PlotBy:=xlColumns

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

PH

Yer right on Source:=objExcel...! But I think it still bombs on objExcel.ActiveChart

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Cranger]

I appreciate everyones help, but Now I get another error pointing to this

Method 'Sheets' of object'_global'failed

objExChart.SetSourceData Source:=Sheets("Sheet1&quot.Range("A1:C" & LastOne), PlotBy:=xlColumns

this is skipvought's second option

Again, this works the first time I run the code....
Fails if I rerun the chart

 

[SkipVought]

Here's something to keep in mind when Setting a Chart Object.

Initially when you use the Add method, a Chart Object is created.

When you CHANGE the location to a Worksheet, the object CHANGES to a ChartObject Object. So if you set a Chart Object and then try to reference a ChartObject Object it will bomb.

So I guess i'd do it like this...
[tt]
objBook.Charts.Add

With ActiveChart
.ChartType = xlBarClustered


.SetSourceData Source:=objBook.Sheets("Sheet1&quot.Range("A1:C" & LastOne), PlotBy:=xlColumns
.Location Where:=xlLocationAsObject, Name:="Sheet1"
End with
With ActiveChart
' remainder of code


End With

[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Cranger]

SkipVought ,

again I appreciate all you have done....
I still have the one time only problem
no bombs on

With ActiveChart

Method ActiveChart of Object Global Failed.

I think I am just going to tell the user if he wants to run more than 1 chart, exit the program and log back in

 

[PHV]

And what happens with this?
With objExcel.ActiveChart
and so on taking care of objExcel.Sheets and objExcel.Selection

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884