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 Whe=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