Help: 1004 Method 'Charts' of Object '_Global' failed

[anu]

Hi all,
i have a code which sends data from a datagrid to MS Excel and then
plots the graph. I am copy pasting my code for it. The data gets
transferred from to Excel but once it is done i get the error message:
1004 Method 'Charts' of Object '_Global' failed. I dont get the plot.
However, at times the same code runs and a plot comes up. Any
Help ????? :-(

My code:
Dim iRowIndex As Integer
Dim iColIndex As Integer
Dim iRecordCount As Integer
Dim iFieldCount As Integer
Dim avRows As Variant
Dim excelVersion As Integer

Screen.MousePointer = vbHourglass

On Error GoTo expError

Adodc1.Recordset.MoveFirst

'--read all records into array
avRows = Adodc1.Recordset.GetRows()

'--Determine how many fields and records
iRecordCount = UBound(avRows, 2) + 1
iFieldCount = UBound(avRows, 1) + 1

'--Create reference variable for the spreadsheet
Set objExcel = GetObject("", "Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add

'--Ensures Excel remains visible if we switch to the active sheet
Set objTemp = objExcel

excelVersion = Val(objExcel.Application.Version)
If (excelVersion >= 8) Then
Set objExcel = objExcel.ActiveSheet
End If



'--add data
With objExcel
For iRowIndex = 2 To iRecordCount + 1
For iColIndex = 1 To iFieldCount
.Cells(iRowIndex - 1, iColIndex).Value = avRows(iColIndex - 1,
iRowIndex - 2)
Next
Next
End With

objExcel.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
objExcel.Cells(1, 1).CurrentRegion.EntireRow.AutoFit
'Screen.MousePointer = vbDefault


'To plot the XY scatter after transferring data to Excel
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("H3")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
'ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!C2"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!C3"
ActiveChart.SeriesCollection(1).Name = "=""Detector 11"""
ActiveChart.SeriesCollection(2).XValues = "=Sheet1!C4"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!C5"
ActiveChart.SeriesCollection(2).Name = "=""Raw UV Absorbance
Data"""
ActiveChart.SeriesCollection(3).XValues = "=Sheet1!C6"
ActiveChart.SeriesCollection(3).Values = "=Sheet1!C7"
ActiveChart.SeriesCollection(3).Name = _
"=""Differential Refractive Index data"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Light Scattering Data from DAWN
HELEOS of Wyatt Tech"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Volume (mL)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"Intensity"

End With
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 46
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 46
.MarkerForegroundColorIndex = 46
.MarkerStyle = xlCircle
.Smooth = True
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Legend.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Bookman Old Style"
.FontStyle = "Bold"
.Size = 9
End With
Selection.Left = 309
Selection.Top = 224
ActiveChart.SeriesCollection(3).Select
ActiveChart.PlotArea.Select
Selection.Left = 27
Selection.Top = 30
ActiveChart.Legend.Select
Selection.Height = 44
Selection.Left = 249
Selection.Width = 209
Selection.Height = 39
Selection.Top = 264
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.PlotArea.Select
Selection.Width = 309
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.08, msoFalse, _
msoScaleFromBottomRight
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.09, msoFalse,
msoScaleFromTopLeft
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone


Create_GIF
Picture1.Picture = LoadPicture("c:\Mychart.gif")
Picture1.Visible = True
Screen.MousePointer = vbDefault

Exit Sub


Thanks,
Anu

 

[paul.robinson]

Hi
What line generates the error?
regards
Paul

 

[anu]

Hi
What line generates the error?
regards
Paul

Hi Paul,
I am not able to add the chart. So it should be Charts.Add.
Any help??
Thanks
Anusha

 

[Peter T]

Referring to the code you posted in your OP,

change
objExcel.Workbooks.Add
to
set objWB = objExcel.Workbooks.Add

change
Set objExcel = objExcel.ActiveSheet
to
Set objWS= objExcel.ActiveSheet
(not sure you need this reference at all)

change
Charts.Add
to
Set objChart = objWB.Charts.Add

Change each 'ActiveChart' to 'objChart'

Amend the code to remove all those Select and Selection statements. But if
you really can't avoid, qualify with objChart.xxx.Select or
objExcel.Selection.

As you are working with automation, and I assume not with Excel-VBA, every
Excel object will need to explicitly relate to your reference to the Excel
application, objExcel, or interim references such as objWB, objWS or
objChart.

Regards,
Peter T