Herbers Excel-Forum - das Archiv

Lohnsteuerberechnung (UDF, XLM-Datei, Webabfrage)

Bild

Betrifft: Lohnsteuerberechnung (UDF, XLM-Datei, Webabfrage)
von: Rolf

Geschrieben am: 19.07.2008 15:46:35

Hallo,
bisher habe ich für meine Excel-Tabellen die Steuerberechnung in VBA nach dem Programmablaufplan des BMF (https://www.abgabenrechner.de/pruefdaten/pap2008.pdf) programmiert.
Seit einigen Jahren bietet der BMF die auf folgende Seite beschriebene Möglichkeit an:
https://www.abgabenrechner.de/interface/
Die Abfrage kann ich zwar aus Excel heraus als Hyperlink starten und mir wird auch die XML-Seite angezeigt. Ich möchte nun aber nicht die XML-Seite angezeigt bekommen, sondern die Ergebnisse (aufbereitet) in eine Excel-Tabelle einfügen.
Kann und würde mir einer von Euch weiterhelfen? Für jede Idee wäre ich dankbar.
Gruß
Rolf

Bild

Betrifft: AW: Lohnsteuerberechnung (UDF, XLM-Datei, Webabfrage)
von: ChristianM

Geschrieben am: 20.07.2008 09:55:04
Hallo Rolf,
hier ein Ansatz für Excel Versionen ab XL2000. Weitere Aufbereitung der Array-Werte von vRes funktioniert dann nach dem gleichen Prinzip.
Grüße
Christian

Option Explicit
Sub GetXmlData()
Dim sUrl$, sTxt$, vTxt, vRes, i&, k&
Dim oIE As Object
sUrl = "https://www.abgabenrechner.de/interface/2008.jsp?LZZ=1&RE4=2500000&STKL=1"
k = 2
Set oIE = CreateObject("InternetExplorer.Application")
oIE.navigate sUrl
Do: Loop Until oIE.Busy = False
sTxt = oIE.document.documentElement.outerTEXT
Set oIE = Nothing
With Sheets("Tabelle1")
.Cells.Clear
vTxt = Split(sTxt, vbCrLf)
For i = 0 To UBound(vTxt)
If InStr(vTxt(i), "name=") > 0 Then
vRes = Trim(Replace(Replace(vTxt(i), "<", ""), "/>", ""))
vRes = Split(vRes)
.Range(.Cells(k, 1), .Cells(k, UBound(vRes) + 1)) = vRes
k = k + 1
End If
Next
End With
End Sub


Bild

Betrifft: AW: Lohnsteuerberechnung (UDF, XLM-Datei, Webabfrage)
von: Rolf

Geschrieben am: 20.07.2008 13:13:27
Hallo Christian,
vielen Dank für Deine Anwort. Das hilft mir schon sehr weiter.
Kannst Du die Zeilen
Do: Loop Until oIE.Busy = False
sTxt = oIE.document.documentElement.outerTEXT
noch etwas erläutern?
Was bedeutet "oIE.Busy" und ".outerTEXT"
Was wird in der Schleife ausgeführt (ich bin etwas irretiert)?
Gruß
Rolf

Bild

Betrifft: AW: Lohnsteuerberechnung (UDF, XLM-Datei, Webabfrage)
von: ChristianM

Geschrieben am: 20.07.2008 14:02:30
Hallo,
mit: Do: Loop Until oIE.Busy = False
wird solange gewartet, bis der Browser die Seite angefragt hat. Zur Sicherheit könntest du diese Zeile zweimal unter einander schreiben.
outerTEXT gibt die den HTML-Text der angefragte Seite zurück.
Gruß
Christian

Bild

Betrifft: AW: Lohnsteuerberechnung (UDF, XLM-Datei, Webabfra
von: Kurt

Geschrieben am: 20.07.2008 14:56:53
Hallo Rolf,
Hallo Christian,
hab Euch heimlich belauscht ;-). Finde sowohl Idee als auch Lösung sehr interessant. Hab jetzt aber noch ein Problem.
Wie bekomme ich die Variablen für LZZ, RE4, STKL, ZKF und R automatisch aus meinen Zellen meiner Excel-Tabelle in die Url-adresse für die Web-Abfrage eingetragen?
Wäre geil so was. Kann jemand helfen?
Viele Grüße aus der Hauptstadt

Bild

Betrifft: AW: Lohnsteuerberechnung (UDF, XLM-Datei, Webabfra
von: ChristianM

Geschrieben am: 20.07.2008 16:45:47
hallo Kurt,
Den String zusammensetzen wie zB.

Option Explicit
Sub GetXmlData()
Dim sUrl$, sTxt$, vTxt, vRes, i&, j&, k&
Dim oIE As Object
Dim lngLZZ&, lngSTKL&, lngRE&
With Sheets("Tabelle1")
k = 4
lngLZZ = CLng(.Cells(2, 1))
lngRE = CLng(.Cells(2, 2))
lngSTKL = CLng(.Cells(2, 3))
sUrl = "https://www.abgabenrechner.de/interface/2008.jsp?LZZ=" & _
lngLZZ & "&RE4=" & lngRE & "&STKL=" & lngSTKL
.Rows(k & ":" & .Rows.Count).ClearContents
' usw.

hier am Bsp von LZZ, RE4 und STKL.
KF und R seh ich bei benannter URL nicht
Gruß
Christian

Bild

Betrifft: AW: Lohnsteuerberechnung (UDF, XLM-Datei, Webabfra
von: Kurt
Geschrieben am: 21.07.2008 07:33:51
Hallo Christian,
hab es zwar noch nicht 100%ig hin bekommen. Hatte aber auch bisher wenig Zeit. Danke für deine Hilfe.
Ach so, ZKF ist der Kinderfreibetrag und R die Religionszugehörigkeit
Viele Grüße aus der Hauptstadt
Kurt

Bild

Betrifft: AW: Lohnsteuerberechnung (UDF, XLM-Datei, Webabfra
von: Rolf

Geschrieben am: 21.07.2008 20:17:10
Hallo Kurt,
meine erste Version:
Option Explicit
'Eingangsparameter
Dim AJAHR, ALTER1, JFREIB, JHINZU, JRE4, JVBEZ, KRV, LZZ, LZZHINZU, LZZFREIB, _
R, RE4, SONSTB, STERBE, STKL, VBEZ, VBEZM, VBEZS, VBS, VJAHR, VKAPA, VMT, _
ZKF, ZMVB
'Ausgangsparameter
Dim BK, BKS, BKV, LSTLZZ, _
SOLZLZZ, SOLZS, SOLZV, STS, STV
Dim sUrlAlt As String

Public Function xlLSTJAHR_BMF(GiltAb, UE_64, _
E_AJAHR, _
RV_FREI, _
E_STKL, _
E_ZKF, _
E_ZMVB, _
E_VJAHR, _
E_R, _
E_LZZ, _
E_JFREIB, _
E_JHINZU, _
E_LZZFREIB, _
E_LZZHINZU, _
BRUTTO, _
BRUTTO_VB, _
VB_Jan, _
E_VBEZS, _
SB, _
E_STERBE, _
SB_VB, _
E_VMT, _
E_VKAPA, _
VJAL, _
VJAL_VB, _
Auswahl)
If Len(GiltAb) <= 4 Then
GiltAb = "01.01." & GiltAb
End If
Select Case UE_64
Case Is = "Nein"
UE_64 = 0
Case Is = "Ja"
UE_64 = 1
Case Else
UE_64 = UE_64
End Select
Select Case RV_FREI
Case Is = "allgemeine"
RV_FREI = 0
Case Is = "besondere"
RV_FREI = 1
Case Else
RV_FREI = RV_FREI
End Select
Select Case E_LZZ
Case Is = "Jahr"
E_LZZ = 1
Case Is = "Monat"
E_LZZ = 2
Case Is = "Woche"
E_LZZ = 3
Case Is = "Tag"
E_LZZ = 4
Case Else
E_LZZ = E_LZZ
End Select
Select Case E_R
Case Is = "Nein"
E_R = 0
Case Is = "Ja"
E_R = 1
Case Else
E_R = E_R
End Select
If E_STKL = 0 Or _
STKL > 6 Or _
Year(GiltAb) < 2006 Or _
Year(GiltAb) > 2008 Then
Exit Function
End If
Dim sUrl$, sTxt$, vTxt, vRes, i&
Dim oIE As Object
sUrl = "https://www.abgabenrechner.de/interface/" & Year(GiltAb) & ".jsp?"
sUrl = sUrl & "ALTER1=" & UE_64 & "&"
sUrl = sUrl & "AJAHR=" & E_AJAHR & "&"
sUrl = sUrl & "LZZHINZU=" & E_LZZHINZU * 100 & "&"
sUrl = sUrl & "HINZUR=" & E_LZZHINZU * 100 & "&"
sUrl = sUrl & "JFREIB=" & E_JFREIB * 100 & "&"
sUrl = sUrl & "JHINZU=" & E_JHINZU * 100 & "&"
sUrl = sUrl & "JRE4=" & VJAL * 100 & "&"
sUrl = sUrl & "JVBEZ=" & VJAL_VB * 100 & "&"
sUrl = sUrl & "KRV=" & RV_FREI & "&"
sUrl = sUrl & "LZZ=" & E_LZZ & "&"
sUrl = sUrl & "R=" & E_R & "&"
sUrl = sUrl & "RE4=" & BRUTTO * 100 & "&"
sUrl = sUrl & "SONSTB=" & SB * 100 & "&"
sUrl = sUrl & "STERBE=" & E_STERBE * 100 & "&"
sUrl = sUrl & "STKL=" & E_STKL & "&"
sUrl = sUrl & "VBEZ=" & BRUTTO_VB * 100 & "&"
sUrl = sUrl & "VBEZM=" & VB_Jan * 100 & "&"
sUrl = sUrl & "VBEZS=" & E_VBEZS * 100 & "&"
sUrl = sUrl & "VBS=" & SB_VB * 100 & "&"
sUrl = sUrl & "VJAHR=" & E_VJAHR & "&"
sUrl = sUrl & "VKAPA=" & E_VKAPA * 100 & "&"
sUrl = sUrl & "VMT=" & E_VMT * 100 & "&"
sUrl = sUrl & "LZZFREIB=" & E_LZZFREIB * 100 & "&"
sUrl = sUrl & "WFUNDF=" & E_LZZFREIB * 100 & "&"
sUrl = sUrl & "ZKF=" & Replace(E_ZKF, ",", ".") & "&"
sUrl = sUrl & "ZMVB=" & E_ZMVB
If sUrlAlt <> sUrl Then
Set oIE = CreateObject("InternetExplorer.Application")
oIE.navigate sUrl
Do: Loop Until oIE.Busy = False
Do: Loop Until oIE.Busy = False
sTxt = oIE.document.documentElement.outerTEXT
Set oIE = Nothing
vTxt = Split(sTxt, vbCrLf)
For i = 0 To UBound(vTxt)
If InStr(vTxt(i), "name=") > 0 Then
vRes = Trim(Replace(Replace(vTxt(i), "<", ""), "/>", ""))
vRes = Trim(Replace(vRes, "eingabe", ""))
vRes = Trim(Replace(vRes, "ausgabe", ""))
vRes = Trim(Replace(vRes, "name=", ""))
vRes = Trim(Replace(vRes, "value=", ""))
vRes = Trim(Replace(vRes, """", ""))
vRes = Split(vRes)
Select Case vRes(0)
Case Is = "BK"
BK = vRes(1)
Case Is = "BKS"
BKS = vRes(1)
Case Is = "BKV"
BKV = vRes(1)
Case Is = "LSTLZZ"
LSTLZZ = vRes(1)
Case Is = "SOLZLZZ"
SOLZLZZ = vRes(1)
Case Is = "SOLZS"
SOLZS = vRes(1)
Case Is = "SOLZV"
SOLZV = vRes(1)
Case Is = "STS"
STS = vRes(1)
Case Is = "STV"
STV = vRes(1)
End Select
End If
Next
sUrlAlt = sUrl
End If
Select Case Auswahl
Case 1                   'Rückgabe Lohnsteuer
xlLSTJAHR_BMF = LSTLZZ / 100
Case 11                  'Rückgabe Lohnsteuer _
(sonstige Einkünfte)
xlLSTJAHR_BMF = STS / 100
Case 111                 'Rückgabe Lohnsteuer _
(mehrjährige Tätigkeit)
xlLSTJAHR_BMF = STV / 100
Case 2                   'Rückgabe Solidaritätszuschlag
xlLSTJAHR_BMF = SOLZLZZ / 100
Case 22                  'Rückgabe Solidaritätszuschlag _
(sonstige Einkünfte)
xlLSTJAHR_BMF = SOLZS / 100
Case 222                 'Rückgabe Solidaritätszuschlag _
(mehrjährige Tätigkeit)
xlLSTJAHR_BMF = SOLZV / 100
Case 3                   'Bemessungsgrundlage für die Kirchensteuer
xlLSTJAHR_BMF = BK / 100
Case 33                  'Bemessungsgrundlage für die Kirchensteuer _
(sonstige Einkünfte)
xlLSTJAHR_BMF = BKS / 100
Case 333                 'Bemessungsgrundlage für die Kirchensteuer _
(mehrjährige Tätigkeit)
xlLSTJAHR_BMF = BKV / 100
Case Else
xlLSTJAHR_BMF = 0
End Select
End Function


Gruß Rolf

Bild

Betrifft: AW: Lohnsteuerberechnung (UDF, XLM-Datei, Webabfrage)
von: Rolf
Geschrieben am: 20.07.2008 17:14:41
Hallo Christian,
nochmals vielen Dank. Damit dürfte ich es fertig bekommen.
Gruß
Rolf

 Bild
Excel-Beispiele zum Thema "Lohnsteuerberechnung (UDF, XLM-Datei, Webabfrage)"
Ergebnis einer WebAbfrage in neue Arbeitsmappe Webabfrage periodisch aktualisieren und Ergebnisse speichern
Webabfrage über ein Perl-Script Webabfrage in formatierter Tabelle