How to refresh the Syspro screen from VBScript

This handy line of VBScript in Syspro will refresh the screen (such as the Sales Order Lines' grid):

SystemVariables.CodeObject.ActionToInvoke = "DoRefreshLines,40004"

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

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.

How to access SOAP services from VBA in Excel or similar

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.

How to close a Syspro Window using VBScript

Function CustomizedPane_OnToolbarButton1Clicked()
    Dim WshShell
    set WshShell = CreateObject("WScript.Shell")
    WshShell.SendKeys "%{F4}"
    ' WshShell.SendKeys "{ESCAPE}" ' Can use this too
    set WshShell = nothing
End Function

Good sendkeys reference:http://www.devguru.com/technologies/wsh/quickref/wshshell_SendKeys.html

Developing .Net User Controls for Syspro - Intro

Syspro gives you the ability to house a .Net User Control inside the Syspro application, in a custom pane. (A custom pane can be housed in nearly any Window in the Syspro application, or in the Syspro main menu.) The custom-written control can then interact with your Syspro user interface, the Syspro database, and the Syspro Business Objects - so it's a very nice way to write custom solutions.

However, there are a few traps for the uninitiated:

1. You need to start with a WinForm user control because Syspro can’t run WPF user controls directly; not hard to resolve; you just add a WPF user control inside your WinForm user control.
   
2. You need to add Assembly Resolver code so that your DLL’s are loaded from ManagedAssemblies rather than BASE. See this post on the Syspro Forum for details.
   
3. You need to override the loading of configuration files as the .Net default of BASE\IMPACT.exe.config is obviously inappropriate.
   This code is good: http://stackoverflow.com/questions/6150644/change-default-app-config-at-runtime
   
4. There are some traps with calling Business Objects from .Net via Syspro's VBScript interface.
   In short, you need to:
   
   A. Avoid going over the 70-character limit, so passing XML files via temporary files is one solution.
   
   B. Avoid using spaces, new lines, or other characters, that are of significance to VBScript, in the strings you pass from .Net to Syspro's VBScript. The way to solve this is to encode all those characters as control characters before passing them to VBScript, then decode them inside the VBScript.
   
   C. You may need to pass more than two parameters to Syspro from .Net, but you only have two variables you can use. One way to solve this is to combine several parameters into one string, separating them with control characters.

Eight things every developer needs to know about SYSPRO

Syspro is highly customisable and extendable using programs you can write in C# or VB.Net, VBScript, or using web services, workflows, you name it, but there are some key things you need to know about.

1. Where does Syspro run VBScripts?

Syspro has an in-built VBScript engine that allows Syspro administrators to add custom functionality on to Sypsro. There are a few things that are useful to know about SYSPRO's VBScript engine.Firstly, it's important to know where your VBScripts run: either on the server or on the client.

VBScripts as code behind forms

VBscripts that are placed in the code behind your forms run on the Syspro client; these are triggered from user-interface events such as a form's OnLoad event or a field's OnLostFocus event.

Electronic Signatures

VBScripts that are triggered from Syspro's Electronic Signatures are run on the Syspro server.

Event Management

Event Management events are executed on the server.

Trigger Programs

Trigger Programs run other Syspro programs, but do not fire VBScripts directly themselves. They run on the client.

2. What companies are affected by a VBScript?

When you install a VBScript in SYSPRO, it applies to all companies managed by that server.

This can trap unwary users! Be warned: even if you have a test company and a live company and you install your VBScript in the test company, it will affect the live company immediately!

The correct way to handle this is to have a totally separate SYSPRO server for development and testing.

Also, you can easily limit your VBScripts to a particular company with code that uses the Company system variable, such as this example, where "T" might be the company you use for your test company:

if SystemVariables.CodeObject.Company = "T" then

    <your code>

end if

or more simply:

if SystemVariables.CodeObject.Company <> "T" then exit function

3. What users are affected by a VBScript?

When you create a VBScript, that VBScript will not only affect all companies; it will also affect all users, not just the user that created the VBScript. In other words, the script is a system-wide VBScript.

However, if you are using Roles, you will have a choice whether to edit the system-wide VBScript, or to create or edit the VBScript that will just affect the users of that Role. This is the correct method to use to limit a VBScript to one or more users.

4. What users will see changes to a menu or pane?

If you move fields around on a pane, or change their Field Properties (font, colour etc.), then only the logged-in user will see those changes next time you log in again, and you will only see them on the same computer that you logged in on, i.e. if you log in on another computer as the same user, you won't see those changes. This is because the changes are recorded in local .DAT files; these files are not copied up to the server and distributed to other computers.

However, if you are using Roles, then all people in that role will see the changes. The changes are again stored in local .DAT files, but they are copied up to the server and distributed to other users of that role.

5. What users will see new custom or scripted fields?

If you create a new custom or scripted field and add it to a pane, all users will see it on their pane.

6. Where does Syspro run reports?

Syspro runs reports using SRS, aka. Syspro Reporting Services, which uses Crystal Reports at its core.
Custom-written reports are run on the Syspro CLIENT, in Syspro 6.1. This is the case, in Syspro 6.1, regardless of which communication method you are using: Syspro Communications Service (which is built using WCF) or the CCIT communications service.

I don't yet know where STANDARD (i.e. not custom-written) Syspro reports are run, although I suspect they're run on the client.

I suspect that in Syspro 7, reports will be run on the server, but this is yet to be confirmed.

7. Where does Syspro run Workflows?

Syspro workflows can be started by a VBScript on the Syspro client, but the workflow itself runs on the server.

8. What configuration files does Syspro use?

IMPWRK.INI

On the client, when you log in, the IMPWRK.INI file is first read from the same directory that the Syspro executable file is stored in. So if you run C:\Syspro61\BASE\IMPCSC.EXE, then the first configuration file read will be C:\Syspro61\BASE\IMPWRK.INI.

IMPACT.INI

IMPWRK.INI points to a second file, IMPACT.INI, which is usually kept in C:\Syspro61\WORK\IMPACT.INI.

Sysprodb database

Syspro keeps its data in Microsoft SQL Server a database, and it has a master database, SysproDb, that it uses to map each company to its database.

ADMOPR.DAT and other *.DAT (and their *.IDX) files

Syspro keeps its list of operators in the file, ADMOPR.DAT and its associated index in ADMOPR.IDX. These two files are kept on the Syspro server, but they are copied down to the Syspro client when the client logs in.

Other .DAT files are copied to the Syspro client at log-in as well. See the tables below for more information.
The .DAT files are C-ISAM files.

Filename prior to and including Syspro 6.1	Where kept	Description	Where the information is kept in Syspro 7
ADMLSR.DAT (.idx)	Client	List view layouts and settings file	List_operator_listviewName.XML
ADMLSD.DAT (.idx)	Client	Docking control layouts	Dock­­_operator_dockingName.XML
ADMLST.DAT (.idx)	Client	Form settings	ADMLFR.DAT (.idx)
ADMPRO.DAT (.idx)	Client	Form caption sequences	Form_operator_formName.XML
ADMLAY.DAT(.idx)	Server	List view, docking, form and customized pane layouts for roles	…\settings\role_nnn\
			List_name.XML
			Form_name.XML
			Dock_name.XML
			Pane_name.TXT
ADMPRO.DAT (.idx)	Server	Group and Company caption layouts	Form_Group_group_formName.XML
			Form_Company_company_formName.XML
ADMOPR.DAT (.idx)	Server	List of Syspro Operators	ADMOPR.DAT (.idx)

How to access Syspro Business Objects from VBA in a 64-bit process

If you get this error, "ActiveX component can't create object", when you move your spreadsheet to a 64-bit environment and you're calling a Syspro Business Object, here is a solution.

Background: It is quite easy to create an Excel spreadsheet, put a button on the spreadsheet, and write a macro in VBA (Visual Basic for Applications) to pass the data into Syspro using one of Syspro's Business Objects, and so perform some business function such as creating a sales order or closing a manufacturing job; this can easily be done by using COM to access Syspro's Encore.transaction object which lives in Encore.DLL. On Windows XP and Vista and 32-bit versions of Windows 7, all runs well; you might use VBScript code like this:

Dim transaction As Object Set transaction = CreateObject("Encore.transaction") Dim XmlParameters as string Dim XmlIn as string Dim EncoreLanguageCode as string Dim EncoreLogLevel as string Dim EncoreInstance as string EncoreLanguageCode = "AUTO" EncoreLogLevel = "ldNoDebug" EncoreInstance = "EncoreInstance_0" XmlParameters = "<your xml>" XmlIn = "<your xml in>" Dim GUID as string GUID = transaction.Logon("Operator", "OperatorPassword", "CompanyId", _           "CompanyPassword", EncoreLanguageCode, EncoreLogLevel, EncoreInstance, "") Dim XmlOut as string XmlOut = transaction.Post(GUID, "SORTOI", XmlParameters, XmlIn)

However, on 64-bit versions of Microsoft Office on 64-bit versions of Windows 7 or Windows 8 or Windows Server 2008, you will get an error such as this:

ActiveX component can't create object

This is because you can no longer call Encore.DLL because 64-bit processes can't run 32-bit DLL's. So how do you solve it? Here's a couple of ways you can solve it.

Use a 32-bit version of Microsoft Office

You can install the 32-bit version of Microsoft Office on your 64-bit computer; this works OK, but isn't the preferred solution, but it could be the simplest solution.

Web services via SOAP - stuck again

You could try to call Syspro web services using Microsoft's SOAP Toolkit 3.0, BUT again, the SOAP Tookit is no longer supported by Microsoft, and there isn't a 64-bit version of it, so again, you're stuck.
See this post for more details.

Web services via a WCF proxy

In Visual Studio 2010, you can create a 64-bit DLL which you can reference the same way you'd reference Encore.DLL, but this DLL is just a proxy to call the Syspro Web Services using SOAP. To do that, create a new project in Visual Studio 2010; the output should be a Class Library, and mark the Assembly as COM-Visible (go to the project's properties / Application / Assembly Information).

Then add Service References: add the four Syspro web services (Query, Setup, Transaction and Utilities).
Create your object: here's C# code:

using System; using System.Collections.Generic; using System.Diagnostics; using System.Runtime.InteropServices; using System.ServiceModel; using Syspro.Base.SoapClient.SysproQueryServiceReference; using Syspro.Base.SoapClient.SysproSetupServiceReference; using Syspro.Base.SoapClient.SysproUtilitiesServiceReference; using Syspro.Base.SoapClient.SysproTransactionServiceReference; namespace Syspro.Base.SoapClient { [Guid("2672CDBD-F7C9-41E9-A86E-BD7034285E2E")] [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)] public interface ISoapClient { [DispId(1)] // TODO: Are these Dispatch ID's necessary? If not, remove them. void SetBaseURL(string URL); // Utility routines [DispId(2)] string Logon(string Operator, string OperatorPassword, string CompanyId, string CompanyPassword); [DispId(3)] string Logoff(string userId); [DispId(4)] string GetLogonProfile(string UserId); [DispId(5)] string Run(string UserId, string BusinessObject, string Parameter); // Transaction routines [DispId(6)] string Post(string UserId, string BusinessObject, string XmlParameters, string XmlIn); [DispId(7)] string Build(string UserId, string BusinessObject, string XmlIn); // Query routines [DispId(8)] string Query(string UserId, string BusinessObject, string XmlIn); [DispId(9)] string Browse(string UserId, string XmlIn); [DispId(10)] string Fetch(string UserId, string XmlIn); [DispId(11)] string NextKey(string UserId, string XmlIn); [DispId(12)] string PreviousKey(string UserId, string XmlIn); // Setup routines [DispId(13)] string Add(string UserId, string BusinessObject, string XmlParameters, string XmlIn); [DispId(14)] string Delete(string UserId, string BusinessObject, string XmlParameters, string XmlIn); [DispId(15)] string Update(string UserId, string BusinessObject, string XmlParameters, string XmlIn); } // Reference: http://msdn.microsoft.com/en-us/library/bb608604.aspx [Guid("7D825322-30C9-42A4-9A74-57154FC3169F")] [ClassInterface(ClassInterfaceType.None)] [ProgId("Syspro.Base.SoapClient")] public class SoapClient : ISoapClient { private string BaseURL = String.Empty; private const string utilitiesEndpointName = "/utilities.asmx"; private const string transactionEndpointName = "/transaction.asmx"; private const string queryEndpointName = "/query.asmx"; private const string setupEndpointName = "/setup.asmx"; public SoapClient() { // Must have a public default constructor so that COM clients can create the type. // Reference: http://msdn.microsoft.com/en-us/library/7fcfby2t.aspx } public void SetBaseURL(string URL) { BaseURL = URL; } #region Utility Routines public string Logon(string Operator, string OperatorPassword, string CompanyId, string CompanyPassword) { System.ServiceModel.Channels.Binding binding = new System.ServiceModel.BasicHttpBinding(); var endPoint = new EndpointAddress(BaseURL + utilitiesEndpointName); var client = new utilitiesclassSoapClient(binding, endPoint); var result = client.Logon(Operator, OperatorPassword, CompanyId, CompanyPassword, Language.AUTO, LogDetail.ldNoDebug, Instance.EncoreInstance_0, ""); return result; } public string Logoff(string userId) { System.ServiceModel.Channels.Binding binding = new System.ServiceModel.BasicHttpBinding(); var endPoint = new EndpointAddress(BaseURL + utilitiesEndpointName); var client = new utilitiesclassSoapClient(binding, endPoint); var result = client.Logoff(userId); return result; } public string GetLogonProfile(string userId) { System.ServiceModel.Channels.Binding binding = new System.ServiceModel.BasicHttpBinding(); var endPoint = new EndpointAddress(BaseURL + utilitiesEndpointName); var client = new utilitiesclassSoapClient(binding, endPoint); var result = client.GetLogonProfile(userId); return result; } public string Run(string UserId, string BusinessObject, string Parameter) { System.ServiceModel.Channels.Binding binding = new System.ServiceModel.BasicHttpBinding(); var endPoint = new EndpointAddress(BaseURL + utilitiesEndpointName); var client = new utilitiesclassSoapClient(binding, endPoint); var result = client.Run(UserId, BusinessObject, Parameter); return result; } #endregion Utilities Routines #region Transaction Routines public string Post(string UserId, string BusinessObject, string XmlParameters, string XmlIn) { BasicHttpBinding binding = new System.ServiceModel.BasicHttpBinding(); binding.ReaderQuotas.MaxStringContentLength = 2147483647; binding.MaxReceivedMessageSize = 2147483647; var endPoint = new EndpointAddress(BaseURL + transactionEndpointName); var client = new transactionclassSoapClient(binding, endPoint); var result = client.Post(UserId, BusinessObject, XmlParameters, XmlIn); return result; } public string Build(string UserId, string BusinessObject, string XmlIn) { BasicHttpBinding binding = new System.ServiceModel.BasicHttpBinding(); binding.ReaderQuotas.MaxStringContentLength = 2147483647; binding.MaxReceivedMessageSize = 2147483647; var endPoint = new EndpointAddress(BaseURL + transactionEndpointName); var client = new transactionclassSoapClient(binding, endPoint); var result = client.Build(UserId, BusinessObject, XmlIn); return result; } #endregion Transaction Routines #region Query Routines public string Query(string UserId, string BusinessObject, string XmlIn) { BasicHttpBinding binding = new System.ServiceModel.BasicHttpBinding(); binding.ReaderQuotas.MaxStringContentLength = 2147483647; binding.MaxReceivedMessageSize = 2147483647; var endPoint = new EndpointAddress(BaseURL + queryEndpointName); var client = new queryclassSoapClient(binding, endPoint); var result = client.Query(UserId, BusinessObject, XmlIn); return result; } public string Browse(string UserId, string XmlIn) { BasicHttpBinding binding = new System.ServiceModel.BasicHttpBinding(); binding.ReaderQuotas.MaxStringContentLength = 2147483647; binding.MaxReceivedMessageSize = 2147483647; var endPoint = new EndpointAddress(BaseURL + queryEndpointName); var client = new queryclassSoapClient(binding, endPoint); var result = client.Browse(UserId, XmlIn); return result; } public string Fetch(string UserId, string XmlIn) { BasicHttpBinding binding = new System.ServiceModel.BasicHttpBinding(); binding.ReaderQuotas.MaxStringContentLength = 2147483647; binding.MaxReceivedMessageSize = 2147483647; var endPoint = new EndpointAddress(BaseURL + queryEndpointName); var client = new queryclassSoapClient(binding, endPoint); var result = client.Fetch(UserId, XmlIn); return result; } public string NextKey(string UserId, string XmlIn) { BasicHttpBinding binding = new System.ServiceModel.BasicHttpBinding(); binding.ReaderQuotas.MaxStringContentLength = 2147483647; binding.MaxReceivedMessageSize = 2147483647; var endPoint = new EndpointAddress(BaseURL + queryEndpointName); var client = new queryclassSoapClient(binding, endPoint); var result = client.NextKey(UserId, XmlIn); return result; } public string PreviousKey(string UserId, string XmlIn) { BasicHttpBinding binding = new System.ServiceModel.BasicHttpBinding(); binding.ReaderQuotas.MaxStringContentLength = 2147483647; binding.MaxReceivedMessageSize = 2147483647; var endPoint = new EndpointAddress(BaseURL + queryEndpointName); var client = new queryclassSoapClient(binding, endPoint); var result = client.PreviousKey(UserId, XmlIn); return result; } #endregion Query Routines #region Setup Routines public string Add(string UserId, string BusinessObject, string XmlParameters, string XmlIn) { BasicHttpBinding binding = new System.ServiceModel.BasicHttpBinding(); binding.ReaderQuotas.MaxStringContentLength = 2147483647; binding.MaxReceivedMessageSize = 2147483647; var endPoint = new EndpointAddress(BaseURL + setupEndpointName); var client = new setupclassSoapClient(binding, endPoint); var result = client.Add(UserId, BusinessObject, XmlParameters, XmlIn); return result; } public string Delete(string UserId, string BusinessObject, string XmlParameters, string XmlIn) { BasicHttpBinding binding = new System.ServiceModel.BasicHttpBinding(); binding.ReaderQuotas.MaxStringContentLength = 2147483647; binding.MaxReceivedMessageSize = 2147483647; var endPoint = new EndpointAddress(BaseURL + setupEndpointName); var client = new setupclassSoapClient(binding, endPoint); var result = client.Delete(UserId, BusinessObject, XmlParameters, XmlIn); return result; } public string Update(string UserId, string BusinessObject, string XmlParameters, string XmlIn) { BasicHttpBinding binding = new System.ServiceModel.BasicHttpBinding(); binding.ReaderQuotas.MaxStringContentLength = 2147483647; binding.MaxReceivedMessageSize = 2147483647; var endPoint = new EndpointAddress(BaseURL + setupEndpointName); var client = new setupclassSoapClient(binding, endPoint); var result = client.Update(UserId, BusinessObject, XmlParameters, XmlIn); return result; } #endregion Setup Routines } }

Compile your object, Syspro.Base.SoapClient.DLL.

Copy Syspro.Base.SoapClient.DLL into your Syspro\Base\ManagedAssemblies folder; the ideal way to do this is to upload it using Syspro’s tool available from the main menu: Home / Customization / Customization Tools / Upload Files to the Server….

Now you need to register the DLL. Run an elevated command prompt (go to Start / All Programs / Accessories, right-click on Command Prompt, click on Run as Administrator), and type in these commands:

cd C:\Windows\Microsoft.NET\Framework64\v4.0.30319 regasm /codebase "C:\Syspro61\Base\ManagedAssemblies\Syspro.Base.SoapClient.dll"

You should get this response:

Types registered successfully

Now in your VBscript, you can call the web service using code such as this:

Dim soap as Object Dim XmlOut as string Dim XmlParameters as string Dim XmlIn as string XmlParameters = "<your xml>" XmlIn = "<your xml in>" Set soap = CreateObject("Syspro.Base.SoapClient") soap.SetBaseURL "http://www.example.com/sysprowebservices/" Dim GUID as string GUID = soap.Logon("Operator", "OperatorPassword", "CompanyId", "CompanyPassword") XmlOut = soap.Post(GUID, "SORTOI", XmlParameters, XmlIn)