Hallo zusammen,
seit einigen Tagen beschäftige ich mich mit VBA in Excel, da ich im Rahmen einer Versuchsreihe eine relativ große Datenmenge in Form von csv und txt Files generiert habe und dies nun auswerten möchte.
Ich habe in Summe 96 Versuche gefahren und für jeden Versuch jeweils 1 csv und 2 txt files generiert. Nun soll in einer Excel Datei jeder Versuch in einem separaten Tabellenblatt inkl. Diagramm importiert/ausgewertet werden. Ich habe hierzu bereits ein Makro (sehr einfach, bin ja Anfänger) geschrieben, welches auch funktioniert. Dieses Makro greift jedoch nur auf die Versuchsreihe zu, die ich innerhalb dieses Makros als Beispiel verwendet habe. Wie mache ich es, dass das Makro beginnend von Versuch 01 bis Versuch 96 alle Importe und die entsprechende Auswertung/Namensvergabe automatisch macht und die Versuche auch jeweils in ein neues Tabellenblatt ablegt?
Das entsprechende (sehr lange) Makro habe ich angefügt und hoffe bzw. bitte um Hilfe...
Vielen Dank,
Gruß Tolis
Sub Import_Messdaten()
'
' Import_Messdaten Makro
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\Streifenziehanlage\Versuche 21.01.2014\HBM-Daten\02.csv", Destination _
:=Range("$A$2"))
.Name = "02"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("A2:B2").Select
Range("B2").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("C2:D2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("E2:F2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Font.Size = 12
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "Versuch_002"
Columns("A:F").Select
Range("A3").Activate
Selection.ColumnWidth = 12
Columns("G:G").Select
Selection.ColumnWidth = 2
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("H1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\Streifenziehanlage\Versuche 21.01.2014\Aramis-Daten\SCHNITT-STREIFEN_002_point0.txt" _
, Destination:=Range("$H$1"))
.Name = "SCHNITT-STREIFEN_002_point0"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = " "
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("I5:M14").Select
Selection.Cut Destination:=Range("H5:L14")
Range("H4").Select
ActiveCell.FormulaR1C1 = "Stage"
Range("I4").Select
ActiveCell.FormulaR1C1 = "Time"
Range("J4").Select
ActiveCell.FormulaR1C1 = "phi_1"
Range("K4").Select
ActiveCell.FormulaR1C1 = "phi_2"
Range("L4").Select
ActiveCell.FormulaR1C1 = "Weg"
Range("H2:L3").Select
Range("H3").Activate
Selection.ClearContents
Range("H1:L1").Select
Selection.ClearContents
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Selection.Font.Size = 12
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "Back -> Point 0"
Columns("M:P").Select
Selection.Delete Shift:=xlToLeft
Columns("H:L").Select
Range("H2").Activate
Selection.ColumnWidth = 10
Columns("M:M").Select
Selection.ColumnWidth = 2
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("I4:L100").Select
Selection.NumberFormat = "0.00000"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\Streifenziehanlage\Versuche 21.01.2014\Aramis-Daten\SCHNITT-STREIFEN_002_point1.txt" _
, Destination:=Range("$N$1"))
.Name = "SCHNITT-STREIFEN_002_point1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = " "
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("O5:S14").Select
Selection.Cut Destination:=Range("N5:R14")
Range("N4").Select
ActiveCell.FormulaR1C1 = "Stage"
Range("O4").Select
ActiveCell.FormulaR1C1 = "Time"
Range("P4").Select
ActiveCell.FormulaR1C1 = "phi_1"
Range("Q4").Select
ActiveCell.FormulaR1C1 = "phi_2"
Range("R4").Select
ActiveCell.FormulaR1C1 = "Weg"
Range("N2:R3").Select
Selection.ClearContents
Range("N1:R1").Select
Selection.ClearContents
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Selection.Font.Size = 12
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "Pull -> Point 1"
Columns("N:R").Select
Range("N2").Activate
Selection.ColumnWidth = 10
Columns("S:V").Select
Selection.Delete Shift:=xlToLeft
Columns("S:S").Select
Selection.ColumnWidth = 2
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Rows("3:3").Select
Selection.Delete Shift:=xlUp
Range("O4:R100").Select
Selection.NumberFormat = "0.00000"
Range("U3").Select
Sheets("Tabelle1").Select
Sheets("Tabelle1").Name = "Versuch_002"
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""F_Back"""
ActiveChart.SeriesCollection(1).XValues = "=Versuch_002!$A$4:$A$501"
ActiveChart.SeriesCollection(1).Values = "=Versuch_002!$B$4:$B$501"
ActiveChart.SeriesCollection(1).Select
With Selection.Format.Line
.Visible = msoTrue
.Weight = 1.5
End With
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""F_Pull"""
ActiveChart.SeriesCollection(2).XValues = "=Versuch_002!$C$4:$C$501"
ActiveChart.SeriesCollection(2).Values = "=Versuch_002!$D$4:$D$501"
ActiveChart.SeriesCollection(2).Select
With Selection.Format.Line
.Visible = msoTrue
.Weight = 1.5
End With
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "=""phi1_p0"""
ActiveChart.SeriesCollection(3).XValues = "=Versuch_002!$I$4:$I$56"
ActiveChart.SeriesCollection(3).Values = "=Versuch_002!$J$4:$J$56"
ActiveChart.SeriesCollection(3).Select
With Selection.Format.Line
.Visible = msoTrue
.Weight = 1.5
End With
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).Name = "=""phi2_p0"""
ActiveChart.SeriesCollection(4).XValues = "=Versuch_002!$I$4:$I$56"
ActiveChart.SeriesCollection(4).Values = "=Versuch_002!$K$4:$K$56"
ActiveChart.SeriesCollection(4).Select
With Selection.Format.Line
.Visible = msoTrue
.Weight = 1.5
End With
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(5).Name = "=""phi1_p1"""
ActiveChart.SeriesCollection(5).XValues = "=Versuch_002!$O$4:$O$56"
ActiveChart.SeriesCollection(5).Values = "=Versuch_002!$P$4:$P$56"
ActiveChart.SeriesCollection(5).Select
With Selection.Format.Line
.Visible = msoTrue
.Weight = 1.5
End With
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(6).Name = "=""phi2_p1"""
ActiveChart.SeriesCollection(6).XValues = "=Versuch_002!$O$4:$O$56"
ActiveChart.SeriesCollection(6).Values = "=Versuch_002!$Q$4:$Q$56"
ActiveChart.SeriesCollection(6).Select
With Selection.Format.Line
.Visible = msoTrue
.Weight = 1.5
End With
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(7).Name = "=""Weg_p0"""
ActiveChart.SeriesCollection(7).XValues = "=Versuch_002!$I$4"
ActiveChart.SeriesCollection(7).XValues = "=Versuch_002!$I$4:$I$56"
ActiveChart.SeriesCollection(7).Values = "=Versuch_002!$L$4:$L$56"
ActiveChart.SeriesCollection(7).Select
With Selection.Format.Line
.Visible = msoTrue
.Weight = 1.5
End With
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(8).Name = "=""Weg_p1"""
ActiveChart.SeriesCollection(8).XValues = "=Versuch_002!$O$4:$O$56"
ActiveChart.SeriesCollection(8).Values = "=Versuch_002!$R$4:$R$56"
ActiveChart.SeriesCollection(8).Select
With Selection.Format.Line
.Visible = msoTrue
.Weight = 1.5
End With
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SeriesCollection(6).Select
ActiveChart.SeriesCollection(6).AxisGroup = 2
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SeriesCollection(5).Select
ActiveChart.SeriesCollection(5).AxisGroup = 2
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 2
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).AxisGroup = 2
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = 0.3
ActiveChart.Axes(xlValue, xlSecondary).MinimumScale = -0.2
ActiveChart.Axes(xlValue, xlSecondary).MajorUnit = 0.1
Selection.TickLabels.NumberFormat = "0.00"
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = 0
ActiveChart.Axes(xlValue).MaximumScale = 50
ActiveChart.Axes(xlValue).MajorUnit = 10
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MinimumScale = 0
ActiveChart.Axes(xlCategory).MaximumScale = 5
ActiveChart.ApplyLayout (1)
ActiveChart.Axes(xlValue).MajorGridlines.Select
Selection.Delete
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.PlotArea.Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 0, 0)
End With
'x-Achse
ActiveChart.Axes(xlCategory).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Zeit [sec]"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Zeit [sec]"
With Selection.Format.TextFrame2.TextRange.Characters(1, 10).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 10).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
' Primäre y-Achse
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Kraft [kN]" & Chr(13) & "Weg [mm]"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Kraft [kN]" & Chr(13) & "Weg [mm]"
With Selection.Format.TextFrame2.TextRange.Characters(1, 11).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 11).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
With Selection.Format.TextFrame2.TextRange.Characters(12, 8).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(12, 8).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
' Sekundäre y-Achse
ActiveChart.ChartArea.Select
ActiveChart.SetElement (msoElementSecondaryValueAxisTitleRotated)
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Text = "phi [-]"
Selection.Format.TextFrame2.TextRange.Characters.Text = "phi [-]"
With Selection.Format.TextFrame2.TextRange.Characters(1, 7).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 7).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
' Diagramm: Größe ändern und positionieren
ActiveSheet.Shapes("Diagramm 1").ScaleWidth 1.5, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Diagramm 1").ScaleHeight 1.75, msoFalse, _
msoScaleFromTopLeft
ActiveChart.Parent.Cut
ActiveSheet.Paste
' Diagramm-Titel
ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
ActiveChart.ChartTitle.Text = "Versuch_002"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Versuch_002"
With Selection.Format.TextFrame2.TextRange.Characters(1, 11).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 11).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
Selection.Format.Fill.Visible = msoFalse
Selection.Format.Line.Visible = msoFalse
Selection.Left = 185
Selection.Top = 18
Range("U30").Select
End Sub
|