Change the data source on a chart in excel us vb

[wavp]

I have written some vb code in access which generates an excel report, with a pre-draw graph. And what I want to do is to adjust this graph to fit the data which has been dumped in the excel sheet. Below is some of the code, I just can get the data source changed on the chart sheet that’s all?

Any ideas? Thanks in advance.

Dim objXL As Object, objXLWrkBk As Object, objXLWrkSht As Object
Dim objXLWrkSht_1 As Object, objXLChart As Object
Dim lngRow As Long, lngCol As Long

'Excel Settings
Set objXL = CreateObject("Excel.Application")
Set objXLWrkBk = objXL.Workbooks.Open(tmplReport) 'XLS file which will be used
Set objXLWrkSht_1 = objXLWrkBk.Worksheets("DATA")
[B][COLOR=Red]'Set objXLChart = objXLWrkBk.Worksheets("CHART")
Set objXLChart = objXLWrkBk.ChartObjects("CHART")[/COLOR][/B]
objXL.Visible = True

'----------------------------------------------------------------
'Format Chart Data Source
'----------------------------------------------------------------
[COLOR=Red][B]objXLChart.SeriesCollection.Add Source:=objXLWrkSht_1.Range(objXLWrkSht_1.Cells(1, 2), objXLWrkSht_1.Cells(intTotal, intDTotal))[/B][/COLOR]

'----------------------------------------------------------------
objXLWrkBk.SaveAs strSaveAs
    
Set objXLWrkBk = Nothing
Set objXLWrkSht = Nothing
'Set objXLWrkSht_1 = Nothing
Set objXLChart = Nothing
   
    'objXL.Quit
Set objXL = Nothing

 

[KenHigg]

Can't you do the graph in Access?

 

[wavp]

Can't you do the graph in Access?

Needs to be in excel, so the graph and data can be sent around via email.

 

[KenHigg]

Hum.... If you can't get this to work, you may be able to use the snapshot viewer.

Maybe we should ask Pat to bring up this 'non-portable' aspect of reports to MS...

 

[Bat17]

will something like this do it? don't have time to test

    With objXLChart.Axes(xlValue)
        .MinimumScale = 2
        .MaximumScale = 3
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With

Peter