Thema: Ausländische UStNr nach dem WDDX-Verfahren prüfen

StandardModule: Modul1

Sub IsUStIDOK()
   Dim vRow As Variant
   Dim iQuery As Integer, iChar As Integer
   Dim sQuery As String, sNo As String, sTxt As String
   iQuery = 2
   Do Until IsEmpty(Cells(iQuery, 1))
sQuery = "http://wddx.bff-online.de/ustid.php?eigene_id=" & _
         Range("H1").Value & "&abfrage_id=" & Cells(iQuery, 1).Value
      With ActiveSheet.QueryTables.Add(Connection:= _
         "URL;" & sQuery, Destination:=Range("IV1"))
         .Name = "ustid"
         .Refresh BackgroundQuery:=False
      End With
      sTxt = Range("IV3").Value
      For iChar = 1 To Len(sTxt) - 20
         If Mid(sTxt, iChar, 20) Like "<string>###</string>" Then
            sNo = Mid(sTxt, iChar + 8, 3)
            Exit For
         End If
      Next iChar
      If IsNumeric(sNo) Then
         vRow = Application.Match(CInt(sNo), Worksheets("Codes").Columns(1), 0)
         If Not IsError(vRow) Then
            Cells(iQuery, 2).Value = Worksheets("Codes").Cells(vRow, 2).Value
         End If
      Else
         Cells(iQuery, 2).Value = "Problem: " & sNo
      End If
      Columns("IV").Delete
      iQuery = iQuery + 1
   Loop
End Sub

Beiträge aus dem Excel-Forum zu den Themen Extern und Internet