% in US Format Conversion back from Date to %

Moderator: ModerationP

% in US Format Conversion back from Date to %

Beitragvon Udo Weinmann » 23. Feb 2015, 17:47

Hallo,

beim Exportieren von % Daten im US Format in eine Excel Datei mit einem Datumsformat, dass "Punkte" verwendet, werden die % Zahlen "verunstaltet".
Ich habe ein Makro hierfür gecschrieben, dass zusätzliche Spalten benötigt und auf mich sehr kompliziert wirkt. Und wenn sich die Quelldaten ändern, muss ich die Hilfsspaltenberechnung ändern.
Gibt es hierfür eine "schlankere" Version?

Die Aufgabe:

% % from System % Rounding
82.89 82,89 83
17. Nov 17,11 17
31.25 31,25 31
15 15,00 15
100 100,00 100
50 50,00 50
0,00 0
5 5,00 5
Mrz 78 3,78 4
17.78 18,00 18
Mrz 23 3,23 3
Sep 57 9,57 10
Jun 25 6,25 6
Jul 74 7,74 8
Mrz 61 3,61 4
02. Mai 2,50 3
Jan 59 1,59 2

Der Code sieht wie folgt aus:
Code: Alles auswählen
With Sheets("Revenue-Data")
        .Columns("A:A").Copy .Columns("CA:CA") 'CA is the first free column - the original data will be copied into this column. Column BN will be kept all other additional columns will be deleted at the end.
        .Range("CA1:CG1") = Array("Orig % Complete", "Year", "Month", "Day", "Formula", "Dot Repl.&Text to Col.", "Rounding 0")
        .Range("CB2:CB" & EndRow).FormulaR1C1 = "=IF(ISERROR(YEAR(RC[-1])),0,YEAR(RC[-1]))"
        .Range("CC2:CC" & EndRow).FormulaR1C1 = "=MONTH(RC[-2])"
        .Range("CD2:CD" & EndRow).FormulaR1C1 = "=DAY(RC[-3])"
        .Range("CE2:CE" & EndRow).FormulaR1C1 = _
            "=IF(RC80=1900,RC79,IF(RC80=0,RC79,IF(RC80<>YEAR(TODAY()),CONCATENATE(RC81,"","",RIGHT(RC80,2)),IF(AND(RC80=YEAR(TODAY()),RC82>1),CONCATENATE(RC82,"","",RC81),IF(AND(RC80=(YEAR(TODAY())),RC82<=1),CONCATENATE(RC81,"","",RC82-1))))))"
        .Range("CE2:CE" & EndRow).Copy
        .Range("CF2").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        .Range("CF2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        .Range("CF2:CF" & EndRow).Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False
        .Range("CF2:CF" & EndRow).TextToColumns Destination:=Range("CF2"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
                ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
                FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
        .Range("CG2:CG" & EndRow).FormulaR1C1 = "=ROUND(RC[-1],0)" 'Rounding to Zero Digits to avoid to many Probs in the Pivot Tables - LC GAAP Rev for Consulting impacted!
        .Range("CG2:CG" & EndRow).Copy
        .Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        '.Range("A2:A" & EndRow).NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 " '2 Digits
        .Range("A2:A" & EndRow).NumberFormat = "#,##0;[Red]-#,##0" 'Zero Digits
        .Range("CB1:CG" & EndRow).ClearContents 'all columns not required anymore are deleted
        .Columns("CA:CA").EntireColumn.AutoFit
    End With


Jegliche Vereinfachung würde hier sehr helfen.
Gruß

Udo
Udo Weinmann
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 163
Registriert: 10. Nov 2010, 18:22
Wohnort: Darmstadt

Re: % in US Format Conversion back from Date to %

Beitragvon Der Steuerfuzzi » 23. Feb 2015, 17:49

Hallo Udo,

wo kommen die Daten her? Eine Textdatei?
Kannst Du mal eine Testdatei posten und dann die Daten in einer Excel Datei wie sie dann letztednlich aussehen sollen?
Gruß
Michael
Benutzeravatar
Der Steuerfuzzi
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 3730
Registriert: 25. Mär 2013, 13:28

Re: % in US Format Conversion back from Date to %

Beitragvon Flipper7 » 23. Feb 2015, 18:01

Export in Richtung Textdatei und dann die txt wieder einlesen. Warum? Weil du beim Einlesen festlegen kannst was 1000er Trennzeichen und Komma sein soll.

Excel 2007 Daten/Aus Text
Im dritten Schritt des Assistenten kannst du unter Weitere Optionen das festlegen für jede Variable
Kannst du das Problem so austricksen?
Flipper7
Flipper7
 

Re: % in US Format Conversion back from Date to %

Beitragvon Udo Weinmann » 23. Feb 2015, 18:12

Hier der Link zu einer Datei mit Daten.

[url]
https://drive.google.com/file/d/0B-420j ... sp=sharing
[/url]

Spalte A zeigt die Daten so wie sie aus dem Datenbanksystem in eine Excel Datei übertragen werden.
Spalte B zeigt die "Original Daten" in der Datenbank. So müssen die Spalte A Daten auch wieder umgewandelt werden (macht das von mir entwickelte "umständliche" Makro)
Spalte C zeigt die Daten nach "Runden".

Ich benötige ein Makro, da mit manuellen Schritten in der weitaus komplexeren Datei nichts zu machen ist.
Gruß

Udo
Udo Weinmann
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 163
Registriert: 10. Nov 2010, 18:22
Wohnort: Darmstadt

Re: % in US Format Conversion back from Date to %

Beitragvon Der Steuerfuzzi » 23. Feb 2015, 18:38

Erstellt die Datenbank tatsächlich eine Excel-Datei oder z. B. "nur" eine csv-Datei, die Du mit Excel öffnest?
Gruß
Michael
Benutzeravatar
Der Steuerfuzzi
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 3730
Registriert: 25. Mär 2013, 13:28

Re: % in US Format Conversion back from Date to %

Beitragvon Udo Weinmann » 23. Feb 2015, 22:34

eine csv Datei, die mit Excel geöffnet wird.

Die Daten werden dann von den Anwendern in eine Makro Datei kopiert und anschließend per Makro für Analysen vorbereitet.
Gruß

Udo
Udo Weinmann
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 163
Registriert: 10. Nov 2010, 18:22
Wohnort: Darmstadt

Re: % in US Format Conversion back from Date to %

Beitragvon Udo Weinmann » 23. Feb 2015, 22:39

bzw. ein "Tab Delimited Text File", das mit Excel geöffnet wird.
Gruß

Udo
Udo Weinmann
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 163
Registriert: 10. Nov 2010, 18:22
Wohnort: Darmstadt

Re: % in US Format Conversion back from Date to %

Beitragvon Da Steuerfuzzi » 23. Feb 2015, 22:45

Poste doch mal die csv Datei (bevor Du sie in Excel öffnest).
Da Steuerfuzzi
 

Re: % in US Format Conversion back from Date to %

Beitragvon Udo Weinmann » 23. Feb 2015, 23:24

Die Excel öffnet sich automatisch.
Gruß

Udo
Udo Weinmann
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 163
Registriert: 10. Nov 2010, 18:22
Wohnort: Darmstadt

Eigentl ist das ganz einfach - VBA speaks English

Beitragvon Lux:-? » 24. Feb 2015, 01:05

Das kann man auch nachträglich machen, Udo,
wenn die Angaben als von Xl nicht uminterpretierbarer Text vorliegen, also ggf mit TextPräfix (Standard '), und die Form #0.0#% haben. Dazu muss nur eine SubProzedur alle Werte durchgehen und sie dabei evaluieren wie das folgende UDF demonstriert:
Code: Alles auswählen
Function LetFormula(Bezug$)
    LetFormula = Evaluate("=" & Mid(Bezug, 1 - CInt(Bezug Like "=*")))
End Function

Gruß, Lux :-?
Lux:-?
 

Re: % in US Format Conversion back from Date to %

Beitragvon Udo Weinmann » 24. Feb 2015, 11:13

Die Angaben liegen wie folgt vor - wie auch in der Excel Datei in Spalte A eingefügt:

2.5 aus der Datenbank wird in Excel als 2. Mai interpretiert.
9.57 wird zu Sep 57
3.61 wird zu Mrz 61
50 zu 50 (keine Änderung notwendig)
31.25 beleibt 31.25 - da ist nur ein ersetzen des "." durch "," notwendig.

In der Datei und der ersten Aufgaben Beschreibung habe ich weitere Beispiele.

Jetzt muss das Makro zurück "umwandeln".

Ich dachte, es gibt hier evtl. eine elegantere Lösung als die von mir beschriebene.
Gruß

Udo
Udo Weinmann
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 163
Registriert: 10. Nov 2010, 18:22
Wohnort: Darmstadt

Re: % in US Format Conversion back from Date to %

Beitragvon Flipper7 » 24. Feb 2015, 11:46

Hi Udo,
ich glaube wir reden aneinander vorbei. Mein weg könnte theoretisch funktionieren, wenn du jetzt genau acht gibst.
Dein txt öffnet sich bei Doppelklick automatisch mit Excel weil das als Standardprogramm zum öffnen von txt Dateien gerade so hinterlegt sit bei dir.
Du könntest aber auch mit Rechtsklick deine Txt mit dem Editor öffnen. (Öffnen mit, Editor auswählen).

Im Editor kannst du grob sehen, ob die Werte hier noch unverfälscht sind. Meist sind sie es da txts nur reine Werte ohne Formate enthalten.
Mach sie wieder zu.
Wenn du nun ein leeres Excelfile öffnest also ein neues unbeschriebenes, kannst du wie bereits erwähnt auf Daten in der Multifunktionsleiste
drücken. Dann gibt es die Option "Aus Text" zu drücken.

Bitte vorher einmal den Makrorekorder einschalten.

Nun wählst du im Filedialog deine txt aus.
Der Importassistent fragt dich nun Stück für Stück wie du die txt einlesen möchtest. Stell Punkt und Tausendertrennzeichen einmal um.
Fertigstellen.
Ich weiß nix manuelles, zu groß die Datei aber du hast nun ein Makro aufgezeichnet, stoppe die Aufzeichnung und du hast in etwa so einen Code vorliegen
Evtl noch justieren bei Bedarf irgendwo, aber mit etwas Glück genügt dir diese Routine zum einlesen zukünftig ablaufen zu lassen:

Code: Alles auswählen
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\lalala\Test.txt", Destination:=Range("$A$1" _
        ))
        .Name = "Test"
        .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 = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileDecimalSeparator = "."
        .TextFileThousandsSeparator = ","
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub


Flipper7
Flipper7
 

Re: % in US Format Conversion back from Date to %

Beitragvon Udo Weinmann » 24. Feb 2015, 12:34

Hallo Flipper,

vielen Dank für die Beschreibung der Vorgehensweise sofern ich eine Text Datei vorliegen habe.

Es ist jedoch so, dass die Excel automatisch geöffnet wenn die Datenbank die Daten ausliest. Das ist auch gut so, da dann jeder Anwender direkt mit Copy & Paste die Daten einfach in eine Makrodatei kopiert und dann mit einem Knopfdruck die Daten entsprechend manipuliert werden.

Die Datenkonvertierung von Dezimalzahlen kann ich nicht verhindern in diesem Fall - die Daten werden im US Format rausgeschrieben und in der deutschen Excelversion dann eben teilweise in ein Datum umgewandelt.

Die Aufgabe bleibt also bestehen so wie am Anfang beschrieben.

Ich habe ja eine "unelegante" Lösung und dachte, dass es da vielleicht eine schlankere oder bessere gibt.
Gruß

Udo
Udo Weinmann
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 163
Registriert: 10. Nov 2010, 18:22
Wohnort: Darmstadt

Re: % in US Format Conversion back from Date to %

Beitragvon Gast » 24. Feb 2015, 12:42

Hallo Udo,

was passiert?

Vor dem Export unter Datei – Optionen – Erweitert – Bearbeitungsoptionen – Trennzeichen vom Betriebssystem übernehmen:
Häkchen entfernen, Komma gegen Punkt tauschen und Punkt gegen Komma tauschen. Nach Export die Einstellung wieder rückgängig machen.

Jupp
Gast
 

Re: % in US Format Conversion back from Date to %

Beitragvon Udo Weinmann » 24. Feb 2015, 13:18

Ebenfalls eine gute Idee, Jupp.

Nur bei der Anzahl von Anwendern und Daten-Exports am Tag und pro Stunde nicht umsetzbar - leider.
Gruß

Udo
Udo Weinmann
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 163
Registriert: 10. Nov 2010, 18:22
Wohnort: Darmstadt

Nächste

Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: Wisi82 und 24 Gäste

cron