Phil Detail: 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)

Phil Detail

Thursday, 11 April 2013

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

Use a 32-bit version of Microsoft Office

Web services via SOAP - stuck again

Web services via a WCF proxy

No comments:

Post a Comment