If you are invoking a SYSPRO VBScript function from a .Net User Control on a Custom pane, there are a number of traps to avoid that aren’t explicitly stated in the otherwise very helpful document, Using .Net User Controls in 6.1 on the SYSPRO Support Zone.

Your delegate must be in the same namespace

Your delegate that calls doRefresh must be in the same namespace as the control linked to the customized pane. (This isn’t explicitly stated in the version of the documentation published 08 June 2010, although the converse situation is mentioned in the section, “Invoking methods on the User Control from a SYSPRO form”.)

Parameter values must be able to become valid VBScript

The value being passed as a parameter to doRefresh must compile as standard VBScript, i.e.:

if it contains any double quotes, they must be escaped as two double quotes. Even better, on the .Net side, just encode any double quotes as a non-printable character such as CTRL+C, then on the VBScript side, do the reverse.
if it contains any new lines, they must be removed.

The parameter which becomes the RefreshValue in VBScript CAN be of unlimited length (contrary to what I first wrote here).

Here is some example code for the DotNet side to safely encode double quotes and new lines:

private static string MakeSafeForVBScript(string sourceString)
{
 // The VB Script will essentially do a statement like this:
 // Dim RefreshValue : Refreshvalue = "<?xml version="1.0" encoding="Windows-1252"?>
 // However, it will give a syntax error on any embedded double quotes or new lines,
 // so convert new lines into CTRL+B,
 // and convert double-quotes into CTRL+D.

 string newLineReplacement = ((char)0x2).ToString(); // CTRL+B
 const char doubleQuoteReplacement = (char)0x4; // CTRL+D
 const char doubleQuote = '"';

sourceString = sourceString.Replace(doubleQuote, doubleQuoteReplacement).Replace("\r\n", newLineReplacement);

 return dotNetVariable;

}

Here is an example VBScript code snippet; it puts the double-quotes and new lines back:

Function Unencode (encodedString)
 Dim control_B
 Dim control_D
 Dim doubleQuote
 Dim newLine
 Dim result

 control_B = Chr(2)
 control_D = Chr(4)
 doubleQuote = Chr(34)
 newLine = chr(13) & chr(10) ' \r\n, i.e. CTRL+M CTRL+J.
 encodedString = Replace(encodedString, control_D, doubleQuote)

 Unencode = Replace(encodedString, control_B, newLine)

End Function

GetApplicationInfo and GetSysproInfo don’t always return pure XML

Note that returned XML from GetApplicationInfo or GetSysproInfo does NOT always contain valid XML, because it sometimes contains embedded double quotes, so you have to escape those quotes before passing the returned value into an XML parser.

E.g. the returned XML may contain a fragment like this:

<DiagnosticAppPath Value="""C:\SYSPRO61\BASE\IMPACT.EXE"""/>

This needs to become (note the backslashed quotes):

<DiagnosticAppPath Value="\"C:\SYSPRO61\BASE\IMPACT.EXE\""/>

You can use the following C# code to sort out the embedded quotes:

String tripleQuotes = @"""""""";
String singleQuoteBackslashedQuote = @"""";
var fixedQuotes = systemVariables.Replace(tripleQuotes, singleQuoteBackslashedQuote);

Remove XML NameSpace declarations

Also note that Syspro’s Business Objects called from SYSPRO’s VBScript doesn’t like XML that has namespace declarations; see How to use XSD2Code for Syspro Business Objects for more details. (Calls to SYSPRO’s Business Objects via Web Services or DCOM don’t seem to have this problem.)

Conclusion

After sorting these things out, you should be ready to run.

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.

Phil Detail

Monday, 21 April 2014

How to invoke a SYSPRO VBScript function from a .Net User Control