I was facing problems with the web service call from excel and found no help on the web.I have tried googling but landed nowhere.I will show a simple way of calling web service from the excel workbook.
steps1:
Download the Microsoft Office XP Web Services Toolkit2.0
http://www.microsoft.com/downloads/details.aspx?familyid=4922060f-002a-4f5b-af74-978f2cd6c798&displaylang=en
step2:
Open Excel and go to tools->macro->Visual basic editor.
step3:
In the Visual basic editor.goto tools->web service references.As seen in the pic. above.Type the name of the web service and add the web reference.
Step4:
A class module will get created on the vbaproject with some methods such as Class_Initialize(),Class_Terminate(),ServiceErrorHandler(str_Function As String) and with the other methods of the web sevice.
step5:
create a object of the web service and you will have all the mehtods of the web service.
eg:-
Dim wsxls As New clsws_Service
wsxls.wsm_NotificationEngine "promothash@codefasttrack.com"
This seems to be very easy but the problems that may encounter are many like-
issue related to date objects,connection time out problem [-2147221504 error] etc.
Default the connector property of the service is HTTP but it causes problem related to connection time out so to avoid it you must use the "WinInetConnector30" and also set the timeout.
add this code snippet to the Class_Initialize() method to avoid a timeout error[-2147221504].
'declarations
Private sc_Service As SoapClient30
Private Const c_WSDL_URL As String = "http://localhost/wexcel/Service.asmx?wsdl"
Private Const c_SERVICE As String = "Service"
Private Const c_PORT As String = "ServiceSoap"
Private Const c_SERVICE_NAMESPACE As String = "http://tempuri.org/"
Private Sub Class_Initialize()
'*****************************************************************
'This subroutine will be called each time the class is instantiated.
'Creates sc_ComplexTypes as new SoapClient30, and then
'initializes sc_ComplexTypes.mssoapinit2 with WSDL file found in
'http://localhost/wexcel/Service.asmx?wsdl.
'*****************************************************************
Dim str_WSML As String
str_WSML = ""
Set sc_Service = New SoapClient30
sc_Service.MSSoapInit2 c_WSDL_URL, str_WSML, c_SERVICE, c_PORT, c_SERVICE_NAMESPACE
'Use the proxy server defined in Internet Explorer's LAN settings by
'setting ProxyServer to
sc_Service.ClientProperty("ServerHTTPRequest") = False
sc_Service.ClientProperty("ConnectorProgID") = "MSSOAP.WinInetConnector30"
sc_Service.ConnectorProperty("ProxyServer") = "
sc_Service.ConnectorProperty("Timeout") = "90000"
'Autodetect proxy settings if Internet Explorer is set to autodetect
'by setting EnableAutoProxy to True
sc_Service.ConnectorProperty("EnableAutoProxy") = True
End Sub
