To access a SOAP web service (such as a Syspro web service) from Visual Basic for Applications (such as an Excel 2003 macro), you can download
Microsoft’s SOAP toolkit and access that from Visual Basic.
If you tailor the example code from Microsoft’s SOAP toolkit to make it easy to call Syspro functions; here’s a code snippet example:
Dim GUID as String
Dim XmlOut as String
Dim WebServicesBaseURL = "http://example.com/sysprowebservices"
GUID = LogonToSysproViaWebServices()
XmlOut = CreateSalesOrderWebServices(GUID, "SORTOI", XmlParameters, XmlIn)
Private Function LogonToSysproViaWebServices() As String
Dim XmlIn As String
Dim XmlOut As String
Dim GUID As String
XmlIn = ""
Dim objSysproWS As New Syspro_Utilities_Web_Service
objSysproWS.Setup WebServicesBaseURL
XmlOut = objSysproWS.Logon(Operator, OperatorPassword, CompanyId, CompanyPassword, LanguageCode, LogLevel, EncoreInstance, XmlIn)
GUID = Trim(XmlOut)
Set objSysproWS = Nothing
LogonToSysproViaWebServices = GUID
End Function
Private Function CreateSalesOrderWebServices(ByRef GUID As String, ByRef BusinessObject As String, ByRef XmlParameters As String, ByRef XmlIn As String) As String
Dim XmlOut As String
Dim objSysproWS As New Syspro_Transaction_Web_Service
objSysproWS.Setup WebServicesBaseURL
XmlOut = objSysproWS.Post(GUID, BusinessObject, XmlParameters, XmlIn)
Set objSysproWS = Nothing
CreateSalesOrderWebServices = XmlOut
End Function ' CreateSalesOrderWebServices
Place the following code in a Class Module in Excel; call the moduleSyspro_Utilities_Web_Service, or change the above code to match whatever you call it.
'*****************************************************************
' This is based on Microsoft-generated code from:
' http://msdn.microsoft.com/en-us/magazine/cc163837.aspx
' ... but it has been modified to make the URL configurable.
'
'*****************************************************************
'This class was created by the Microsoft Office 2003 Web Services Toolkit.
'
'Description:
'This class is a Visual Basic for Applications class representation of the 'Web service as defined by http://localhost/sysprowebservice/transaction.asmx?wsdl.
'
'To Use:
'Dimension a variable as new clsws_Service, and then write code to
'use the methods provided by the class.
'Example:
' Dim ExampleVar as New clsws_Service
' debug.print ExampleVar.wsm_Post("Sample Input")
'
'For more information, see Complex Types in Microsoft Office 2003
'Web Services Toolkit Help.
'
'Changes to the code in this class may result in incorrect behavior.
'
'*****************************************************************
Option Explicit
'Dimensioning private class variables.
Private sc_Service As SoapClient30
' e.g. Private Const c_WSDL_URL As String = "http://localhost/sysprowebservices/utilities.asmx?WSDL"
' e.g. Private Const c_WSDL_URL As String = "http://www.example.com/sysprowebservices2/utilities.asmx?WSDL"
' The last part of the line MUST be "/utilities.asmx?WSDL".
'Private Const c_WSDL_URL As String = "http://localhost/sysprowebservices/utilities.asmx?WSDL"
Private c_WSDL_URL As String
Private Const c_WSDL_URL_Extension As String = "/utilities.asmx?WSDL"
Private Const c_SERVICE As String = "Service"
Private Const c_PORT As String = "ServiceSoap"
Private Const c_SERVICE_NAMESPACE As String = "http://www.syspro.com/ns/utilities/"
Private Sub Class_Initialize()
End Sub
Public Sub Setup(ByVal c_WSDL_URL_Base As String)
'*****************************************************************
'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/sysprowebservices/transaction.asmx?wsdl.
'*****************************************************************
Dim str_WSML As String
' WSML: Default value is "".
' This string is in Web Services Meta Language (WSML).
' This is a required parameter only when using custom type mappers.
str_WSML = ""
Set sc_Service = New SoapClient30
' Set sc_Service = Server.CreateObject("MSSOAP.SoapClient30")
' Not needed:
'sc_Service.ClientProperty("ServerHTTPRequest") = True
c_WSDL_URL = c_WSDL_URL_Base & c_WSDL_URL_Extension
sc_Service.MSSoapInit (c_WSDL_URL)
' Doesn't work; reason unknown:
'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.ConnectorProperty("ProxyServer") = ""
'Autodetect proxy settings if Internet Explorer is set to autodetect
'by setting EnableAutoProxy to True
sc_Service.ConnectorProperty("EnableAutoProxy") = True
End Sub
Private Sub Class_Terminate()
'*****************************************************************
'This subroutine will be called each time the class is destructed.
'Sets sc_ComplexTypes to Nothing.
'*****************************************************************
'Error Trap
On Error GoTo Class_TerminateTrap
Set sc_Service = Nothing
Exit Sub
Class_TerminateTrap:
ServiceErrorHandler ("Class_Terminate")
End Sub
Private Sub ServiceErrorHandler(str_Function As String)
'*****************************************************************
'This subroutine is the class error handler. It can be called from any
'class subroutine or function when that subroutine or function
'encounters an error. Then, it will raise the error along with the
'name of the calling subroutine or function.
'*****************************************************************
'SOAP Error
If sc_Service.FaultCode "" Then
Err.Raise vbObjectError, str_Function, sc_Service.FaultString
'Non SOAP Error
Else
Err.Raise Err.Number, str_Function, Err.Description
End If
End Sub
Public Function Logon(ByVal Operator As String, ByVal OperatorPassword As String, ByVal CompanyId As String, ByVal CompanyPassword As String, ByVal LanguageCode As String, ByVal LogLevel As String, ByVal EncoreInstance As String, ByVal XmlIn As String) As String
'*****************************************************************
'Proxy function created from
'http://localhost/sysprowebservice/utilities.asmx?wsdl.
'
'"Logon" is defined as XML. See Complex Types: XML Variables
'in Microsoft Office 2003 Web Services Toolkit Help for details on
'implementing XML variables.
'*****************************************************************
'Error Trap
On Error GoTo Logon_ErrorHandler
Logon = sc_Service.Logon(Operator, OperatorPassword, CompanyId, CompanyPassword, LanguageCode, LogLevel, EncoreInstance, XmlIn)
Exit Function
Logon_ErrorHandler:
ServiceErrorHandler "Logon"
End Function
Public Function Logoff(ByVal Operator As String) As String
'*****************************************************************
'Proxy function created from
'http://localhost/sysprowebservice/utilities.asmx?wsdl.
'
'"Logoff" is defined as XML. See Complex Types: XML Variables
'in Microsoft Office 2003 Web Services Toolkit Help for details on
'implementing XML variables.
'*****************************************************************
'Error Trap
On Error GoTo Logoff_ErrorHandler
Logoff = sc_Service.Logoff(Operator)
Exit Function
Logoff_ErrorHandler:
ServiceErrorHandler "Logoff"
End Function
' TODO: Create other routines: GetLogonProfile, Run.
Place the following code in a Class Module in Excel; call the moduleSyspro_Transaction_Web_Service, or change the above code to match whatever you call it.
'*****************************************************************
' Proxy to post to the a web service.
'
' This is based on Microsoft-generated code from:
' http://msdn.microsoft.com/en-us/magazine/cc163837.aspx
' ... but it has been modified to make the URL configurable.
'
'*****************************************************************
'This class was created by the Microsoft Office 2003 Web Services Toolkit.
'
'Description:
'This class is a Visual Basic for Applications class representation of the 'Web service as defined by http://localhost/sysprowebservice/transaction.asmx?wsdl.
'
'To Use:
'Dimension a variable as new clsws_Service, and then write code to
'use the methods provided by the class.
'Example:
' Dim ExampleVar as New clsws_Service
' debug.print ExampleVar.wsm_Post("Sample Input")
'
'For more information, see Complex Types in Microsoft Office 2003
'Web Services Toolkit Help.
'
'Changes to the code in this class may result in incorrect behavior.
'
'*****************************************************************
Option Explicit
'Dimensioning private class variables.
Private sc_Service As SoapClient30
Dim c_WSDL_URL As String
Private Const c_WSDL_URL_Extension As String = "/transaction.asmx?WSDL"
Private Const c_SERVICE As String = "Service"
Private Const c_PORT As String = "ServiceSoap"
Private Const c_SERVICE_NAMESPACE As String = "http://www.syspro.com/ns/transaction/"
Private Sub Class_Initialize()
End Sub
Public Sub Setup(ByVal c_WSDL_URL_Base As String)
'*****************************************************************
'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/sysprowebservices/transaction.asmx?wsdl.
'*****************************************************************
Dim str_WSML As String
str_WSML = ""
Set sc_Service = New SoapClient30
' Set sc_Service = Server.CreateObject("MSSOAP.SoapClient30")
' Not needed:
'sc_Service.ClientProperty("ServerHTTPRequest") = True
c_WSDL_URL = c_WSDL_URL_Base & c_WSDL_URL_Extension
sc_Service.MSSoapInit (c_WSDL_URL)
' Doesn't work; reason unknown:
'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.ConnectorProperty("ProxyServer") = ""
'Autodetect proxy settings if Internet Explorer is set to autodetect
'by setting EnableAutoProxy to True
sc_Service.ConnectorProperty("EnableAutoProxy") = True
End Sub
Private Sub Class_Terminate()
'*****************************************************************
'This subroutine will be called each time the class is destructed.
'Sets sc_ComplexTypes to Nothing.
'*****************************************************************
'Error Trap
On Error GoTo Class_TerminateTrap
Set sc_Service = Nothing
Exit Sub
Class_TerminateTrap:
ServiceErrorHandler ("Class_Terminate")
End Sub
Private Sub ServiceErrorHandler(str_Function As String)
'*****************************************************************
'This subroutine is the class error handler. It can be called from any
'class subroutine or function when that subroutine or function
'encounters an error. Then, it will raise the error along with the
'name of the calling subroutine or function.
'*****************************************************************
'SOAP Error
If sc_Service.FaultCode "" Then
Err.Raise vbObjectError, str_Function, sc_Service.FaultString
'Non SOAP Error
Else
Err.Raise Err.Number, str_Function, Err.Description
End If
End Sub
Public Function Post(ByVal UserId As String, ByVal BusinessObject As String, ByVal XmlParameters As String, ByVal XmlIn As String) As String
'*****************************************************************
'Proxy function created from
'http://localhost/sysprowebservice/transaction.asmx?wsdl.
'
'"Post" is defined as XML. See Complex Types: XML Variables
'in Microsoft Office 2003 Web Services Toolkit Help for details on
'implementing XML variables.
'*****************************************************************
'Error Trap
On Error GoTo Post_ErrorHandler
Post = sc_Service.Post(UserId, BusinessObject, XmlParameters, XmlIn)
Exit Function
Post_ErrorHandler:
ServiceErrorHandler "Post"
End Function
In Excel, in the Microsoft Visual Basic for Applications window, you will need to go to Tools / References and add these references, if you not using late binding to the Microsoft SOAP library:
- Microsoft Soap Type Library v3.0
- Microsoft Soap WinHttp Connector Type Library (v3.0)
- Microsoft Soap WinInet Connector Type Library (v3.0)
Microsoft’s SOAP libraries have been deprecated
Microsoft recommends you use VSTO (VIsual Studio Tools for Office) going forward.
The SOAP toolkit doesn’t work on Windows 2008 R2 Server
Microsoft’s SOAP libraries have been deprecated, but if you have applications that still want to use SOAP, there are still ways to do that.
Firstly, I found that the Microsoft SOAP libraries still work on Windows XP, Windows 7 (both 32 and 64-bit), but NOT on Windows Server 2008 R2.
They don’t seem to work on Windows Server 2008 R2 because the system can’t find the SOAP DLL’s in the registry, because the system seems to use new, different, or wrong registry keys to locate the SOAP DLL’s:
On Windows Server 2008 R2, the system looked, unsuccessfully, for this registry key:
HKCR\Wow6432Node\CLSID\{the-GUID}\InprocHandler
but on Windows 7 64-bit, where it was successful, it looks for this registry key:
HKCR\Wow6432Node\CLSID\{the-GUID}\InprocServer32.
(This was discovered using Process Monitor to watch registry activity.
How to access SOAP web services from Windows Server 2008 R2
One solution to accessing SOAP is to create a .Net SOAP client (create a project, add a web service to your SOAP endpoint; add a subroutine to call that SOAP endpoint). Then expose that as a COM object so that you can consume your newly created DLL in VBA or where-ever you want.
NOTE for developers
Visual Studio 2010 must be run As Administrator so that you can test the DLL when you run it from Visual Studio as the SoapClient is exposed as a COM Object and needs to be registered when it is built so that it can be found when it is run.
(You can also manually register it using regasm; again you must run as Administrator.)
On the client’s machine, it doesn’t matter where you put the DLL on the client’s machine but the DLL must be registered using regasm.
See Also
- The Code Snippet in Syspro’s VBScript editor – there’s a code snippet there for calling SOAP.
