Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1120to1124
1120to1124
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VBA-Problem mit "Daten - Text in Spalten"

Forumthread: VBA-Problem mit "Daten - Text in Spalten"

VBA-Problem mit "Daten - Text in Spalten"
NoNet
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
Anzeige
AW: VBA-Problem mit "Daten - Text in Spalten"
09.12.2009 17:09:30
Hajo_Zi
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.

Guter Ansatz - leider erfolglos - Frage offen...
09.12.2009 17:21:38
NoNet
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
Anzeige
AW: Guter Ansatz - leider erfolglos - Frage offen...
09.12.2009 17:37:39
Hajo_Zi
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

Anzeige
AW: Trimmen
09.12.2009 20:33:42
Gerd
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
Anzeige
@Gerd : Ändert leider nichts am Format :-( _oT
09.12.2009 21:10:46
NoNet
_oT
AW: VBA-Problem mit "Daten - Text in Spalten"
10.12.2009 11:17:32
Christian
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
Anzeige
AW: VBA-Problem mit "Daten - Text in Spalten"
10.12.2009 21:21:01
Gerd
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
Anzeige
Danke nochmals - habe es mit DATWERT gelöst
10.12.2009 22:09:23
NoNet
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
Anzeige
;

Forumthreads zu verwandten Themen

Zelleninhalt (Text) in 2 Spalten teilen
El.

Hallo!
Habe eine Spalte in der Artikelname und Artikelmerkmale, durch drei Punkte getrennt "...", zusammen stehen. Beispiel: Lecker Whisky 21 Jahre ... Lieferung in hochwertiger Schatulle
Wie krieg ich nun den Text links der Punkte in eine extra Spalte und rechts der Punkte in eine extr...
03.09.2008 16:35:00
Thread-ID: 1007155
4 Beiträge
Spaltentrennzeichen nach speichern in Text-Datei
Michael

Hallo
ich habe ein Problem. Vielleicht ist es schwierig, vielleicht aber auch nicht.
Ich habe eine Text-Datei mit Spaltentrennzeichen "|" (besser als ";" der Übersichtlichtlichkeit halber) und verschiedenen Längen der Spalten.
Ich will diese in Excel überarbeiten und als Text-Dat...
11.09.2008 09:28:00
Thread-ID: 1008978
3 Beiträge
Textdatei per VBA einlesen
Werner

Ich möchte eine Textdatei per VBA einlesen. Untenstehender Versuch bringt die Fehlermeldung:
Excel kann auf die Datei in '' nicht zugreifen.
Die Datei ist unter Umständen schreibgeschützt oder verschlüsselt.
Woher weiß das Makro das, wo ich noch gar keinen Dateinamen angegeben habe...
11.09.2008 09:57:26
Thread-ID: 1008986
4 Beiträge
Anzeige
Text in Spalten
Simone

Hallo zusammen,
ich hab ein wohl eher triviales Problem, komme aber trotzdem einfach nicht weiter.
Ich füge in ein Arbeitsblatt Daten aus einem anderen Programm ein. Jetzt habe ich mehrmals die Funktion 'Text in Spalten' verwendet / ausprobiert, und wenn ich jetzt wieder Daten in A1 ein...
16.09.2008 13:54:00
Thread-ID: 1010092
3 Beiträge
@viele meinungen: spaltenangaben in vba
Herbert

hallo,
wie macht ihr denn das: wenn ich in einem vba-code spaltenangaben mache, und in der tabelle dann spalten einfügen oder entfernen muss, muss ich alle spaltenangaben im code manuell ändern. und wehe, ich vergesse das, dann stimmt das ganze makro nicht mehr.
beispiel:
With Sheet...
08.10.2008 19:41:00
Thread-ID: 1015156
6 Beiträge
Text in Spalten
Tom

Hallo,
ich möchte mit der Funktion Text in Spalten erreichen, dass alle Zeilen, die mit einer Zahl beginnen, nach der Zahl in eine neue Spalte geschoben werden - nur finde ich hierfür nur die Funktionen nach Leerzeichen, Komma usw...
Wer hat ne Lösung?
In A1: 1020 Maier Huber Mülle...
20.10.2008 14:09:42
Thread-ID: 1017654
7 Beiträge
Text autom. in Excel-Spalten einsortieren
Annalena49

Hallo,
habe eine (für mich) ungewöhnliche Datendatei, ähnlich CSV ist aber keine CSV. Hier ein Beispiel einer Excel-Zeile:
10153-8158 10194 1023007 1025250000 102950600 1030LP 1032EUR 1029880000 1032EUA 11010100001 1102UM 110105586724 1102KN 11019783815860847 1102EN 11013815860849 110...
05.12.2008 20:54:02
Thread-ID: 1030586
3 Beiträge
Anzeige
Linien, wenn Text in Zeile - VBA
Wolfgang

Hallo,
in einer Arbeitsmappe hatte ich bislang bis ca. Zeile 6000 für die Spalten A-Q das Format für die Linien direkt über Zellenformat vorgegeben. Das macht die Datei wohl doch relativ fett.- Gäbe es eine Möglichkeit, evtl. über VBA zu steuern, dass ausschließlich die Zeilen, in denen au...
07.12.2008 09:58:34
Thread-ID: 1030803
2 Beiträge
Spalten in VBA anpassen
Markus

Hallo zusammen,
eine vermutlich blöde Frage.
Ich lese aus einem Anwendungsfenster Daten in bestimmte Zellen aus. Wenn alle Daten (je nach Anwendung bis zu 30 Spalteneinträge) ausgelesen sind, erfasse ich die Zeit mit ActiveCell.Offset(0, 10).Value = Format(Date & " " & Time)
10.12.2008 20:45:20
Thread-ID: 1031825
9 Beiträge
Anzeige
Filter - Text in Spalten
LD

Hallo,
habe hier eine Adressliste aus welcher ich nur die email adr. rausziehen will.
Habe schon einen Filter angewendet um alle Zeilen mit @ anzuzeigen, allerdings
steht vor den adr. "email an" und ich bekomme das nicht weg. Wenn ich Text
in Spalten mache, sind alle anderen Zei...
13.03.2009 13:50:22
Thread-ID: 1059860
6 Beiträge
Text in Spalten Makro
Steffen

Guten Morgen!
folgendes problem: in Spalte E stehen datumswerte die aus einer anderen datei kopiert wurden,diese datumswerte werden mit einer bedingten formatierung belegt .da diese nach dem kopieren nicht als datumswert in der spalte stehn ,hab ich ein text in spalten makro aufgezeichnet s...
31.03.2009 12:08:50
Thread-ID: 1065568
3 Beiträge
Text in Spalten
Dieter

Hallo zusammen,
ich habe mit dem Recorder ein Makro aufgezeichnet,
um Daten mit der Funktion "Text in Spalten zu trennen.
Das Makro sieht so aus....
Sub DatenSpalten() Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, Con...
11.04.2009 14:30:27
Thread-ID: 1068301
6 Beiträge
Anzeige

Infobox / Tutorial

VBA-Anpassungen für "Daten - Text in Spalten" in Excel

Schritt-für-Schritt-Anleitung

  1. Öffne Excel und die entsprechende Arbeitsmappe.

  2. Drücke ALT + F11, um den VBA-Editor zu öffnen.

  3. Erstelle ein neues Modul:

    • Klicke im Menü auf Einfügen > Modul.

  4. Füge den folgenden Code ein, um die Funktion "Text in Spalten" zu implementieren:

    Sub DatumSpalte()
    Columns("E:E").TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        Comma:=True, FieldInfo:=Array(1, 4)
End Sub

  5. Füge bei Bedarf eine Formatierung hinzu, um sicherzustellen, dass die Datumsangaben korrekt angezeigt werden.

  6. Führe das Makro aus, um die Daten zu konvertieren und das Format anzupassen.

Häufige Fehler und Lösungen

  • Problem: Text in Spalten funktioniert nicht

    • Lösung: Überprüfe die Zellenformatierung. Die Zellen sollten als Datum formatiert sein.

  • Problem: Spalte bleibt linksbündig

    • Lösung: Nutze das CDate-Funktion oder multipliziere mit 1 in einer Hilfsspalte, um die Formatierung zu erzwingen.

  • Problem: excel text in spalten deaktivieren

    • Lösung: Stelle sicher, dass keine Filter oder Datenvalidierungen aktiv sind, die das Einfügen von Werten verhindern.

Alternative Methoden

  • Hilfsspalte verwenden: Füge eine Hilfsspalte hinzu und verwende eine Formel wie =A2*1, um die Daten zu konvertieren, und kopiere dann die Werte zurück in die originale Spalte.

  • Direktes Einlesen von Textdateien: Anstatt die Funktion Text in Spalten zu verwenden, kannst du eine Textdatei zeilenweise einlesen und die Daten direkt in die Zellen schreiben:

    Sub GetData()
    Dim intFile As Integer, k As Long
    Dim strLine 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
        If IsDate(strLine) Then
            Sheets(1).Cells(k, 1) = CDate(strLine)
        Else
            Sheets(1).Cells(k, 1) = strLine
        End If
        k = k + 1
    Loop
    Close #intFile
End Sub

Praktische Beispiele

  • Beispiel für Text in Spalten:

    Sub BeispielTextInSpalten()
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        Comma:=True, FieldInfo:=Array(1, 4)
End Sub

  • Beispiel für DATWERT-Funktion:

    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

Tipps für Profis

  • Verwende den excel match destination formatting shortcut: Dies kann hilfreich sein, um sicherzustellen, dass die Formatierung der Quell- und Zielzellen übereinstimmt.

  • Teste deine Makros mit verschiedenen Datentypen, um sicherzustellen, dass sie robust sind und auch unerwartete Eingaben verarbeiten können.

  • Wenn du vba is text verwendest, zögere nicht, die IsDate-Funktion zu nutzen, um sicherzustellen, dass die Eingaben korrekt behandelt werden.

FAQ: Häufige Fragen

1. Warum funktioniert Text in Spalten nicht in meinem Fall? Vergewissere dich, dass die Zellen als Datum formatiert sind und dass du keine Leerzeichen oder unerwartete Zeichen in den Zellen hast.

2. Wie kann ich die Formatierung nach Text in Spalten ändern? Du kannst die Zellen manuell formatieren oder im VBA-Code nach dem Ausführen von TextToColumns das Format der Zellen anpassen.

3. Gibt es eine Möglichkeit, Text in Spalten mit VBA zu deaktivieren? Ja, du kannst sicherstellen, dass der Makro-Code keine unerwünschten Formatierungen anwendet, indem du die entsprechenden Optionen im Code anpasst.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige