Office: (Office 2013) Formatierung mit Macros

Hallo, ich hab mal wieder eine Herausforderung und da ich blutiger Anfänger bin was Macros angeht benötige ich eure Hilfe.

Ich bekomm jeden Donnerstag eine xml als Export aus einem Programm. Diese geht bin Spalte BV.

In einigen Spalten ist ein Datum, was aber nicht als solches erkannt wird.

Es steht zum beispiel 02.03.2013 dort. Die Zelle wird als Text erkannt.

Kann ein Macro die Formatierung ändern auf "Datum kurz"?

Bisher muss ich immer eine Spalte anwählen, format auf Datum kurz um dann im Bereich Daten auf "text in Spalten" . Das ist sehr müseelig.

 

Hallo Spitzbart,

du könntest es ja mal so in der Art probieren:

Code:

Option Explicit

Public Sub test()

Dim strZellwert As String
Dim objZelle As Object
Dim objBereich As Object

    With Me         ' Verweis auf das Arbeitsblatt wenn im Modul des Arbeitsblattes
                    ' eventuell auf "ActiveWorksheet" oder konkreten Blattnamen ändern
        Set objBereich = Intersect(.UsedRange, .Range("A:A"))
        For Each objZelle In objBereich
            strZellwert = objZelle
            objZelle.NumberFormat = "m/d/yyyy"
            If IsDate(strZellwert) Then objZelle = CDate(strZellwert)
        Next objZelle
    End With
    
End Sub

Was meinst Du eigentlich mit "Datum kurz" ?

 

Unter Start kann das Zellformat festgelegt werden.

Oh man, sieht kompliziert aus, kannst du das mal beispielhaft fü die Zellen A1-A10 machen?

 

Hallo Spitzbart,

entweder verstehe ich Dich nicht, oder Du mich nicht.
Ich kenne verschieden Datumsformate, aber den Begriff "Datum kurz" kenne ich nicht. Deshalb wäre es hilfreich, wenn Du mir mal ein datum in diesem Format aufschreibst, damit ich sehe was ich mir darunter vorstellen soll.

Keine Sorge, da täuscht gewaltig. ;O)
in Deinem Eingangspost hast Du geschrieben: "In einigen Spalten ist ein Datum, was aber nicht als solches erkannt wird."
Also habe ich mir eine Lösung überlegt, die man spaltenweise arbeiten lassen kann.

Code:

Option Explicit

Public Sub test()

Dim strZellwert As String
Dim objZelle As Object
Dim objBereich As Object

Bis hierher ist eigentlich alles nur "Einleitung", quasi ein "Vorwort".
"Option Explicit" sorgt dafür, dass Variablen definiert werden müssen, bevor der Code das erste Mal läuft. Ansonsten bekommt man sofort einen Laufzeitfehler. Das hilft ungemein Programmierfehler zu vermeiden, und sollte in den Optionen immer eingeschaltet sein.

"Public Sub test()" ist einfach nur der Name der Prozedur (des Makros).

"Dim strZellwert As String Dim objZelle As Object Dim objBereich As Object" Hier werden drei Variablen initiert und einem Datentyp zugeordnet. Dabei bedeutet "String", dass die Variable Daten im Textformat aufnimmt, und "Object" dass die Variable ein Object repräsentiert.

"With Me" Verweist auf das Arblatt, in dessen Modul der Code steht. Je nachdem wo Dein Code später steht, oder wie Dein Makro am Ende aufgebaut ist, muss man diesen Verweis halt noch anpassen. In Deinem Code must Du Excel halt auch immer genau sagen worauf Du Dich beziehst, quasi Stadt, Strasse, Hausnummer und Etage angeben.

"Set objBereich = Intersect(.UsedRange, .Range("A:A"))" Hier wird der Variablen objBereich ein Zellbereich zugewiesen. (Durch die Referenzierung eine Zeile höher ist auch klar, in welchem Blatt.) "UsedRange" beschreibt den kompletten Bereich aller benutzten Zellen, "Range("A:A")" beschreibt die komplette Spalte A, und "Intersect" bildet die Schnittmenge aus beiden Bereichen, allso alle benutzten Zellen in Spalte A. genau hier müsste also an Deine tabelle angepasst werden, aber da ich nicht weiß in welchen Spalten Deine falsch formatierten Daten stehen, kann ich da momentan nichts machen. Vor allem weiß ich auch nicht, ob die Spalten immer gleich sind, oder ob die sich ändern.

"For Each objZelle In objBereich - Next objZelle" Dies ist die Kopf- und Fusszeile einer Schleife. Sie arbeitet nach dem Prinziep: "Für jede Zelle im definierten Bereich... - nächste Zelle"

"strZellwert = objZelle" hier wird der Wert der aktuellen Zelle an eine Variable übergeben.

"objZelle.NumberFormat = "m/d/yyyy"" hier wird die aktuelle Zelle im normalen Datumsformat formatiert, also = 28.05.2015. Im Makro wird zwar die englische Schreibweise benutzt, aber durch die lokalen Einstellungen auf Deinem Rechner wird das deutsche/europäische Datumsformat angezeigt.

"If IsDate(strZellwert) Then" hier wird geprüft, ob der Zellwert überhaupt ein Datum ist, damit die nächste Zeile keinen Fehler verursacht. (die Zelle könnte leer sein, oder es steht "Hallo" drinn...). Die Prüfung ist eine einfache "Wenn - Dann" Prüfung.

"objZelle = CDate(strZellwert)" hier wird der gespeicherte Wert der Textvariablen wieder in die aktuelle Zelle zurückgeschrieben. Diesmal aber durch das "CDate" definitiv im Datums- und nicht im Textformat.

Noch Fragen?
Spiel doch einfach mal ein bischen damit rum.
Und wenn Du eine Beispieldatei mit Dummydaten hier im Forum hochlädst, kann ich das Makro auch auf die entsprechenden Spalten einstellen.

 

Ok ich werde das mal ausprobieren. Als Datumsformat soll dd/mm/yyyy verwendet werden. Wenn ich es nicht hin bekomme erstell ich morgen ne Dummidatei.

Bis dahin erstmal vielen Dank für deine Unterstützung. Ich melde mich. Egal ob es geklappt hat oder nicht.

 

So ich hab mal getestet. Habe Active.worksheet genommen.

Bei .usedRange kommt dann die Meldung "Unzulässiger oder nicht ausreichend definierter Bereich" Range hab ich auf A:K gesetzt

 

Hallo Spitzbart,

sry, mein Fehler. Es muss "ActiveSheet" heißen. (vielleicht lerne ich das irgendwann mal, den Fehler mache ich dauernd)
Habs damit mal kurz getestet, klappt bei mir mit mehreren Spalten.
Wenns bei Dir nicht klappt, bitte mal Deinen Code posten, am besten allerdings direkt eine Beispieldatei hochladen.

 

Ich probier es morgen. Zur not erstell ich ne Dummydatei mit einem Tatensatz.