Sub Import_Messdaten()
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
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
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
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
ActiveSheet.Shapes("Diagramm 1").ScaleWidth 1.5, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Diagramm 1").ScaleHeight 1.75, msoFalse, _
msoScaleFromTopLeft
ActiveChart.Parent.Cut
ActiveSheet.Paste
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