VBA: Einfügen von Diagrammen automatisieren

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
Label MsgBox
Bild

Betrifft: VBA: Einfügen von Diagrammen automatisieren
von: Jason
Geschrieben am: 17.07.2015 13:16:54

Hallo Wissen!
Ich baue eine Controlling Tabelle mit diversen KPI's auf. Dabei setzte ich unzählige gleiche Diagramme ein. Zu diesem Zweck habe ich mir eine eigene Diagrammvorlage abgespeichert, was das formatieren enorm erleichtert.
Jezt möchte ich folgende zwei Dinge erreichen:
1. Diagramm wird auf Knopfdruck aufgrund des ausgewählten Bereiches erstellt;
2. Die grösse des Diagrammes wird automatisch angepasst (Height & Width).
Der Makrorecorder hat folgenden Code (Ausschnitt) ausgegeben:
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ApplyChartTemplate ( _
"C:\Pfad\Charts\KPI.crtx")
ActiveChart.SetSourceData Source:=Range("KPIs!$B$80:$E$80;KPIs!$B$85:$E$85")
ActiveSheet.Shapes("Diagramm 26").Height = 130.6771653543
ActiveSheet.Shapes("Diagramm 26").Width = 153.6377952756
Das Problem sind der fixe Range (welcher dynamisch/aufgrund der Auswahl sein sollte) und die Bezeichnung des Diagrammes, welches ebenfalls dynamisch sein sollte.
Vielen Dank für die Unterstützung
Gruss
Jason

Bild

Betrifft: AW: VBA: Einfügen von Diagrammen automatisieren
von: Beverly
Geschrieben am: 17.07.2015 14:24:55
Hi Jason,
was verstehst du unter "aufgrund der Auswahl" bzw. gibt es eine Systematik, nach der sich die Wertebereiche der Diagramme im Tabellenblatt befinden? Wie soll die Position des Diagramms fetgelegt werden? Und nach welchem Prinzip sollen die Namen vergeben werden?




Bild

Betrifft: AW: VBA: Einfügen von Diagrammen automatisieren
von: Jason
Geschrieben am: 17.07.2015 14:33:02
Hallo Beverly
Aufgrund der Auswahl: Den Zellenbereich welchen ich jeweils manuell markiere/auswähle.
Die Position des Diagramms spielt keine Rolle, kann Grundsätzlich irgendwo eingefügt werden. Ich verschiebe diese dann jeweils manuell.
Die Namensvergabe spielt keine Rolle - kann eine forlaufende Bezeichnung sein!
Hoffe du verstehst was ich meine..?

Bild

Betrifft: AW: VBA: Einfügen von Diagrammen automatisieren
von: Beverly
Geschrieben am: 17.07.2015 14:48:47
Hi Jason,
wenn du einen Zellbereich auswählst und das Diagramm erstellst, dann wird der Datenbereich automatisch zugewiesen:

Sub DiasErstellen()
    With ActiveSheet.Shapes.AddChart(0, 0, 0, 0).Chart
        .ApplyChartTemplate ("C:\Pfad\Charts\KPI.crtx")
        .Parent.Height = 130.6771653543
        .Parent.Width = 153.6377952756
        .Parent.Top = Selection.Cells(1).Top
    End With
End Sub



Bild

Betrifft: AW: VBA: Einfügen von Diagrammen automatisieren
von: Jason
Geschrieben am: 17.07.2015 15:00:29
Hi Beverly
Ich erhalte einen Laufzeitfehler 1004: "Anwendungs- oder objektdefinierter Fehler" bei dieser Codezeile:


With ActiveSheet.Shapes.AddChart(0, 0, 0, 0).Chart


Bild

Betrifft: AW: VBA: Einfügen von Diagrammen automatisieren
von: Beverly
Geschrieben am: 17.07.2015 15:11:43
Hi Jason,
komisch - sollte bei Excel2010 eigentlich genau so funktionieren wie bei 2013...
Versuche es mal so:

Sub DiasErstellen()
    With ActiveSheet.ChartObjects.Add(0, 0, 0, 0).Chart
        .ApplyChartTemplate ("C:\Pfad\Charts\KPI.crtx")
        .SetSourceData Source:=Selection
        .Parent.Height = 130.6771653543
        .Parent.Width = 153.6377952756
        .Parent.Top = Selection.Cells(1).Top
    End With
End Sub



Bild

Betrifft: AW: VBA: Einfügen von Diagrammen automatisieren
von: Jason
Geschrieben am: 17.07.2015 15:17:04
Jetzt erhalte ich einen Laufzeitfehler: Die Methode 'Add' für das Objekt 'ChartObjects' ist fehlgeschlagen. Bei der gleichen Codezeile wie vorhin...!

Bild

Betrifft: AW: VBA: Einfügen von Diagrammen automatisieren
von: Beverly
Geschrieben am: 17.07.2015 15:35:25
Du hast den Zellbereich, aus dem das Diagramm erstellt werden soll, im Tabellenblatt markiert?




Bild

Betrifft: AW: VBA: Einfügen von Diagrammen automatisieren
von: Jason
Geschrieben am: 17.07.2015 15:51:48
Habe ich ja - aber ich habe zwischenzeitlich herausgefunden warum der Code nicht funkt!
Wenn den Zellenbereich angrenzend ist, funktioniert der Code (Bsp: Zellenbereich: B130:E131)
Ich erstelle jedoch Diagramme über mehrere Zellenbereiche mit Leerzeilen dazwischen (Bsp: B130:E130 UND B132:E132).
Von hand funktioniert die Diagrammerstellung einwandfrei - jedoch nicht wenn eine Zeile, welche dazwischen liegt, nicht beachtet werden muss...!

Bild

Betrifft: AW: VBA: Einfügen von Diagrammen automatisieren
von: Beverly
Geschrieben am: 17.07.2015 16:03:26
Ich habe den Code getestet mit den Zeilen aus deinem Ursprungscode

ActiveChart.SetSourceData Source:=Range("KPIs!$B$80:$E$80;KPIs!$B$85:$E$85")

also mit markierten Zellbereichen B80:E80 UND B85:E85 - es gibt keinen Fehler, gleichgültig ob Leerzellen dazwischen liegen oder nicht. Das könnte aber vielleicht am Diagrammtyp liegen, da ich deine Diagrammvorlage nicht kenne. Lade doch mal deine Mappe mit einem aus deiner Vorlage erstellten Diagramm und ein paar Daten hoch, damit ich das damit testen kann.



Bild

Betrifft: AW: VBA: Einfügen von Diagrammen automatisieren
von: Jason
Geschrieben am: 17.07.2015 16:21:47
Hallo Beverly
Vielen Dank schon mal für deine Unterstützung.
Beiliegend das Excel mit ein paar Testdaten und dem Diagramm.
Excel: https://www.herber.de/bbs/user/98903.xlsm
Gruss
Jason

Bild

Betrifft: AW: VBA: Einfügen von Diagrammen automatisieren
von: Jason
Geschrieben am: 17.07.2015 18:02:30
... habe es bei mir mit Excel 2013 probiert - funktioniert einwandfrei :(
muss also an der V 2010 liegen! Weiss aber nicht, wie ich das Makro anpassen muss, damit es unter V 2010 läuft.
Für deine Hilfe bedanke ich mich nochmals.
Gruss
Jason

Bild

Betrifft: AW: VBA: Einfügen von Diagrammen automatisieren
von: Beverly
Geschrieben am: 17.07.2015 19:53:29
Hi Jason,
hast du es in Excel2010 mal in einer neuen Arbeitsmappe getestet? Ich habe den Code jetzt von jemandem mit Excel2010 testen lassen: es wird kein Fehler ausgegeben. Das Problem muss also in deiner Mappe oder bei Excel auf deinem Rechner liegen.




Bild

Betrifft: AW: VBA: Einfügen von Diagrammen automatisieren
von: fcs
Geschrieben am: 18.07.2015 10:44:47
Hallo Jason,
ich hab deine Datei unter Excel 2010 (Version 14.0.7151.5001 (32 bit)), Windows Vista getestet.
Ich hatte 2 Probleme nachdem ich das 1. Diagramm als Diagramm-Mustervorlage gespeichert hatte.
1. Bei der Selektion mehrere Zellbereiche (2. und 3. Diagramm) kommt eine Fehlermeldung
2. Die Beschriftung der Datenpunkte funktioniert nicht richtig.
Mit einigen Klimmzügen im Makro hab ich es hinbekommen, dass das Diagramm erzeugt wird. Dabei wird das Diagramm auf Basis der ersten 2 Zeilen des Datenblocks erstelle und im Anschluß dann der selekterte Datenbereich zugewiesen.
Mit einer Erweiterung kann man alle Diagramme eines Datenblocks erzeugen, ohne jeweils die Zellen markieren zu müssen. Es muss dann nur die linke-obere Zelle des Datenbereichs vor dem Makrostart markiert werden.
Gruß
Franz

Sub TortenDiagramm_einfuegen()
'
'
    Dim objChart As Chart
    Dim objDataLabels As DataLabels
    Dim rngData As Range
    Dim dblTop As Double, dblLeft As Double
'
    'Bei mehreren markierten Bereichen den Bereich für die Daten des Diagramms in _
            einer Varialen neu zusammensetzen
    If Selection.Areas.Count > 1 Then
        Set rngData = Application.Union(Selection.Areas(1), Selection.Areas(2))
        'die ersten Beiden Datenbereich für Diagramm-Erstellung markieren
        rngData.Areas(1).Resize(2, rngData.Areas(1).Columns.Count).Select
    Else
        Set rngData = Selection
    End If
    
    'Top und Left-Position vorgeben
    dblTop = rngData.Areas(1).Range("A1").Top
    dblLeft = rngData.Areas(1).Range("A1").Offset(0, 5).Left
    
    'Chartobject erstellen
    ActiveSheet.ChartObjects.Add Top:=dblTop, Left:=dblLeft, _
            Width:=153.54, Height:=130.67
    Set objChart = ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Chart
    With objChart
        'Vorlage zuweisen
        .ApplyChartTemplate ( _
            "C:\Users\Admin\AppData\Roaming\Microsoft\Templates\Charts\KPI_TORMAX.crtx")
        'Daten zuweisen
        .SetSourceData Source:=rngData
        
        'Beschriftung neu setzen
        .ApplyDataLabels
        Set objDataLabels = .SeriesCollection(1).DataLabels
        With objDataLabels
            .AutoText = True
            .ShowCategoryName = False
            .ShowLegendKey = False
            .ShowSeriesName = False
            .ShowValue = False
            .ShowPercentage = True
        End With
    End With
    rngData.Select
End Sub
Sub TortenDiagramm_Alle_einfuegen()
'
' Vor dem Start des Makros muss die linke ober Zelle des Datenbereichs der _
    Diagramme selektiert werden
        
'
    Dim objChart As Chart, objChartObject As ChartObject
    Dim objDataLabels As DataLabels
    Dim rngData As Range
    Dim dblTop As Double, dblLeft As Double
    Dim wks As Worksheet, Zeile As Long, Zeile1 As Long, Spalte1 As Long, Spalte2 As Long
    Dim intDiagramm As Integer
'
    If MsgBox("Ist die linke obere Zelle der Diagramm-Daten die aktive Zelle?", _
            vbQuestion + vbOKCancel, "Diagramme erstellen") = vbCancel Then Exit Sub
            
    Set wks = ActiveSheet
    Zeile1 = ActiveCell.Row                     'Zeile mit den Kategorien
    Spalte1 = ActiveCell.Column                 'Spalte mit den Reihennamen
    Spalte2 = ActiveCell.End(xlToRight).Column  'letzte Spalte mit Diagrammdaten
    
    intDiagramm = 0
    For Zeile = Zeile1 + 1 To ActiveCell.End(xlDown).Row
        With wks
            'Bereich mit den Daten des Diagramms in einer Varialen zusammensetzen
            Set rngData = Application.Union( _
                .Range(.Cells(Zeile1, Spalte1), .Cells(Zeile1, Spalte2)), _
                .Range(.Cells(Zeile, Spalte1), .Cells(Zeile, Spalte2)))
            'Kategorien plus 1. Datenbereich selektieren (nur so fnktioniert Diagrammerstellung) _
            .Range(.Cells(Zeile1, Spalte1), .Cells(Zeile1 + 1, Spalte2)).Select
            
            'Top und Left-Position vorgeben
            dblTop = .Cells(Zeile1, Spalte1).Top
            dblLeft = .Cells(Zeile1, Spalte2 + 2 + intDiagramm * 3).Left - 20
            intDiagramm = intDiagramm + 1
        End With
        
        'Chartobject erstellen
        wks.ChartObjects.Add Top:=dblTop, Left:=dblLeft, _
                Width:=153.54, Height:=130.67
        Set objChart = wks.ChartObjects(wks.ChartObjects.Count).Chart
        With objChart
            'Vorlage zuweisen
            .ApplyChartTemplate ( _
                "C:\Users\Admin\AppData\Roaming\Microsoft\Templates\Charts\KPI_TORMAX.crtx")
            'Daten zuweisen
            .SetSourceData Source:=rngData
            
            'Beschriftung neu setzen
            .ApplyDataLabels
            Set objDataLabels = .SeriesCollection(1).DataLabels
            With objDataLabels
                .AutoText = True
                .ShowCategoryName = False
                .ShowLegendKey = False
                .ShowSeriesName = False
                .ShowValue = False
                .ShowPercentage = True
            End With
        End With
    Next
    
End Sub


Bild

Betrifft: AW: VBA: Einfügen von Diagrammen automatisieren
von: Jason
Geschrieben am: 20.07.2015 10:28:55
Hallo Franz, hallo Beverly
Ich danke euch beiden recht herzlichg für die Unterstützung.
Mit deinem Code Franz funktioniert die Diagramm herstellung einwandfrei - Danke für den Code.
Dein Code Beverly funktioniert bei mir unter Excel 2013 ebenfalls. Ich habe, deinem Rat folgend, eine neue Arbeitsmappe erstellt mit dem Code, doch auch hier erhalte ich nur Fehlermeldungen! Habe die Einstellungen überprüft, jedoch nichts gefunden was den Code an der Ausführung hindern sollte!
Kann es sein, dass ich ein Steuerelement aktivieren muss? Fall ja, welches könnte dies sein?
Vielen Dank und Grüsse
Jason

Bild

Betrifft: AW: VBA: Einfügen von Diagrammen automatisieren
von: Beverly
Geschrieben am: 20.07.2015 10:47:43
Hi Jason,
Steuerelemente haben mit dem Code nichts zu tun.
Hast du den Code mal auf einem anderen Rechner mit Excel2010 getestet? Ich würde darauf tippen, dass mit deinem Exel irgendetwas nicht stimmt. Getestet ist der Code mit Excel2010 und Win 7 ohne Fehler.




 Bild

Beiträge aus den Excel-Beispielen zum Thema " VBA: Einfügen von Diagrammen automatisieren"