Controle Grphique Excel Via Access

Question

Bonjour,
la question est simple comment je fais pour changer la position d'un label dans un graphe Excel


mon code est 
xls.Chart(1).SeriesCollection(3).Points(1).DataLabel.Left = 45
et il reconnait pas la péthode Left alors que c'est excel qui me l'a fourni

darkspoilt · Answer

Bonjour je sais que C'est pas propre mais c'est encore pire si je nomme mon graph c'est a dire
quand je fais 
xls.ActiveChart.name = "Graph1"
xls.Chart("Graph1).SetSourceData Source:=Sheets("R_analyse_croisée").Range("A1"), PlotBy:=xlRows


Donc j'ai fait ce code avec des Active Truc qui est je conçoit plus crade mais qui fonctionne au moins une fois sur deux, mais j'aimerais savoir comment je pourais faire pour qu'il fonctionne tout le temps

Merci d'avance




Option Compare Database
Option Explicit


Public Sub Macro1(xls As Excel.Application)
'
' Macro1 Macro
' Macro enregistrée le 14/06/2007 par
'


'
    With xls
    .Application.CutCopyMode = False
    .Charts.Add
    .ActiveChart.ChartType = xlColumnStacked
    .ActiveChart.SetSourceData Source:=Sheets("R_analyse_croisée").Range("A1"), PlotBy:=xlRows
    .ActiveChart.SeriesCollection.NewSeries
    .ActiveChart.SeriesCollection.NewSeries
    .ActiveChart.SeriesCollection(1).XValues = "=R_analyse_croisée!R3C2:R4C10"
    .ActiveChart.SeriesCollection(1).Values = "=R_analyse_croisée!R54C2:R54C10"
    .ActiveChart.SeriesCollection(1).name = "=""Fraude brute émetteur à l'étranger"""
    .ActiveChart.SeriesCollection(2).Values = "=R_analyse_croisée!R53C2:R53C10"
    .ActiveChart.SeriesCollection(2).name = "=""Fraudes brute émetteur en France"""
    .ActiveChart.Location Where:=xlLocationAsNewSheet
    End With
    
    With xls.ActiveChart
        .HasAxis(xlCategory, xlPrimary) = True
        .HasAxis(xlValue, xlPrimary) = True
    End With
    
    With xls
    .ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
    .ActiveChart.PlotArea.Select
    .ActiveChart.SeriesCollection.NewSeries
    .ActiveChart.SeriesCollection(3).Values = "=R_analyse_croisée!R32C2:R32C10"
    .ActiveChart.SeriesCollection(3).ChartType = xlXYScatter
    End With
    
    With xls.ActiveChart.SeriesCollection(3).Border
        .Weight = xlHairline
        .LineStyle = xlNone
    End With
    With xls.ActiveChart.SeriesCollection(3)
        .MarkerBackgroundColorIndex = xlAutomatic
        .MarkerForegroundColorIndex = xlAutomatic
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 5
        .Shadow = False
    End With
    xls.ActiveChart.SeriesCollection(3).ApplyDataLabels AutoText:=True, LegendKey:=False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False
        
    With xls
    .ActiveChart.SeriesCollection(3).Points(1).DataLabel.Select
    .Selection.Left = 45
    .Selection.Top = 100
    .ActiveChart.SeriesCollection(3).Points(2).DataLabel.Select
    .Selection.Left = 102
    .Selection.Top = 60
    .ActiveChart.SeriesCollection(3).Points(3).DataLabel.Select
    .Selection.Left = 159
    .Selection.Top = 119
    .ActiveChart.SeriesCollection(3).Points(4).DataLabel.Select
    .Selection.Left = 214
    .Selection.Top = 50
    .ActiveChart.SeriesCollection(3).Points(5).DataLabel.Select
    .Selection.Left = 273
    .Selection.Top = 50
    .ActiveChart.SeriesCollection(3).Points(6).DataLabel.Select
    .Selection.Left = 330
    .Selection.Top = 159
    .ActiveChart.SeriesCollection(3).Points(7).DataLabel.Select
    .Selection.Left = 384
    .Selection.Top = 132
    .ActiveChart.SeriesCollection(3).Points(8).DataLabel.Select
    .Selection.Left = 446
    .Selection.Top = 327
    .ActiveChart.SeriesCollection(3).Points(9).DataLabel.Select
    .Selection.Left = 503
    .Selection.Top = 315
    .ActiveChart.SeriesCollection(3).Select
    End With
    With xls.Selection.Border
        .Weight = xlHairline
        .LineStyle = xlNone
    End With
    With xls.Selection
        .MarkerBackgroundColorIndex = xlAutomatic
        .MarkerForegroundColorIndex = xlAutomatic
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 5
        .Shadow = False
    End With
    xls.ActiveChart.PlotArea.Select
    xls.ActiveChart.SeriesCollection(3).DataLabels.Select
    With xls.ActiveChart.SeriesCollection(3).DataLabels.Border
        .Weight = xlHairline
        .LineStyle = xlNone
    End With
    xls.Selection.Shadow = False
    xls.Selection.Interior.ColorIndex = xlAutomatic
    xls.Selection.AutoScaleFont = True
    With xls.Selection.Font
        .name = "Arial"
        .FontStyle = "Gras"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
        .Background = xlAutomatic
    End With
    xls.ActiveChart.PlotArea.Select
    With xls.ActiveChart.SeriesCollection(3).DataLabels.Border
        .ColorIndex = 16
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    With xls.Selection.Interior
        .ColorIndex = 2
        .PatternColorIndex = 1
        .Pattern = xlSolid
    End With
    With xls
    .ActiveChart.SeriesCollection(3).DataLabels.Select
    .ActiveChart.SeriesCollection(3).Points(4).DataLabel.Select
    .ActiveChart.SeriesCollection(3).Points(3).DataLabel.Select
    .Selection.Left = 158
    .Selection.Top = 112
    .ActiveChart.SeriesCollection(3).Points(5).DataLabel.Select
    .Selection.Left = 272
    .Selection.Top = 74
    End With
    
    xls.ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:=False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False
    xls.ActiveChart.SeriesCollection(2).ApplyDataLabels AutoText:=True, LegendKey:=False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False


    xls.ActiveChart.SeriesCollection(1).DataLabels.Select
    With xls.Selection.Font
        .name = "Arial"
        .FontStyle = "Gras"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    xls.ActiveChart.SeriesCollection(1).DataLabels.Select
    xls.Selection.AutoScaleFont = True
    With xls.Selection.Font
        .name = "Arial"
        .FontStyle = "Gras"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    xls.ActiveChart.SeriesCollection(2).DataLabels.Select
    xls.Selection.AutoScaleFont = True
    With xls.Selection.Font
        .name = "Arial"
        .FontStyle = "Gras"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    xls.ActiveChart.SeriesCollection(2).Select
    With xls.Selection.Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
    End With
    xls.Selection.Shadow = False


    With xls.Selection.Interior
        .ColorIndex = 24
        .Pattern = xlSolid
    End With
    xls.ActiveChart.SeriesCollection(1).Select
    With xls.Selection.Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
    End With
    xls.Selection.Shadow = False
    xls.Selection.InvertIfNegative = False
    With xls.Selection.Interior
        .ColorIndex = 50
        .Pattern = xlSolid
    End With
    With xls
    .ActiveChart.SeriesCollection(3).DataLabels.Select
    .ActiveChart.SeriesCollection(3).Points(5).DataLabel.Select
    .Selection.Left = 273
    .Selection.Top = 78
    .ActiveChart.SeriesCollection(3).Points(4).DataLabel.Select
    .Selection.Left = 215
    .Selection.Top = 51
    .ActiveChart.Legend.Select
    .ActiveChart.Legend.LegendEntries(3).Select
    .Selection.Delete
    .ActiveChart.Legend.Select
    .Selection.Left = 31
    .Selection.Top = 18
    .Selection.Width = 186
    .Selection.Width = 182
    End With
End Sub

Controle Grphique Excel Via Access

1 réponse

Votre réponse

Discussions similaires