VBA IE automation

P

PeterChao

New Member
Joined
Mar 5, 2020
Messages
11
Office Version
  1. 2010
Platform
  1. Windows
Hi,

I found this VBA on this forum, but I need some changes applied to it, that I hope you guys can help me with.

VBA Code: 
Public Function IsVatNumberValid(ByVal countryCode As String, ByVal vatNumber As String) As Variant

    Static req As Object
    If req Is Nothing Then Set req = CreateObject("MSXML2.XMLHTTP")
    
    On Error GoTo handler
    
    With req
        .Open "POST", "http://ec.europa.eu/taxation_customs/vies/services/checkVatService", False
        .send "<?xml version=""1.0"" encoding=""UTF-8"" standalone=""no""?><SOAP-ENV:Envelope xmlns:SOAP-ENV=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:tns1=""urn:ec.europa.eu:taxud:vies:services:checkVat:types"" xmlns:soapenc=""http://schemas.xmlsoap.org/soap/encoding/"" xmlns:impl=""urn:ec.europa.eu:taxud:vies:services:checkVat"" xmlns:apachesoap=""http://xml.apache.org/xml-soap"" xmlns:wsdl=""http://schemas.xmlsoap.org/wsdl/"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:wsdlsoap=""http://schemas.xmlsoap.org/wsdl/soap/"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" ><SOAP-ENV:Body><tns1:checkVat xmlns:tns1=""urn:ec.europa.eu:taxud:vies:services:checkVat:types""><tns1:countryCode>" & countryCode & "</tns1:countryCode><tns1:vatNumber>" & vatNumber & "</tns1:vatNumber></tns1:checkVat></SOAP-ENV:Body></SOAP-ENV:Envelope>"
        
        
        'There's a server error or an error with the input
        If InStr(.responseText, "faultstring") > 0 Then
            IsVatNumberValid = Split(Split(.responseText, "faultstring>")(1), "<")(0)
            Exit Function
        End If
        
        'There are no validation errors, read whether it is valid
        If InStr(.responseText, "valid") > 0 Then
            If Split(Split(.responseText, "<valid>")(1), "<")(0) = "true" Then
                IsVatNumberValid = "Ja, gyldigt momsregistreringsnummer"
            Else
                IsVatNumberValid = "Nej, momsnummeret er ugyldigt for grænseoverskridende transaktioner inden for EU."
            End If
            Exit Function
        End If
        
    End With
    
'If we've got this far there's something else wrong or an error has been raised
handler:
    IsVatNumberValid = CVErr(18)
    
End Function

This VBA is automated to give me a result on VAT numbers.

There are new requirements to EU regulations, so I have to add another function to the VBA. If you go to EU Validation site, you can see there is 2 options, the Member State section and Requester Member State section. In the current VBA it is automated to fill in the Member State section, and return with result from this.

Now we need to add our VAT number in the Requester section and return with a Consultation Number in the worksheet.

For a example you can type SE556042722001 (Completely random VAT) to see Consultation number example.

Can anyone help me with this matter? Thanks!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Try this GetVatNumberData function, which returns the Valid flag and Consultation Number (requestIdentifier in the XML response). If required, the function can be easily modified to return all parts of the VAT data response (traderName, traderCompanyType and traderAddress). I've left in the XML string for the checkVat request used by the original IsVatNumberValid function, so it can also be used to simply check the validity of a VAT number.

The Test routine shows how to call GetVatNumberData and interpret its return data.

VBA Code: 
Public Sub Test()

    Dim VatData As Variant
    Dim VatError As ErrObject
   
    Set VatError = GetVatNumberData("SE", "556042722001", "SE", "556042722001", VatData)
   
    If VatError Is Nothing Then
        'No run-time error occurred
        If VatData(0) = True Then
            'Service succeeded
            MsgBox "Valid = " & VatData(1) & vbNewLine & _
                   "Consultation Number = " & VatData(2), Title:="Service Succeeded"
        Else
            'Service returned soap error
            MsgBox "Error: " & VatData(1) & vbNewLine & _
                   VatData(2), Title:="Service Soap Error"
        End If
    Else
        'Run-time error occurred
        MsgBox VatError.Description
    End If
       
End Sub


Public Function GetVatNumberData(ByVal countryCode As String, ByVal vatNumber As String, ByVal requesterCountryCode As String, ByVal requesterVatNumber As String, ByRef VatData As Variant) As ErrObject

    Static http As Object
    Dim XMLdoc As Object
    Dim XMLrequest As String
    Dim checkVatApproxResponse As Object
    Dim soapFault As Object
    Dim responseData(0 To 2) As Variant
   
    Set XMLdoc = CreateObject("MSXML2.DOMDocument")
   
    If http Is Nothing Then Set http = CreateObject("MSXML2.XMLHTTP")
   
    On Error GoTo handler
       
'    'checkVat request
'    XMLrequest = "<?xml version='1.0' encoding='UTF-8' standalone='no'?>" & _
'                 "<SOAP-ENV:Envelope xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'" & _
'                 " xmlns:tns1='urn:ec.europa.eu:taxud:vies:services:checkVat:types'" & _
'                 " xmlns:soapenc='http://schemas.xmlsoap.org/soap/encoding/'" & _
'                 " xmlns:impl='urn:ec.europa.eu:taxud:vies:services:checkVat'" & _
'                 " xmlns:apachesoap='http://xml.apache.org/xml-soap'" & _
'                 " xmlns:wsdl='http://schemas.xmlsoap.org/wsdl/'" & _
'                 " xmlns:xsd='http://www.w3.org/2001/XMLSchema'" & _
'                 " xmlns:wsdlsoap='http://schemas.xmlsoap.org/wsdl/soap/'" & _
'                 " xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>" & _
'                 " <SOAP-ENV:Body>" & _
'                 "  <tns1:checkVat xmlns:tns1='urn:ec.europa.eu:taxud:vies:services:checkVat:types'>" & _
'                 "   <tns1:countryCode>" & countryCode & "</tns1:countryCode>" & _
'                 "   <tns1:vatNumber>" & vatNumber & "</tns1:vatNumber>" & _
'                 "  </tns1:checkVat>" & _
'                 " </SOAP-ENV:Body>" & _
'                 "</SOAP-ENV:Envelope>"
         
    'checkVatApprox request - the Consultation Number is returned in the requestIdentifier element in the checkVatApproxResponse xml
    XMLrequest = "<?xml version='1.0' encoding='UTF-8' standalone='no'?>" & _
                "<SOAP-ENV:Envelope xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'" & _
                " xmlns:tns1='urn:ec.europa.eu:taxud:vies:services:checkVat:types'" & _
                " xmlns:soapenc='http://schemas.xmlsoap.org/soap/encoding/'" & _
                " xmlns:impl='urn:ec.europa.eu:taxud:vies:services:checkVat'" & _
                " xmlns:apachesoap='http://xml.apache.org/xml-soap'" & _
                " xmlns:wsdl='http://schemas.xmlsoap.org/wsdl/'" & _
                " xmlns:xsd='http://www.w3.org/2001/XMLSchema'" & _
                " xmlns:wsdlsoap='http://schemas.xmlsoap.org/wsdl/soap/'" & _
                " xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>" & _
                " <SOAP-ENV:Body>" & _
                "  <tns1:checkVatApprox xmlns:tns1='urn:ec.europa.eu:taxud:vies:services:checkVat:types'>" & _
                "   <tns1:countryCode>" & countryCode & "</tns1:countryCode>" & _
                "   <tns1:vatNumber>" & vatNumber & "</tns1:vatNumber>" & _
                "   <tns1:requesterCountryCode>" & requesterCountryCode & "</tns1:requesterCountryCode>" & _
                "   <tns1:requesterVatNumber>" & requesterVatNumber & "</tns1:requesterVatNumber>" & _
                "  </tns1:checkVatApprox>" & _
                " </SOAP-ENV:Body>" & _
                "</SOAP-ENV:Envelope>"
       
    With http
        .Open "POST", "http://ec.europa.eu/taxation_customs/vies/services/checkVatService", False
        .send (XMLrequest)
        Debug.Print .responseText
    End With
      
    'Example checkVatApprox response XML
    '
    '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    ' <soap:Body>
    '  <checkVatApproxResponse xmlns="urn:ec.europa.eu:taxud:vies:services:checkVat:types">
    '   <countryCode>SE</countryCode>
    '   <vatNumber>556042722001</vatNumber>
    '   <requestDate>2020-03-07+01:00</requestDate>
    '   <valid>true</valid>
    '   <traderName>H &amp; M Hennes &amp; Mauritz AB</traderName>
    '   <traderCompanyType>---</traderCompanyType>
    '   <traderAddress>MÄSTER SAMUELSGATAN 46 A
    '106 38 STOCKHOLM</traderAddress>
    '   <requestIdentifier>WAPIAAAAXC1V5ayw</requestIdentifier>
    '  </checkVatApproxResponse>
    ' </soap:Body>
    '</soap:Envelope>
      
    'Example soap fault response XML
    '
    '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    ' <soap:Body>
    '  <soap:Fault>
    '   <faultcode>soap:Client</faultcode>
    '   <faultstring>Message part {urn:ec.europa.eu:taxud:vies:services:checkVat:types}XcheckVatApprox was not recognized.  (Does it exist in service WSDL?)</faultstring>
    '  </soap:Fault>
    ' </soap:Body>
    '</soap:Envelope>
   
    With XMLdoc
        .validateOnParse = False
        .SetProperty "SelectionLanguage", "XPath"
        .SetProperty "SelectionNamespaces", "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/' xmlns:resp='urn:ec.europa.eu:taxud:vies:services:checkVat:types'"
        .LoadXML http.responseText
    End With

    'Did a soap fault occur?
   
    Set soapFault = XMLdoc.SelectSingleNode("//soap:Envelope/soap:Body/soap:Fault")
   
    If soapFault Is Nothing Then
       
        'No - service returned successful response, so extract checkVatApproxResponse details
       
        Set checkVatApproxResponse = XMLdoc.SelectSingleNode("//resp:checkVatApproxResponse")
        responseData(0) = True 'Success
        responseData(1) = checkVatApproxResponse.SelectSingleNode("resp:valid").Text
        responseData(2) = checkVatApproxResponse.SelectSingleNode("resp:requestIdentifier").Text
       
    Else
   
        'Yes - service returned a soap fault
       
        responseData(0) = False 'Failure
        responseData(1) = soapFault.SelectSingleNode("faultcode").Text
        responseData(2) = soapFault.SelectSingleNode("faultstring").Text

    End If
          
    VatData = responseData
    Set GetVatNumberData = Nothing
   
    Exit Function
   
    'A run-time error occurred
handler:
    Set GetVatNumberData = Err

End Function
 
Upvote 0
Hmm, can't really get this to work.

In the old VBA I typed in flag in L6 like "SE" and in L8 I typed in the VAT number, then I got answer in J11.

Can I keep this format and then just add consultation number in like J13? The IsVatNumberValid function doesn't work with this new code.
 
Upvote 0
You didn't say you wanted to use it as a UDF which returns the Consultation Number.

Try this code:
VBA Code: 
Public Function GetVatConsultationNumber(ByVal countryCode As String, ByVal vatNumber As String, ByVal requesterCountryCode As String, ByVal requesterVatNumber As String) As String

    Static http As Object
    Dim XMLdoc As Object
    Dim XMLrequest As String
    Dim checkVatApproxResponse As Object
    Dim soapFault As Object
    
    Set XMLdoc = CreateObject("MSXML2.DOMDocument")
    
    If http Is Nothing Then Set http = CreateObject("MSXML2.XMLHTTP")
    
    On Error GoTo handler
        
    'checkVatApprox request - the Consultation Number is returned in the requestIdentifier element in the checkVatApproxResponse xml
    XMLrequest = "<?xml version='1.0' encoding='UTF-8' standalone='no'?>" & _
                "<SOAP-ENV:Envelope xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'" & _
                " xmlns:tns1='urn:ec.europa.eu:taxud:vies:services:checkVat:types'" & _
                " xmlns:soapenc='http://schemas.xmlsoap.org/soap/encoding/'" & _
                " xmlns:impl='urn:ec.europa.eu:taxud:vies:services:checkVat'" & _
                " xmlns:apachesoap='http://xml.apache.org/xml-soap'" & _
                " xmlns:wsdl='http://schemas.xmlsoap.org/wsdl/'" & _
                " xmlns:xsd='http://www.w3.org/2001/XMLSchema'" & _
                " xmlns:wsdlsoap='http://schemas.xmlsoap.org/wsdl/soap/'" & _
                " xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>" & _
                " <SOAP-ENV:Body>" & _
                "  <tns1:checkVatApprox xmlns:tns1='urn:ec.europa.eu:taxud:vies:services:checkVat:types'>" & _
                "   <tns1:countryCode>" & countryCode & "</tns1:countryCode>" & _
                "   <tns1:vatNumber>" & vatNumber & "</tns1:vatNumber>" & _
                "   <tns1:requesterCountryCode>" & requesterCountryCode & "</tns1:requesterCountryCode>" & _
                "   <tns1:requesterVatNumber>" & requesterVatNumber & "</tns1:requesterVatNumber>" & _
                "  </tns1:checkVatApprox>" & _
                " </SOAP-ENV:Body>" & _
                "</SOAP-ENV:Envelope>"
        
    With http
        .Open "POST", "http://ec.europa.eu/taxation_customs/vies/services/checkVatService", False
        .send (XMLrequest)
    End With
       
    'Example checkVatApprox response XML
    '
    '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    ' <soap:Body>
    '  <checkVatApproxResponse xmlns="urn:ec.europa.eu:taxud:vies:services:checkVat:types">
    '   <countryCode>SE</countryCode>
    '   <vatNumber>556042722001</vatNumber>
    '   <requestDate>2020-03-07+01:00</requestDate>
    '   <valid>true</valid>
    '   <traderName>H &amp; M Hennes &amp; Mauritz AB</traderName>
    '   <traderCompanyType>---</traderCompanyType>
    '   <traderAddress>MÄSTER SAMUELSGATAN 46 A
    '106 38 STOCKHOLM</traderAddress>
    '   <requestIdentifier>WAPIAAAAXC1V5ayw</requestIdentifier>
    '  </checkVatApproxResponse>
    ' </soap:Body>
    '</soap:Envelope>
       
    'Example soap fault response XML
    '
    '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    ' <soap:Body>
    '  <soap:Fault>
    '   <faultcode>soap:Client</faultcode>
    '   <faultstring>Message part {urn:ec.europa.eu:taxud:vies:services:checkVat:types}XcheckVatApprox was not recognized.  (Does it exist in service WSDL?)</faultstring>
    '  </soap:Fault>
    ' </soap:Body>
    '</soap:Envelope>
    
    With XMLdoc
        .validateOnParse = False
        .SetProperty "SelectionLanguage", "XPath"
        .SetProperty "SelectionNamespaces", "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/' xmlns:resp='urn:ec.europa.eu:taxud:vies:services:checkVat:types'"
        .LoadXML http.responseText
    End With

    'Did a soap fault occur?
    
    Set soapFault = XMLdoc.SelectSingleNode("//soap:Envelope/soap:Body/soap:Fault")
    
    If soapFault Is Nothing Then
        
        'No - service returned successful response, so extract checkVatApproxResponse details
        
        Set checkVatApproxResponse = XMLdoc.SelectSingleNode("//resp:checkVatApproxResponse")
        GetVatConsultationNumber = checkVatApproxResponse.SelectSingleNode("resp:requestIdentifier").Text
        
    Else
    
        'Yes - service returned a soap fault
        
        GetVatConsultationNumber = soapFault.SelectSingleNode("faultcode").Text & " - " & soapFault.SelectSingleNode("faultstring").Text

    End If
           
    Exit Function
    
    'A run-time error occurred
handler:
    GetVatConsultationNumber = "Error number " & Err.Number & ": " & Err.Description

End Function
Note that the function takes 4 arguments:

GetVatConsultationNumber(ByVal countryCode As String, ByVal vatNumber As String, ByVal requesterCountryCode As String, ByVal requesterVatNumber As String)

to call it from a cell as a UDF:

=GetVatConsultationNumber(A2,B2,C2,D2)

If the country codes and VAT numbers are the same then:

=GetVatConsultationNumber(A2,B2,A2,B2)
 
Upvote 0
Works flawless, thanks a lot.

Just 2 quick questions, can I hide the invalid_input result, when not searching for anything, so the result only appear, once valid?

And can I add a default requester in VBA code, so my company always is the requester?
 
Upvote 0
1. Change:
VBA Code: 
        GetVatConsultationNumber = soapFault.SelectSingleNode("faultcode").Text & " - " & soapFault.SelectSingleNode("faultstring").Text
to:
VBA Code: 
        GetVatConsultationNumber = ""

2. Change the function 'signature' and put the requester arguments inside the function:
VBA Code: 
Public Function GetVatConsultationNumber(ByVal countryCode As String, ByVal vatNumber As String) As String

    Dim requesterCountryCode As String, requesterVatNumber As String
    
    requesterCountryCode = "SE"
    requesterVatNumber = "1234567890"
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Soap Query and dump in excel
Replies
7
Views
5K
DarkValley
D
S
  • Question Question
Handling of VBA XML response
2
Replies
11
Views
16K
manka
M
S
  • Question Question
vba 2007 sharepoint 2007 - update sharepoint list
Replies
0
Views
2K
suzannejb
S
R
  • Question Question
VBA automate IE actions
2 3
Replies
23
Views
5K
Rasmussen
R
M
  • Question Question
CAML Query Sharepoint and Default List Views
Replies
1
Views
1K
mmarzouk
M

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top