Selecting a chart range based upon a selection

[Felix18807]

Im trying to create a chart based on the selection. See code below.

Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
objExcel.Visible = False
objExcel.Workbooks.Open (Me.txtDirectory)

    objExcel.Cells.Find(What:="date/time of last account table update", After:= _
        objExcel.ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    objExcel.ActiveCell.Offset(0, 1).select
    objExcel.selection.NumberFormat = "dd/mm/yyyy hh:mm:ss"
    
    objExcel.ActiveCell.Offset(-1, 0).Range("A1").select
    objExcel.Range(selection, Cells(1)).select


    objExcel.Range("A2:B7").select
    objExcel.ActiveSheet.Shapes.AddChart.select
    objExcel.ActiveChart.ChartType = xl3DPie
    objExcel.ActiveChart.SetSourceData Source:=objExcel.selection
    objExcel.ActiveChart.ClearToMatchStyle
    objExcel.ActiveChart.ChartStyle = 43

The following line is causing a data mismatch error.

    objExcel.ActiveChart.SetSourceData Source:=objExcel.selection

 

[PHV]

Replace this:
objExcel.Range(selection, Cells(1)).select
with this:
objExcel.Range(objExcel.Selection, objExcel.Cells(1)).Select

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

You could also, with less code, FILTER the chart source data. The chart default is to only display VISIBLE cells. I use the technique often.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Felix18807]

Thanks for the pointers. I'm sure I would have had to find these the hard way later on!

I'm still getting a run time error 13 "Type mismatch" on this line

    objExcel.ActiveChart.SetSourceData Source:=selection

Is it due to the fact I am controlling excel from a MS Access application?

 

[Felix18807]

Hmmm. I turned the visibility on and the chart has been created successfully. I'll just make it ignore this error in the error handler but tis tres odd...

 

[PHV]

objExcel.ActiveChart.SetSourceData Source:=[!]objExcel.[/!]selection

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886