Clever-Excel-Forum

Guten Tag,

ich habe folgendes Problem:
ich habe eine CSV Datei, welche aus einer SPS ausgelesen wird. Diese wandle ich in eine Excel Tabelle mittels "Text zu Spalten" um.
Hier werden unter anderem Exponezialzahlen der Form "2.680000E+2" in eine normale Zahl der Form "268.00" umgewandelt.

Bei der Umwandlung der Tabelle in eine CSV Datei werden die Exponenzialzahlen aus irgendeinem Grund nicht übernommen. Also wenn ich die CSV Datei mit Editor öffne steht darin "268.00" anstelle von "2.680000E+2", obwohl in der Excel Tabelle als Format "Exponenzialzahl / Wissenschaft" ausgewählt ist.

Für die Anpassung in das Ursprüngliche Format verwende ich folgenden Code (über "Makro aufzeichnen" aufgenommen):

Code:
Sub Spaltenanpassung()

'

' Spaltenanpassung Makro

'

'

    Range("A1:S1").Select

    Selection.Font.Bold = False

    With Selection

        .HorizontalAlignment = xlGeneral

        .VerticalAlignment = xlCenter

        .WrapText = True

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = False

    End With

    With Selection

        .HorizontalAlignment = xlGeneral

        .VerticalAlignment = xlBottom

        .WrapText = True

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = False

    End With

    With Selection

        .HorizontalAlignment = xlGeneral

        .VerticalAlignment = xlBottom

        .WrapText = False

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = False

    End With

    Range("B1").Select

    ActiveCell.FormulaR1C1 = "Breite / Durchmesser"

    Range("C1").Select

    ActiveCell.FormulaR1C1 = "Hoehe / Durchmesser"

    Range("D1").Select

    ActiveCell.FormulaR1C1 = "Dicke"

    Range("E1").Select

    ActiveCell.FormulaR1C1 = "Drehpunkt X"

    Range("F1").Select

    ActiveCell.FormulaR1C1 = "Drehpunkt Y"

    Range("G1").Select

    ActiveCell.FormulaR1C1 = "Einlage des Rasters nach Laser"

    Range("H1").Select

    ActiveCell.FormulaR1C1 = "Beginn Strich"

    Range("I1").Select

    ActiveCell.FormulaR1C1 = "Beginn Lable"

    Range("J1").Select

    ActiveCell.FormulaR1C1 = "Labellaenge"

    Range("K1").Select

    ActiveCell.FormulaR1C1 = "Mittenversatz Soll +"

    Range("L1").Select

    ActiveCell.FormulaR1C1 = "Mittenversatz Soll -"

    Range("M1").Select

    ActiveCell.FormulaR1C1 = "Barcode"

    Range("N1").Select

    ActiveCell.FormulaR1C1 = "Gemessene Mitte"

    Range("O1").Select

    ActiveCell.FormulaR1C1 = "Mittenversatz Ist"

    Range("P1").Select

    ActiveCell.FormulaR1C1 = "Pos"

    Range("Q1").Select

    ActiveCell.FormulaR1C1 = "Ausrichtung"

    Range("R1").Select

    ActiveWindow.ScrollColumn = 2

    ActiveWindow.ScrollColumn = 3

    Range("R1").Select

    ActiveCell.FormulaR1C1 = "Strich laenge"

    Range("R1").Select

    ActiveCell.FormulaR1C1 = "Strich Laenge"

    Range("S1").Select

    ActiveCell.FormulaR1C1 = "Datum / Uhrzeit Messung"

    Range("F2:K81").Select

    Selection.NumberFormat = "0.00E+00"

    Selection.NumberFormat = "0.000E+00"

    Selection.NumberFormat = "0.0000E+00"

    Selection.NumberFormat = "0.00000E+00"

    Selection.NumberFormat = "0.000000E+00"

    Range("F10").Select

    ActiveWindow.ScrollColumn = 2

    ActiveWindow.ScrollColumn = 3

    ActiveWindow.ScrollColumn = 4

    ActiveWindow.ScrollColumn = 5

    Range("N2:S81").Select

    Selection.NumberFormat = "0.000E+00"

    Selection.NumberFormat = "0.0000E+00"

    Selection.NumberFormat = "0.00000E+00"

    Selection.NumberFormat = "0.000000E+00"

    Range("O9").Select

    ActiveWindow.Zoom = 100

    ActiveWindow.Zoom = 115

    ActiveWindow.Zoom = 130

    ActiveWindow.Zoom = 115

    ActiveWindow.Zoom = 100

    ActiveWindow.Zoom = 85

    ActiveWindow.Zoom = 70

    ActiveWindow.ScrollColumn = 2

    ActiveWindow.ScrollColumn = 1

    ActiveWindow.ScrollColumn = 2

    ActiveWindow.ScrollColumn = 3

    ActiveWindow.ScrollColumn = 2

    ActiveWindow.ScrollColumn = 1

    ActiveWindow.Zoom = 85

    ActiveWindow.SmallScroll Down:=-15

    ActiveWindow.ScrollColumn = 2

    ActiveWindow.ScrollColumn = 1

End Sub

Für die Umwandlung von der Excel Tabelle zur CSV Datei verwende ich folgenden im Internet gefundenen Quellcode:

Code:
Sub Export()

    Dim xRg As Range

    Dim xRow As Range

    Dim xCell As Range

    Dim xStr As String

    Dim xTxt As String

    Dim xName As Variant

    On Error Resume Next

    If ActiveWindow.RangeSelection.Count > 1 Then

      xTxt = ActiveWindow.RangeSelection.AddressLocal

    Else

      xTxt = ActiveSheet.UsedRange.AddressLocal

    End If

    Set xRg = Application.InputBox("Please select data range:", "Kutools for Excel", xTxt, , , , , 8)

    If xRg Is Nothing Then Exit Sub

    xName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")

    Open xName For Output As #1

    For Each xRow In xRg.Rows

        xStr = ""

        For Each xCell In xRow.Cells

            xStr = xStr & xCell.Value & Chr(44) & Chr(9)    'setze erst Komma und dann Tabstop

        Next

        While Right(xStr, 1) = Chr(9)

            xStr = Left(xStr, Len(xStr) - 1)

        Wend

        Print #1, xStr

    Next

    Close #1

    If Err = 0 Then MsgBox "The file has saved to: " & xName, vbInformation, "Kutools for Excel"

End Sub

Über Hilfe würde ich mich sehr freuen!

Moin

Importiere die csv mit Power Query.
Dabei lassen sich auch die korrekten Datentypen zuweisen.

danke für deine Lösung, das hat super funktioniert!

sa1bot

shift-del

sa1bot