Office: (Office 2016) VBA - Zahlen aus anderer xlsm Datei übernehmen

Hallo,

ich versuche Zahlen aus einer anderen Datei zu bekommen und in einem Diagramm anzuzeigen, jedoch kommt immer die Fehlermeldung "out of range".

Mein Codestück:

ElseIf OptionButtonQuartal.Value = True Then
s_von = Workbooks("C:\Users\Tim\Desktop\Zeiterfassung\202008110248091.xlsm").Worksheets("Wichtige Zahlen").Range("$E")
s_bis = Workbooks("C:\Users\Tim\Desktop\Zeiterfassung\202008110248091.xlsm").Worksheets("Wichtige Zahlen").Range("$G")

.
.
.
ActiveChart.SetSourceData Source:=Range("'Wichtige Zahlen'! " & s_von & "$71:" & s_bis & "$71,'Wichtige Zahlen'!" & s_von & "$74:" & s_bis & "$74,'Wichtige Zahlen'!" & s_von & "$79:" & s_bis & "$79")

Was mache ich da falsch? Ich vermute, das ich die Syntax von S_von und S_bis falsch habe.

MFG
Tim

 

Hola,

dir wird doch schon geholfen.....

https://www.clever-excel-forum.de/Thread-VBA-Zahlen-aus-anderer-xlsm-Datei-uebernehmen

Gruß,
steve1da

 

Hallo Tim,

diese muss tatsächlich heißen:
s_von = Workbooks( "C:\Users\Tim\Desktop\Zeiterfassung\202008110248091.xlsm").Worksheets( "Wichtige Zahlen").Range("$E:$E")
s_bis = Workbooks( "C:\Users\Tim\Desktop\Zeiterfassung\202008110248091.xlsm").Worksheets( "Wichtige Zahlen").Range("$G:$G")

Und kann das überhaupt so funktionieren???

Code:

With Workbooks.Open("C:\Users\Tim\Desktop\Zeiterfassung\202008110248091.xlsm")
  With .Worksheets("Wichtige Zahlen")
     set s_von=.Range("$E:$E")
     set s_bis =.Range("$G:$G")
  End With
End With

sofern s_von und s_bis als "As Range" deklariert sind, woran ich zweifle.

Anmerkung: Das Leerzeichen vor .xlsm wird durch den Forumseditor verursacht und gehört natürlich weggedacht.

 

Hallo Anton,
bei s_von und s_bis soll es sich ja um Werte handeln die in den Diagramm einfließen, daher soll nicht die gesamte Spalte angesprochen werden.
Also wirklich
s_von = "$B"
s_bis = "$D" nur befinden die sich im anderen Dokument.

Die beiden kommen dann in diesen Code rein:
ActiveChart.SetSourceData Source:=Range("'Wichtige Zahlen'! " & s_von & "$71:" & s_bis & "$71,'Wichtige Zahlen'!" & s_von & "$74:" & s_bis & "$74,'Wichtige Zahlen'!" & s_von & "$79:" & s_bis & "$79")

 

Hallo Tim,

Dann aber ist erst recht der Abfrageausdruck "...Range("$E") " oder "...Range("$G") " falsch. Im Folgenden habe ich angenommen, dass die Werte "$B" bzw. "$D" für die Variablen "s_von" bzw. "s_bis" in den Zellen E5 bzw. G5 des Arbeitsblattes "Wichtige Zahlen" der Arbeitsmappe "202008110248091.xlsm" (im Pfad "C:\Users\Tim\Desktop\Zeiterfassung") enthalten sind.

Code:

Option Explicit

Sub TestChart()
  Dim s_von As String
  Dim s_bis As String
  Dim rg1 As Range, rg2 As Range, rg3 As Range
  
  With Workbooks.Open("C:\Users\Tim\Desktop\Zeiterfassung\202008110248091.xlsm")

    With .Worksheets("Wichtige Zahlen")
       '[COLOR="#FF0000"]Die Zelladressen E5 bzw. G5 müssen eventuell noch geändert werden[/COLOR]
       s_von = .Range("[COLOR="#FF0000"]E5[/COLOR]").Value  'enthält den Wert "$B" 
       s_bis = .Range("[COLOR="#FF0000"]G5[/COLOR]").Value  'enthalt den Wert "$D"
       Set rg1 = .Range(s_von & "$71:" & s_bis & "$71")
       Set rg2 = .Range(s_von & "$74:" & s_bis & "$74")
       Set rg3 = .Range(s_von & "$79:" & s_bis & "$79")
    End With
    
    'Chart erzeugen
    ActiveChart.SetSourceData Source:=Union(rg1, rg2, rg3), PlotBy:=xlColumns 'xlRows
    
    'Workbook schließen ohne zu speichern:
    .Close SaveChanges:=False
  End With

  
End Sub

Anmerkung: Ich konnte allerdings das Makro nicht testen mangels Musterdateien.

 

Jetzt wird mir Typ unverträglich angezeigt.

Also hier mal ein ganzer Blick auf den Code.

Der 1.If Block greift erfolgreich nur auf die Werte der geöffneten Datei zu. Der 2. soll auf eine andere Datei zugreifen. Beide Dateien sind vom Aufbau gleich. Alle s_von und s_bis sollen auch im Else-block auf die jeweilige Spalte zeigen

If OptionButtonQuartal.Value = True And OptionButtonQuartal1.Value = True Or OptionButtonQuartal2.Value = True Or OptionButtonQuartal3.Value = True Or OptionButtonQuartal4.Value = True Then


ThisWorkbook.Charts.Add After:=Worksheets("Sheet0")
With ActiveChart
.HasTitle = True
.ChartTitle.Text = "Kernteam Diagramm"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Monat"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Stunden"
.Name = "Diagramm Kernteam Quartal2019"

If OptionButtonQuartal1.Value = True Then
s_von = "$B"
s_bis = "$D"

ElseIf OptionButtonQuartal2.Value = True Then
s_von = "$E"
s_bis = "$G"

ElseIf OptionButtonQuartal3.Value = True Then
s_von = "$H"
s_bis = "$J"

ElseIf OptionButtonQuartal4.Value = True Then
s_von = "$K"
s_bis = "$M"
End If

ActiveChart.SetSourceData Source:=Range( _
"'Wichtige Zahlen'! " & s_von & "$71:" & s_bis & "$71,'Wichtige Zahlen'!" & s_von & "$74:" & s_bis & "$74,'Wichtige Zahlen'!" & s_von & "$79:" & s_bis & "$79")
ActiveChart.FullSeriesCollection(1).Trendlines.Add Type:=xlLinear, Forward _
:=0, Backward:=0, DisplayEquation:=0, DisplayRSquared:=0, Name:= _
"Trendlinie"

.SeriesCollection(1).Name = "=""Erfasste Stunden Kernteam"""
.SeriesCollection(2).Name = "=""Mögliche Stunden Kernteam"""

End With

ActiveChart.FullSeriesCollection(1).Select
With Selection.Format.Fill
.ForeColor.RGB = RGB(85, 26, 139)
End With

ActiveChart.FullSeriesCollection(2).Select
With Selection.Format.Fill
.ForeColor.RGB = RGB(255, 62, 150)
End With


Bis hier hin funktioniert alles! Diagramm wird bei entsprechender Auswahl ausgegeben.
------------------------------------------------------------------------------------------------------------------------------------------------------------------

ElseIf OptionButtonQuartal.Value = True And OptionButtonQuartal12.Value = True Or OptionButtonQuartal22.Value = True Or OptionButtonQuartal32.Value = True Or OptionButtonQuartal42.Value = True Then


ThisWorkbook.Charts.Add After:=Worksheets("Sheet0")
With ActiveChart
.HasTitle = True
.ChartTitle.Text = "Kernteam Diagramm"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Monat"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Stunden"
.Name = "Diagramm Kernteam Quartal2020"


If OptionButtonQuartal12.Value = True Then

Dim rg1 As Range, rg2 As Range, rg3 As Range


With Workbooks.Open("C:\Users\Tim\Desktop\Zeiterfassung\202008110248091.xlsm")

With .Worksheets("Wichtige Zahlen")
Set rg1 = .Range("$B$71:$D$71")
Set rg2 = .Range("$B$74:$D$74")
Set rg3 = .Range("$B$79:$D$79")
.Close SaveChanges:=False

ActiveChart.SetSourceData Source:=Range(rg1, rg2, rg3)
ActiveChart.FullSeriesCollection(1).Trendlines.Add Type:=xlLinear, Forward _
:=0, Backward:=0, DisplayEquation:=0, DisplayRSquared:=0, Name:= _
"Trendlinie"
.SeriesCollection(1).Name = "=""Erfasste Stunden """
.SeriesCollection(2).Name = "=""Mögliche Stunden """


ActiveChart.FullSeriesCollection(1).Select
With Selection.Format.Fill
.ForeColor.RGB = RGB(85, 26, 139)
End With

ActiveChart.FullSeriesCollection(2).Select
With Selection.Format.Fill
.ForeColor.RGB = RGB(255, 62, 150)
End With

End With
End If
Unload Me
End Sub

 

Hallo Tim,

ich habe dran rumgebastelt, es aber mit SetSourceData nicht hinbekommen. Gab dauern irgendwelche Fehlermeldungen...

Probier doch mal den Weg:

HTML:

    s_von = "B"
    s_bis = "G"
    ActiveSheet.ChartObjects("Diagramm 1").Activate
    ActiveChart.FullSeriesCollection(1).Values = "=[Test.xlsx]Tabelle1!$" & s_von & "$6:$" & s_bis & "$6"
    ActiveChart.FullSeriesCollection(2).Values = "=[Test.xlsx]Tabelle1!$" & s_von & "$9:$" & s_bis & "$9"
    ActiveChart.FullSeriesCollection(2).XValues = "=[Test.xlsx]Tabelle1!$" & s_von & "$4:$" & s_bis & "$4"

Funktioniert eigentlich Deine If-Abfrage korrekt?? Bei der Menge an AND's und OR's würde ich erwarten, dass Excel nicht das tur, was du wirklich willst...

Und es ist doch auch nur der Dateiname unterscheidlich. Dann setze doch einfach dein .Name = "Diagramm Kernteam Quartal2019" bzw. .Name = "Diagramm Kernteam Quartal2020" in die If-Abfrage und mach den Rest nur einmal.

Gruß,
Lutz

 

Hallo Tim,

deinen Code habe ich im Folgenden an den kritischen Stellen rot gekennzeichnet.
Die fett und rot gekennzeichnete Stelle bezeichnet offenbar fehlenden Code, die du vorher sicher ergänzen müsstest.
Die 3 grün gekennzeichneten Kommentare sind anstelle des Range-Codes davor gedacht, falls du in Wahrheit die beiden String-Variablen "s_von" bzw. "s_bis" einsetzen möchtest.

Ich habe den Code auf syntaktische Korrektheit geprüft.
Ob er dann auch funktioniert, kann ich nicht sicher beurteilen, da mir die dazugehörigen Daten fehlen.

Code:

[COLOR="#FF0000"]Option Explicit[/COLOR]

[COLOR="#FF0000"]Sub TestChart()
  
  Dim s_von As String, s_bis As String[/COLOR]
  
  If OptionButtonQuartal.Value = True And _
     OptionButtonQuartal1.Value = True Or OptionButtonQuartal2.Value = True Or _
     OptionButtonQuartal3.Value = True Or OptionButtonQuartal4.Value = True Then
  
  
      ThisWorkbook.Charts.Add After:=Worksheets("Sheet0")
      With ActiveChart
        .HasTitle = True
        .ChartTitle.Text = "Kernteam Diagramm"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Monat"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Stunden"
        .Name = "Diagramm Kernteam Quartal2019"
        
        If OptionButtonQuartal1.Value = True Then
          s_von = "$B"
          s_bis = "$D"

        ElseIf OptionButtonQuartal2.Value = True Then
          s_von = "$E"
          s_bis = "$G"

        ElseIf OptionButtonQuartal3.Value = True Then
          s_von = "$H"
          s_bis = "$J"

        ElseIf OptionButtonQuartal4.Value = True Then
          s_von = "$K"
          s_bis = "$M"
        End If
        
        .SetSourceData Source:=Range( _
           "'Wichtige Zahlen'!" & s_von & "$71:" & s_bis & "$71," & _
           "'Wichtige Zahlen'!" & s_von & "$74:" & s_bis & "$74," & _
           "'Wichtige Zahlen'!" & s_von & "$79:" & s_bis & "$79")
        .FullSeriesCollection(1).Trendlines.Add _
           Type:=xlLinear, Forward:=0, Backward:=0, DisplayEquation:=0, DisplayRSquared:=0, _
           Name:="Trendlinie"
        
        .SeriesCollection(1).Name = "=""Erfasste Stunden Kernteam"""
        .SeriesCollection(2).Name = "=""Mögliche Stunden Kernteam"""
      
        With .FullSeriesCollection(1).Format.Fill
          .ForeColor.RGB = RGB(85, 26, 139)
        End With
        
        With .FullSeriesCollection(2).Format.Fill
          .ForeColor.RGB = RGB(255, 62, 150)
        End With
        
      End With
      
'Bis hier hin funktioniert alles! Diagramm wird bei entsprechender Auswahl ausgegeben.
'--------------------------------------------------------------------------------------
  ElseIf OptionButtonQuartal.Value = True And _
         OptionButtonQuartal12.Value = True Or OptionButtonQuartal22.Value = True Or _
         OptionButtonQuartal32.Value = True Or OptionButtonQuartal42.Value = True Then
      
      ThisWorkbook.Charts.Add After:=Worksheets("Sheet0")
      With ActiveChart
        .HasTitle = True
        .ChartTitle.Text = "Kernteam Diagramm"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Monat"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Stunden"
        .Name = "Diagramm Kernteam Quartal2020"
        
        If OptionButtonQuartal12.Value = True Then
[COLOR="#FF0000"]          [B] 'Inhalt des IF-Blockes fehlt offenbar,
           'zusammen mit dem abschließenden 'End If'
        End If[/B]
[/COLOR]        
[COLOR="#FF0000"]        Dim Wb As Workbook[/COLOR]
        Dim rg1 As Range, rg2 As Range, rg3 As Range
        
        [COLOR="#FF0000"]Set Wb = [/COLOR]Workbooks.Open("C:\Users\Tim\Desktop\Zeiterfassung\202008110248091.xlsm")
          
        With [COLOR="#FF0000"]Wb.[/COLOR]Worksheets("Wichtige Zahlen")
            Set rg1 = .Range("$B$71:$D$71") '[COLOR="#008000"][B]Set rg1 = .Range(s_von & "$71:" & s_bis & "$71")[/B][/COLOR]
            Set rg2 = .Range("$B$74:$D$74") '[COLOR="#008000"][B]Set rg2 = .Range(s_von & "$74:" & s_bis & "$74")[/B][/COLOR]
            Set rg3 = .Range("$B$79:$D$79") '[COLOR="#008000"][B]Set rg3 = .Range(s_von & "$79:" & s_bis & "$79")[/B][/COLOR]
        End With
          
        .SetSourceData Source:=[COLOR="#FF0000"]Union[/COLOR](rg1, rg2, rg3)
        .FullSeriesCollection(1).Trendlines.Add _
            Type:=xlLinear, Forward:=0, Backward:=0, DisplayEquation:=0, DisplayRSquared:=0, _
            Name:="Trendlinie"
          
        .SeriesCollection(1).Name = "=""Erfasste Stunden """
        .SeriesCollection(2).Name = "=""Mögliche Stunden """
        
        With .FullSeriesCollection(1).Format.Fill
            .ForeColor.RGB = RGB(85, 26, 139)
        End With
        
        With .FullSeriesCollection(2).Format.Fill
            .ForeColor.RGB = RGB(255, 62, 150)
        End With
          
[COLOR="#FF0000"]        Wb.Close SaveChanges:=False[/COLOR]
      
      End With
  End If
  
  Unload Me
  
End Sub

 

Hallo Tim,

das ist eine Ergänzung zu meinem Posting #8:
Was die AND und OR's bei deiner OptionButtons-Auswertung in den einleitenden IF's angeht, so gilt stets die Regel, dass ein AND eine höhere Priorität besitzt als ein OR.
Das bedeutet, dass Excel den folgenden Ausdruck

Code:

  If OptionButtonQuartal.Value = True And _
     OptionButtonQuartal1.Value = True Or OptionButtonQuartal2.Value = True Or _
     OptionButtonQuartal3.Value = True Or OptionButtonQuartal4.Value = True Then

so auswertet, als wenn dort folgende Klammerung stünde:

Code:

  If [COLOR="#FF0000"][B]([/B][/COLOR]OptionButtonQuartal.Value = True And _
     OptionButtonQuartal1.Value = True[COLOR="#FF0000"][B])[/B][/COLOR] Or OptionButtonQuartal2.Value = True Or _
     OptionButtonQuartal3.Value = True Or OptionButtonQuartal4.Value = True Then

Falls du das nicht möchtest - ich vermute das mal - dann müsstest du zB. folgende Klammerung vornehmen:

Code:

  If OptionButtonQuartal.Value = True And _
     [COLOR="#FF0000"][B]([/B][/COLOR]OptionButtonQuartal1.Value = True Or OptionButtonQuartal2.Value = True Or _
     OptionButtonQuartal3.Value = True Or OptionButtonQuartal4.Value = True[COLOR="#FF0000"][B])[/B][/COLOR] Then

Und man kann das Ganze auch vereinfachen zu:

Code:

  If OptionButtonQuartal.Value And _
     [COLOR="#FF0000"][B]([/B][/COLOR]OptionButtonQuartal1.Value Or OptionButtonQuartal2.Value Or _
     OptionButtonQuartal3.Value Or OptionButtonQuartal4.Value[COLOR="#FF0000"][B])[/B][/COLOR] Then

 

Vielen Dank für die schnelle Antwort. Ich habe jetzt den gesamten Code übernommen. Es wird auch kurz das Dokument, von dem die Werte genommen werden sollen auch geöffnet und geschlossen. Jedoch sind in dem ausgegebenen Diagramm die Werte von dem der Code ausging(also das Dokument wohin ich die Daten haben möchte). Wenn ich auf einen der Balken klicke steht oben "=DATENREIHE("Mögliche Stunden ";'Wichtige Zahlen'!$B$71:$D$71;'Wichtige Zahlen'!$B$79:$D$79;2)" , Also kein Verweis auf die Datei von dem eigentlich die Daten stammen sollten.


ElseIf OptionButtonQuartal.Value And _
(OptionButtonQuartal12.Value Or OptionButtonQuartal22.Value Or _
OptionButtonQuartal32.Value Or OptionButtonQuartal42.Value) Then

ThisWorkbook.Charts.Add After:=Worksheets("Sheet0")
With ActiveChart
.HasTitle = True
.ChartTitle.Text = "Kernteam Diagramm"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Monat"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Stunden"
.Name = "Diagramm Kernteam Quartal2020"

If OptionButtonQuartal12.Value = True Then
Dim Wb As Workbook
Dim rg1 As Range, rg2 As Range, rg3 As Range

Set Wb = Workbooks.Open("C:\Users\Tim\Desktop\Zeiterfassung\202008110248091.xlsm")

With Wb.Worksheets("Wichtige Zahlen")
Set rg1 = .Range("$B$71:$D$71")
Set rg2 = .Range("$B$74:$D$74")
Set rg3 = .Range("$B$79:$D$79")
End With

.SetSourceData Source:=Union(rg1, rg2, rg3)
.FullSeriesCollection(1).Trendlines.Add _
Type:=xlLinear, Forward:=0, Backward:=0, DisplayEquation:=0, DisplayRSquared:=0, _
Name:="Trendlinie"

.SeriesCollection(1).Name = "=""Erfasste Stunden """
.SeriesCollection(2).Name = "=""Mögliche Stunden """

With .FullSeriesCollection(1).Format.Fill
.ForeColor.RGB = RGB(85, 26, 139)
End With

With .FullSeriesCollection(2).Format.Fill
.ForeColor.RGB = RGB(255, 62, 150)
End With


Wb.Close SaveChanges:=False
End If
End With
End If
Unload Me
End Sub