Microsoft Excel

Herbers Excel/VBA-Archiv

VBA-Problem mit "Daten - Text in Spalten" | Herbers Excel-Forum


Betrifft: VBA-Problem mit "Daten - Text in Spalten" von: NoNet
Geschrieben am: 09.12.2009 17:05:31

Hallo Leute,

ich "kämpfe" gerade mit einem etwas doofen Problem :
Aus einem Sharepoint-Server ziehe ich eine Datei (im Lotus WK3-Format) auf meinen PC und möchte diese umformen. Eine der Spalten enthält Datumsangaben (genauer: Datum+Uhrzeit), mit der man auch operieren (rechnen) kann. Soweit - sogut ! Allerdings werden diese Daten in der Zelle linksbündig angezeigt, obwohl die Spalte als TT.MM.JJJJ hh:mm:ss formatiert ist und und obwohl die horizontale Ausrichtung auf STANDARD steht.

Editiere ich nun eine einzelne Zelle per F2-Taste und bestätige per ENTER, stimmt die Ausrichtung (rechtsbündig). Das kann ich auch für die gesamte Spalte per "Daten - Text in Spalten - Getrennt - Fertigstellen" erreichen.

Doch leider klappt das nicht mehr, wenn ich das per VBA ausführe : Columns("A:A").TextToColumns....
Selbst der zuvor aufgezeichnete Code kann die Spalte nun nicht mehr anpassen.

Was ist da los ? - Wie kann ich den Code abändern, so dass die Spalte auch per VBA umformatiert werden kann ?

Hier der aufgezeichnete Code :

Sub DatumSpalte()
'
' DatumSpalte Makro
' Makro am 09.12.2009 von NoNet aufgezeichnet
'

'
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
End Sub
Hier mein "optimierter" Code :
Sub DatumSpalteE()
    Columns("E:E").TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
         Comma:=True, FieldInfo:=Array(1, 4)
End Sub
Hier noch eine Beispiel-Datei (Datumsangaben in Spalte A anstatt Spalte E !) :

https://www.herber.de/bbs/user/66462.xls

Ich hoffe, jemand kann mir helfen...

Vielen Dank vorab, Gruß NoNet

  

Betrifft: AW: VBA-Problem mit "Daten - Text in Spalten" von: Hajo_Zi
Geschrieben am: 09.12.2009 17:09:30

Hallo NoNet,

Karin hatte gestern auch das Problem. Sie hat sich dann damit beholfen Eine Spalte eingefügt mit Formel =Zelle*1 Und dann die Werte kopiert und die Spalte wieder gelöscht.

GrußformelHomepage


  

Betrifft: Guter Ansatz - leider erfolglos - Frage offen... von: NoNet
Geschrieben am: 09.12.2009 17:21:38

Hallo Hajo,

das wäre ja ein "Notnagel" - wenn es denn funktionieren würde :
In meiner Datei (s.o.) funktioniert das leider nicht :

Sub DatumszellenMit1Multiplizieren()
    [C1] = 1
    [C1].Copy
    [A2:A16].PasteSpecial Operation:=xlPasteSpecialOperationMultiply
    [C1].ClearContents
    Application.CutCopyMode = False
End Sub
Gruß, NoNet


  

Betrifft: AW: Guter Ansatz - leider erfolglos - Frage offen... von: Hajo_Zi
Geschrieben am: 09.12.2009 17:37:39

Hallo NoNet,

nicht die Multiplikation mit 1 als VBA sondern als Formel in einer Hilfsspalte.

    Columns(3).Insert shift:=xlRight
    With Range(Cells(3, 3), Cells(UBound(arrText()) + 3, 3))
'       Formel in Spalte C eintragen
        .Formula = "=B3*1"
'       Datums-Format für Spalte C
        .NumberFormat = "dd/mm/yyyy hh:mm:ss"
'       Bereich kopieren
        .Copy
    End With
'   Werte einfügen
    Cells(3, 3).PasteSpecial Paste:=xlPasteValues
'   Spalte B löschen
    Columns(2).Delete shift:=xlLeft
GrußformelHomepage


  

Betrifft: AW: Trimmen von: Gerd L
Geschrieben am: 09.12.2009 20:33:42

Hallo NoNet,

wenn deine Datei den Originalzustand "vorher" hat, ist dies die Meinung meines Makrorekorders.

Sub Makro2()

    ActiveCell.Range("A1:A16").Select
    Selection.Replace What:=" ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub
Gruß Gerd


  

Betrifft: @Gerd : Ändert leider nichts am Format :-( _oT von: NoNet
Geschrieben am: 09.12.2009 21:10:46

_oT


  

Betrifft: AW: VBA-Problem mit "Daten - Text in Spalten" von: Christian
Geschrieben am: 10.12.2009 11:17:32

Hi NoNet,
da bin ich auch schon mal drüber gestolpert.
Mein workaround:
- das Text-file zeilenweise in String einlesen.
- String ggf. splitten.
- das Datum als Date-Typ in Tabelle schreiben.

Varianten gibts hier mehrere, ein Bsp. für die erste Spalte:

Option Explicit

Sub GetData()
    Dim intFile As Integer, k As Long
    Dim strLine As String, strFirst As String
        
    Const SRCFILE As String = "D:\test.wk3"
    Sheets(1).Cells.Clear
    intFile = FreeFile
    
    Open SRCFILE For Input As #intFile
        Do While Not EOF(intFile)
            Line Input #intFile, strLine
            strFirst = Split(strLine, ",")(0)
            k = k + 1
            If IsDate(strFirst) Then
                Sheets(1).Cells(k, 1) = CDate(strFirst)
            Else
                Sheets(1).Cells(k, 1) = strFirst
            End If
        Loop
    Close #intFile
End Sub
Gruß
Christian


  

Betrifft: AW: VBA-Problem mit "Daten - Text in Spalten" von: Gerd L
Geschrieben am: 10.12.2009 21:21:01

HalloNoNet,

auch für eine Nachbearbeitung ist CDate der bessere Ansatz.

Sub Nach_Rechts()
Dim arr(), intIndex As Integer

With Range("A2:A16")
arr = .Value
For intIndex = LBound(arr, 1) To UBound(arr, 1)
arr(intIndex, 1) = CDate(arr(intIndex, 1))
Next
.Clear
.NumberFormat = "dd.mm.yyyy hh:mm:ss"
.Cells = arr
End With

End Sub
Gruß Gerd


  

Betrifft: Danke nochmals - habe es mit DATWERT gelöst von: NoNet
Geschrieben am: 10.12.2009 22:09:23

Hallo Gerd, hallo Hajo,

ich habe es jetzt gelöst, indem ich in einer Hilfsspalte das Datum neu berechne :

Sub DatumKonvertieren()
    Dim lngLZ As Long
    lngLZ = Cells(Rows.Count, 1).End(xlUp).Row
    
    With Range("X2:X" & lngLZ)
        .Formula = "=DATEVALUE(A2)+TIMEVALUE(A2)"
        Range("A2:A" & lngLZ).Value = .Value
        .ClearContents
    End With
End Sub
Wie beschrieben : Die Multiplikation mit *1 konnte das Problem auch nicht lösen !

Danke nochmals, Gruß NoNet