Creativity and Innovation

Just another WordPress.com weblog


Leave a comment

Invoking JAVA SOAP webservice from VBA

  1. Requirement
    1. Tomcat Apache Server 7.0
    2. JDK 1.6
    3. Apache CXF 2.0 Webservice jar library
    4. Eclipse Indigo for Web Development
  2. Steps
    1. Configure your Eclipse for Apache CXF Webservice
    2. Import the ‘MyWebService’ Project in your eclipse Excel file content the embedded object Click here to get the Excel file Calling JAVA SOAP webservice from VBA
    3. Run the ‘TestPrintingInterfaceServer’ program in eclipse on Tomcat Server
    4. Run the ‘TestPrintingInterfaceClient’ program in eclipse to test the webservice is running or not
    5. Go in Excel VBA editor
    6. Consume the webservice
  3. Note
      1. Open Excel File.
      2. Press Alt+F11 button here to go in VBA editor.
      3. Create module ‘WebService_Module’
      4. Add the below code in that


    Option Explicit

    ‘Add Microsoft XML V6.0 library in reference
    Sub invoke_JAVA_SOAP_WebService()

    On Error GoTo ERROR_BLOCK

    Dim ObjHTTP As MSXML2.XMLHTTP
    Dim sURL As String
    Dim sEnv As String
    Set ObjHTTP = New MSXML2.XMLHTTP

    ‘Endpoint address
    sURL = “http://localhost:8020/MyWebService/services/TestPrintingPort”

    ‘SOAP envelope for submission to the Web Service
    sEnv = “
    sEnv = sEnv & “<soapenv:Envelope ” sEnv = sEnv & “xmlns:xsi=””http://www.w3.org/2001/XMLSchema-instance”&#8221; ” sEnv = sEnv & “xmlns:xsd=””http://www.w3.org/2001/XMLSchema”&#8221; ” sEnv = sEnv & “xmlns:soapenv=””http://schemas.xmlsoap.org/soap/envelope/””>&#8221;
    sEnv = sEnv & ” ”
    sEnv = sEnv & ” ” ‘Java function name ‘ChangeCaseToUpper’ and ‘Target namespace’
    sEnv = sEnv & ” ganesh bhagat” ‘Function argument Name in webservice is ‘arg0’
    sEnv = sEnv & ” ”
    sEnv = sEnv & ” ”
    sEnv = sEnv & “”

    ‘Invoke a web service which requires authentication
    ‘ObjHTTP.Open “Post”, sURL, False, “username”, “password”

    ‘Invoke a web service which doesn’t require any user authentication
    ObjHTTP.Open “Post”, sURL, False

    ObjHTTP.setRequestHeader “Content-Type”, “text/xml”
    ObjHTTP.send (sEnv)

    Debug.Print ObjHTTP.responseText

    EXIT_BLOCK:
    On Error Resume Next
    ‘clean up code
    Set ObjHTTP = Nothing
    Exit Sub

    ERROR_BLOCK:
    Debug.Print Err.Description
    Resume EXIT_BLOCK
    End Sub

Advertisements


Leave a comment

Authenticate User and Password with Active Directory


Option Explicit

Sub Test()
Dim status As Boolean

status = authenticateWindowsLogin(“GANESH”, “pass@1273”, “home”)

If (status = True) Then
Debug.Print “User is authenticated successfully”
Else
Debug.Print “User is not authenticated”
End If

End Sub

‘Authenticates user and password entered with Active Directory.
Private Function authenticateWindowsLogin(ByVal strUserName As String, ByVal strpassword As String, ByVal strDomain As String) As Boolean

On Error GoTo ERROR_BLOCK

Dim oADsObject As Object, oADsNamespace As Object
Dim strADsPath As String

strADsPath = “WinNT://” & strDomain
Set oADsObject = GetObject(strADsPath)
Set oADsNamespace = GetObject(“WinNT:”)
Set oADsObject = oADsNamespace.OpenDSObject(strADsPath, strDomain & “\” & strUserName, strpassword, 0)

authenticateWindowsLogin = True ‘ACCESS GRANTED

Exit_BLOCK:
On Error Resume Next
Exit Function

ERROR_BLOCK:
Debug.Print “Error Source:<” & Err.Source & “>,Function Name:”
Debug.Print “Error Number:<” & Err.Number & “>,Error Description:<” & Err.Description & “>”
authenticateWindowsLogin = False ‘ACCESS DENIED
Err.Clear
Resume Exit_BLOCK
End Function