|
Hallo zusammen,
ich möchte über VBA ein dynamisches Diagramm erstellen, welches auf Daten aus .csv Dateien basiert (13 Spalten mit je 1500 – 2000 Zeilen). Da ich verschiedene .csv Dateien habe mit jeweils unterschiedlichen Werten, möchte ich per VBA ein Makro erstellen, das sich immer aus den gleichen Zellen die Werte zieht und diese in ein dynamisches Diagramm packt.
Ich habe dazu das dynamische Diagramm einmal aufgestellt und dann versucht, das Ganze über den Makro-Recorder zu rekonstruieren und dann bestimmte Werte allgemeingültig umzuschreiben (z.B. [Name des Worksheets] in [Worksheet1]), da die Quellen-.csv ja unterschiedliche Namen haben werden.
Allerdings scheitere ich momentan daran, die Werte allgemeingültig in das Diagramm zu bekommen. Meine Vorgehensweise ist, dass ich beispielsweise die X-Werte über den Formalmanager definiert habe als „X_Werte“ und genauso die verschiedenen Y-Werte.
Für das Diagramm habe ich dann für die „Werte der Reihe X:“ „=StartEnde_var.xlsm!X_Werte“ eingegeben und das Ganze parallel dazu für die Y-Werte. „StartEnde_var“ ist dabei der Name des Worksheets.
Mein großes Problem ist nun, dass ich es nicht hinkriege, diesen Schritt so in VBA verfassen, dass der Worksheetname universell ist. In allen vorherigen Schritten konnte ich „StartEnde_var“ mit [Worksheet1] ersetzen, nur hier klappt das irgendwie nicht.
Weiß jemand woran das liegen könnte?
Hier der entsprechende Code dazu:
Sub Dia()
'
' Dia Makro
'
'
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1)), TrailingMinusNumbers _
:=True
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array _
(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array( _
27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), _
Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array( _
40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), _
Array(47, 1), Array(48, 1)), TrailingMinusNumbers:=True
Selection.Delete Shift:=xlToLeft
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlToLeft
Range("C2:E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlToLeft
Range("D2:F2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlToLeft
Range("E2:G2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlToLeft
Range("F2:H2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlToLeft
Range("G2:I2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlToLeft
Range("H2:J2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlToLeft
Range("I2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlToLeft
Range("J2:L2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlToLeft
Range("K2:M2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlToLeft
Range("L2:N2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlToLeft
Range("M2:O2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlToLeft
Range("C1").Select
Selection.Delete Shift:=xlToLeft
Range("D1,F1,H1,J1,L1,N1,P1,R1,T1,V1").Select
Range("V1").Activate
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Sheets.Add After:=ActiveSheet
Sheets(2).Select
Dim lastRow As Long
With Worksheets(1)
lastRow = .Range("M" & Rows.Count).End(xlUp).Row
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Worksheets(1).Range("A1:M" & lastRow)
Dim first_sheet As String
first_sheet = Worksheets(1).Name
Dim second_sheet As String
second_sheet = Worksheets(2).Name
Dim active_workbook As String
active_workbook = ActiveWorkbook.Name
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SetElement (msoElementLegendRight)
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveSheet.Shapes("Diagramm 1").IncrementLeft -51.75
ActiveSheet.Shapes("Diagramm 1").IncrementTop -207
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveSheet.Shapes("Diagramm 1").ScaleWidth 1.16875, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Diagramm 1").ScaleHeight 1.1128474045, msoFalse, _
msoScaleFromTopLeft
'Formeln in Namensmanager
ActiveWorkbook.Names.Add Name:="GültigkeitsBereich", RefersToR1C1:= _
"='" & first_sheet & "'!R2C1:INDEX('" & first_sheet & "'!R2C1:R1697C1,COUNT('" & first_sheet & "'!R2C1:R1697C1))"
ActiveWorkbook.Names.Add Name:="Name_Reihe", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R1C16,MATCH('" & second_sheet & "'!R20C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Name_Reihe2", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R1C16,MATCH('" & second_sheet & "'!R21C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Name_Reihe3", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R1C16,MATCH('" & second_sheet & "'!R22C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Name_Reihe4", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R1C16,MATCH('" & second_sheet & "'!R23C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Name_Reihe5", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R1C16,MATCH('" & second_sheet & "'!R24C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Name_Reihe6", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R1C16,MATCH('" & second_sheet & "'!R25C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Name_Reihe7", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R1C16,MATCH('" & second_sheet & "'!R26C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Name_Reihe8", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R1C16,MATCH('" & second_sheet & "'!R27C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Name_Reihe9", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R1C16,MATCH('" & second_sheet & "'!R28C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Name_Reihe10", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R1C16,MATCH('" & second_sheet & "'!R29C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Name_Reihe11", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R1C16,MATCH('" & second_sheet & "'!R30C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Name_Reihe12", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R1C16,MATCH('" & second_sheet & "'!R31C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="X_Werte", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R2C1:R1697C1,MATCH('" & second_sheet & "'!R20C2,'" & first_sheet & "'!R2C1:R1697C1,0)):INDEX('" & first_sheet & "'!R2C1:R1697C1,MATCH('" & second_sheet & "'!R21C2,'" & first_sheet & "'!R2C1:R1697C1,0))"
ActiveWorkbook.Names.Add Name:="Y_Werte_var1", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R20C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R20C4,'" & first_sheet & "'!R1C1:R1C16,0)):INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R21C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R20C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Y_Werte_var2", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R20C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R21C4,'" & first_sheet & "'!R1C1:R1C16,0)):INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R21C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R21C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Y_Werte_var3", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R20C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R22C4,'" & first_sheet & "'!R1C1:R1C16,0)):INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R21C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R22C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Y_Werte_var4", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R20C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R23C4,'" & first_sheet & "'!R1C1:R1C16,0)):INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R21C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R23C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Y_Werte_var5", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R20C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R24C4,'" & first_sheet & "'!R1C1:R1C16,0)):INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R21C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R24C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Y_Werte_var6", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R20C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R25C4,'" & first_sheet & "'!R1C1:R1C16,0)):INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R21C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R25C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Y_Werte_var7", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R20C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R26C4,'" & first_sheet & "'!R1C1:R1C16,0)):INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R21C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R26C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Y_Werte_var8", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R20C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R27C4,'" & first_sheet & "'!R1C1:R1C16,0)):INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R21C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R27C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Y_Werte_var9", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R20C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R28C4,'" & first_sheet & "'!R1C1:R1C16,0)):INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R21C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R28C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Y_Werte_var10", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R20C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R29C4,'" & first_sheet & "'!R1C1:R1C16,0)):INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R21C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R29C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Y_Werte_var11", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R20C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R30C4,'" & first_sheet & "'!R1C1:R1C16,0)):INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R21C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R30C4,'" & first_sheet & "'!R1C1:R1C16,0))"
ActiveWorkbook.Names.Add Name:="Y_Werte_var12", RefersToR1C1:= _
"=INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R20C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R31C4,'" & first_sheet & "'!R1C1:R1C16,0)):INDEX('" & first_sheet & "'!R1C1:R3000C16,MATCH('" & second_sheet & "'!R21C2,'" & first_sheet & "'!R1C1:R3000C1,0),MATCH('" & second_sheet & "'!R31C4,'" & first_sheet & "'!R1C1:R1C16,0))"
'Auswahlfelder Zeit und Namen füllen
Range("C20").Select
ActiveCell.FormulaR1C1 = "Kennlinien"
Range("D20:D31").Select
Selection.Style = "Eingabe"
Range("B20:B21").Select
Selection.Style = "Eingabe"
Range("A20").Select
ActiveCell.FormulaR1C1 = "Min"
Range("A21").Select
ActiveCell.FormulaR1C1 = "Max"
'Gueltigkeitsbereich in Zellen
Range("B20").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=GültigkeitsBereich"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("B21").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=GültigkeitsBereich"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("D20:D31").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="='" & first_sheet & "'!$B$1:$M$1"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'X_Werte in Zeitformat
Range("B20:B21").Select
Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
'X_Werte füllen
Range("B20").Value = Worksheets(1).Range("A2").Value
Range("B21").Value = Worksheets(1).Range("A3").Value
Range("D20").Value = Worksheets(1).Range("B1").Value
Range("I22").Select
ActiveSheet.Shapes.AddChart2(332, xlXYScatterLines).Select
ActiveSheet.Shapes("Diagramm 2").IncrementLeft -41.25
ActiveSheet.Shapes("Diagramm 2").IncrementTop 60.75
ActiveSheet.ChartObjects("Diagramm 2").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = "" = " & active_workbook & ""!Name_Reihe"
ActiveChart.FullSeriesCollection(1).XValues = "" = " & active_workbook & ""!X_Werte"
ActiveChart.FullSeriesCollection(1).Values = "" = " & active_workbook & ""!Y_Werte_var1"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = "=='" & first_sheet & "'.xlsm!Name_Reihe2"
ActiveChart.FullSeriesCollection(1).XValues = "=='" & first_sheet & "'.xlsm!X_Werte"
ActiveChart.FullSeriesCollection(1).Values = "=='" & first_sheet & "'.xlsm!Y_Werte_var2"
End With
End Sub |