Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Charts Help required through VB

Status
Not open for further replies.
Cranger

Cranger

Programmer
Apr 4, 2001
54
US
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")
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").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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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").Range("A1:C" & LastOne), PlotBy:=xlColumns
 
Upvote 0 Downvote
ActiveChart ALREDY has an implied object

Either

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

or

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

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
And this ?
objExcel.ActiveChart.SetSourceData Source:=objExcel.Sheets("Sheet1").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
 
Upvote 0 Downvote
I appreciate everyones help, but Now I get another error pointing to this

Method 'Sheets' of object'_global'failed

objExChart.SetSourceData Source:=Sheets("Sheet1").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
 
Upvote 0 Downvote
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").Range("A1:C" & LastOne), PlotBy:=xlColumns
.Location Where:=xlLocationAsObject, Name:="Sheet1"
End with
With ActiveChart
' remainder of code


End With

[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
708
combo
combo
SLW89
  • Locked
  • Question
VBA newbie needs help
Replies
4
Views
649
Andrzejek
Andrzejek
Kymenglish
  • Locked
  • Question
Automated data entry code in MS Excel VB - Edge code how to hit enter to remove popups
Replies
3
Views
586
strongm
strongm
remeng
  • Locked
  • Question
Run PowerShell Commands from Excel VBA
Replies
14
Views
4K
dhookom
dhookom
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
2K
remeng
remeng

Part and Inventory Search

Sponsor

Back
Top