graphs to excel

[alarants]

Hi,

I am trying to make graph in excel from access. It works but graph appears to the wrong worksheet. It appears to the first sheet only. Is there anybody with similar problem?


Option Compare Database
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim strPath As String


Public Function Graafikud()
strPath = "C:\Mai Tokjuments\Mody\Aruanne02.xls"
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)

SendTQ2ExcelSheet
ApXL.activeworkbook.Save
ApXL.activeworkbook.Close
ApXL.Quit
Set ApXL = Nothing
Set xlWBk = Nothing
Set xlWSh = Nothing
End Function


Public Sub SendTQ2ExcelSheet()

Set xlWSh = xlWBk.Worksheets("Tehniline")
ApXL.Visible = True
xlWSh.Activate

Range("A7:C14").Select
Charts.Add
ActiveChart.ChartType = xl3DPieExploded
ActiveChart.SetSourceData Source:=Sheets("Tehniline").Range("A7:C14"), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Tehniline!R7C2:R14C2"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Tehniline"

End Sub

 

[chergh]

here's soem code I worte a while ago:

Sub CreateGraph(ws as excel.worksheet, cht_sourcedata As Excel.Range, cht_title As String, _
    x_name As String, y_name As String, xlapp As Excel.Application)

Dim cht As Excel.Chart

Set cht = xlapp.Charts.Add

With cht
    .ChartType = xlColumnClustered
    .SetSourceData cht_sourcedata.CurrentRegion
    .PlotBy = xlColumns
    .Location xlLocationAsObject, ws.name
End With
    
With xlapp.ActiveChart
    .HasTitle = True
    .HasLegend = False

    With .ChartTitle
        .Top = 1
        .AutoScaleFont = False
        .Characters.Text = cht_title
        .Font.Size = 12
        .Font.Bold = True
        .Shadow = True
        .Border.LineStyle = xlSolid
    End With
    
    With .ChartGroups(1)
        .GapWidth = 20
        .VaryByCategories = False
    End With
    
    With .Axes(xlCategory)
        With .TickLabels
            .Font.Size = 8
            .Alignment = xlCenter
            .Orientation = xlHorizontal
        End With
        
        .HasTitle = True
        .AxisTitle.Caption = x_name
        
    End With
    
    With .Axes(xlValue)
        .HasTitle = True
        .AxisTitle.Caption = y_name
        .AxisTitle.Left = 1
    End With
    
    With .PlotArea
        .Interior.ColorIndex = xlNone
        .Top = 26
        .Height = 175
        .Left = 17
        .Width = 342
    End With
    
    .Axes(xlCategoryScale).TickLabels.Font.Size = 8

End With

End Sub

If you have trouble understanding it let me know.

 

[alarants]

On
With .PlotArea
it says "Compile error" What libraries have you included?

 

[chergh]

Just the excel object library I think.