Hi:

I am trying to create Charts in Excel from MS Access data. its working fine. I am able to create charts and then move around to position them as i want.

The only thing which is not working is
Activechart.location

Function generateGraphs(fileName As String, startCol As Long, ByRef xlApp As Excel.Application, _
ByRef xlShtSrc1 As Excel.Worksheet, ByRef xlShtSrc2 As Excel.Worksheet, _
ByRef xlShtSrc3 As Excel.Worksheet, ByRef xlShtDest As Excel.Worksheet)
Dim colsToDraw As Long
Dim rowNumber As Integer

Dim endCol As Long
Dim startColName As String
Dim endColName As String
Dim xlChart As Chart
Dim chartCount As Integer

Dim pName As String

endCol = xlShtSrc1.UsedRange.Columns.count

startColName = strColId(startCol)
endColName = strColId(endCol)
rowNumber = 4

Dim i As Integer
For i = 4 To xlShtSrc1.UsedRange.Rows.count
rowNumber = i
If (Not isEmpty(xlShtSrc1.Cells(rowNumber, 1).Value)) Then
pName = xlShtSrc1.Cells(rowNumber, 3) & "-" & xlShtSrc1.Cells(rowNumber, 2)
If (Not isEmpty(xlShtSrc1.Cells(rowNumber, 4))) Then
pName = pName & "-" & xlShtSrc1.Cells(rowNumber, 4)
End If


xlApp.Charts.Add

xlApp.ActiveChart.ChartType = xlLineMarkers

'Set the dummy data range for the chart
'xlApp.ActiveChart.SetSourceData xlApp.Sheets(xlShtSrc1.name).Range(startColName & rowNumber & ":" & endColName & rowNumber), PlotBy:=xlColumns
xlApp.ActiveChart.Location where:=xlLocationAsObject, name:=xlShtDest.name

'chartCount = xlShtDest.ChartObjects.count

chartCount = xlShtSrc1.ChartObjects.count


xlApp.ActiveChart.HasTitle = True
xlApp.ActiveChart.ChartTitle.Characters.Caption = pName
xlApp.ActiveChart.ApplyDataLabels (xlDataLabelsShowValue)
xlApp.ActiveChart.Legend.Position = xlLegendPositionTop


xlApp.ActiveChart.SeriesCollection.NewSeries
xlApp.ActiveChart.SeriesCollection.NewSeries
xlApp.ActiveChart.SeriesCollection(1).Formula = "=SERIES(""" & xlShtSrc1.name & """,'" & xlShtSrc1.name & "'!" & startColName & "1:" & endColName & "1,'" & xlShtSrc1.name & "'!" & startColName & rowNumber & ":" & endColName & rowNumber & ",1)"
xlApp.ActiveChart.SeriesCollection(2).Formula = "=SERIES(""" & xlShtSrc2.name & """,'" & xlShtSrc2.name & "'!" & startColName & "1:" & endColName & "1,'" & xlShtSrc2.name & "'!" & startColName & rowNumber & ":" & endColName & rowNumber & ",1)"
xlApp.ActiveChart.SeriesCollection(3).Formula = "=SERIES(""" & xlShtSrc3.name & """,'" & xlShtSrc3.name & "'!" & startColName & "1:" & endColName & "1,'" & xlShtSrc3.name & "'!" & startColName & rowNumber & ":" & endColName & rowNumber & ",1)"

xlApp.ActiveChart.Parent.Width = 300
xlApp.ActiveChart.Parent.Height = 200

xlApp.ActiveChart.Parent.Left = 0 + ((chartCount - 1) Mod 2) * (xlApp.ActiveChart.Parent.Width + 10)
xlApp.ActiveChart.Parent.Top = 0 + Int((chartCount - 1) / 2) * (xlApp.ActiveChart.Parent.Height + 10)

End If
Next
End Function

Again the only line which is not working is Activechart.location. this is what bothering me and i dont understand why.

I have Excel 9.0 library. This code was working fine with 10.0. But i cannot update it and it HAS to work with 9.0

Please help