Using Web Services with Excel

Description

Lets say that we want to perform some calculations on a data set stored in a database backend server. On the client side we can have the Excel program or the browser (or mobile browser). This example proposes a simple solution to this with no server-side dependence on Excel.

The basic operations in almost web applications consist in retrieving and updating data; so we use a class with two public functions:

Public Function GetValues() As ReturnValues
Public Function UpdateData( ByVal Avg_sales_deal_size As Double, _ByVal Avg_total_number_of_deals_year As Double, ByVal Avg_profit_margin_customer As Double, _ByVal Increase_in_margin_customer As Double, ByVal Reduction_in_price_discounted_deals As Double _) As Integer

This class is used to connect the database with the Excel (through a webservice) or the browser/mobile browser (through the webform/mobile webform).

The Excel clients must have installed SOAP Toolkit (in this case I tested with SOAP Toolkit 2.0 SP2) and to use the Excel (version 9.0) file from source zip (its recommended to save it locally and not to use it directly from server because if you try to save you can get an error during security permissions on server).

The spreadsheet file uses VBA macros to perform the connection with the webservice so you enable macros when you try to open it. In order to use the SOAP I added a reference to SOAP Type Lib:



Dim objSOAPClient As MSSOAPLib.SoapClient
Dim wsReturn As Object
Set objSOAPClient = New MSSOAPLib.SoapClient
objSOAPClient.mssoapinit bstrWSDLFile:=http://localhost/ROIModel/Service1.asmx?WSDL
Set wsReturn = objSOAPClient.GetValues()

For clients without Excel we used usual webform and mobileform to perform the same calculations as in the excel file. 

Conclusion

we don't have any server-side dependence on Excel, only on client-side; if the client has the Excel he can use it; if not, he can use the preferred browser even from the mobile phone.