XML automaitsch impotieren und formatieren

[MrWoopy]

Liebe Excel-Freunde,

ich bin nicht sehr in VBA-Code/ Makros bewandert. Ich gehe jedoch stark davon aus, dass diese nötig sein werden.

Folgender Sachverhalt:
Ich bekomme regelmäßig XML-Dateien. Diese sind auch stets gleich aufgebaut und müssen in Excel überführt werden. Dabei wähle ich immer das gleiche Inhaltselement aus, welches aus 2 verschiedenen Datensätzen/ Tabellen besteht. Aus diesen Tabellen werden immer die gleichen Spalten (pro XML-Datei) gewählt, da nicht alle Spalten benötigt werden.
In einer Spalte sind Zahlen, jedoch im Punkt-Format. D. h. die Punkte werden durch Kommata ersetzt. Anschließend müssen die Zahlen in ein bestimmtes Zahlenformat formatiert und am Ende der Spalte die Summe ermittelt werden.
Abschließend soll die Summe und eine die letzte Zeile mit Inhalt einer anderen Spalte noch gelb unterlegt werden.


Dieser Vorgang wird bisher per Hand gemacht. Da es aber oft auch mal 20 oder mehr XML-Dateien sind, die jeweils einzeln in eine Exceldatei überführt werden müssen, dachte ich mir, dass sich dieser stupide und langsame Vorgang doch sicher vereinfachen bzw. (teil-)automatisieren lässt.

Ich würde mich freuen, wenn Ihr mir helfen könntet, mit meinen doch eher laienhaften Kenntnissen, den Prozess zu optimieren.

Ich bin für jeden Tipp/ jede Anleitung sehr dankbar.

Gruß

[schauan]

Hallöchen,

wie holst Du die Daten denn jetzt rein, mit PowerQuery? Mit PQ kann man auch mehrere Dateien zusammenfassen, geht sicher auch mit xml's?

[Rumo0815]

   

[Oberschlumpf]

hier hat er auch schon gefragt...
https://www.herber.de/forum/messages/1892596.html
...und noch nicht geantwortet

[MrWoopy]

Hallo,




ja sorry, war leider krank und habe nebenbei noch ein bisschen selbst herumgetüfftelt.

Ich habe jetzt folgendes Makro erstellt:

Code:

Sub XML_laden()
'
' XML_laden Makro
' XML laden und formatieren /JTH
'

'
    ActiveWorkbook.Queries.Add Name:="zahlungsdaten", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Quelle = Xml.Tables(File.Contents(""H:\...\A.xml""))," & Chr(13) & "" & Chr(10) & "    Table2 = Quelle{2}[Table]," & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ"" = Table.TransformColumnTypes(Table2,{{""Attribute:nummer"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Erweiterte antragsdaten"" = Table.ExpandTableColumn(#""Geänderter Typ"", ""antragsdaten"", {""antragsnummer"", ""unternehmen" & _
        "sname"", ""steuernummer"", ""postleitzahl"", ""kontoinhaber"", ""iban"", ""referenzErstantrag""}, {""antragsnummer"", ""unternehmensname"", ""steuernummer"", ""postleitzahl"", ""kontoinhaber"", ""iban"", ""referenzErstantrag""})," & Chr(13) & "" & Chr(10) & "    #""Erweiterte foerderdaten"" = Table.ExpandTableColumn(#""Erweiterte antragsdaten"", ""foerderdaten"", {""antragsdatum"", ""bewilligu" & _
        "ngsdatum"", ""auszahlungsbetrag"", ""waehrung""}, {""antragsdatum"", ""bewilligungsdatum"", ""auszahlungsbetrag"", ""waehrung""})," & Chr(13) & "" & Chr(10) & "    #""Ersetzter Wert"" = Table.ReplaceValue(#""Erweiterte foerderdaten"",""."","","",Replacer.ReplaceText,{""auszahlungsbetrag""})," & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ1"" = Table.TransformColumnTypes(#""Ersetzter Wert"",{{""auszahlungsbetrag"", typ" & _
        "e number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ1"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=zahlungsdaten;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [zahlungsdaten]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "zahlungsdaten"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.ScrollColumn = 2
    Columns("J:J").Select
    Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
    Range("zahlungsdaten[[#Headers],[bewilligungsdatum]]").Select
    Selection.Copy
    Range("zahlungsdaten[[#Headers],[auszahlungsbetrag]]").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("L2").Select
    Selection.End(xlDown).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("J1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Application.CutCopyMode = False
    Intersect(Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).EntireRow, Range("J:J")).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
    Range("J1").Select
    Selection.End(xlDown).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Soweit sogut.




Jetzt ist es so, dass ich den Namen der Datei ja auf "A" setzen muss, damit er sie erkennt. die Dateien heißen aber eigentlich Auszahlung_...


Jetzt dachte ich, ich kann mit einer Wildcard arbeiten. Also Auszahlung*.xml


Er stellt zwar eine Verbindung zur XML her, aber gibt dann einen Fehler. Beim Debuggen verweist er auf Zeile (.Refresh BackgroundQuery:=False). Es ist ein Laufzeitfehler mit der Meldung, illegales Zeichen im Pfad. Ist "*" nicht die Wildcard für eine belieb lange Zeichenfolge?

Wie verhält er sich zudem wenn es mehrere Dateien in dem Format gibt?



Es müssen um die 20 Dateien formatiert werden. Das bisherige Makro ist schon Mal eine sehr gute Hilfe, aber ich denke, da geht noch mehr. Gibt es eine Möglichkeit mit einem Makro alle Dateien zusammen einlesen zu lassen, so dass z.B. jede XML auf einem eigenen Tabellenblatt erscheint?



Besten Gruß

[schauan]

Hallöchen,

Du könntest eine Schleife über alle gewünschten Dateien erstellen. Als Basis könntest Du entweder eine Liste der Dateien anlegen und die Schleife darüber laufen lassen oder Du gehst mit Dir alle Dateien des Verzeichnisses durch und schränkst den weiteren Ablauf innerhalb der Schleife im Prinzip mit If ... like "Auszahlung*" then ... ein