regsvr32.exe registering your ActiveX (ocx) gives you error 0x80040200..yuck.

I design and work with a lot of c++ code and write a number of ActiveX controls. I set my machine up so I can double click the OCX and it is associated with regsvr32.exe, so it gets registered. Nice and easy, right? I have been doing it for years. Double click, whammo. It’s registered.

Now let’s come to the present. Happy go lucky , easy as pie, double click my way to registered controls, is gone. BAH! I upgraded to Windows 7, I knew this OS had to have a gotcha!

So I get regsvr32.exe blowing up and giving me error 0x80040200. That’s real helpful, doesn’t Microsoft speak any ENGLISH. So I go and look up that ridiculous error code, and lo and behold…wait for it….wait…….User Access Control. UAC said, go away child, you can’t play here.

The long and short of it, I typed cmd.exe into the run box on the start menu, right clicked cmd.exe when it appeared and did a runas for Administrator. I then did the old school command line register of the ocx. Sad.

When I get froggy enough, I will write a batch file that will have the always run as admin attributes on it so at least UAC can prompt me to elevate.

So in short, regsvr32 on Windows 7 with UAC. You must find a way to make regsvr32.exe run elevated as an Administrator for it to work.

The dreaded VBA Ignore error in Excel Automation from .NET, and how to deal with it easily….0x800AC472

I have been dealing with automating Excel for quite some time, and it is not always very friendly. It gets much much worse when automating from .NET due to the COM interactions.  The project I was working on kept getting “random” errors which after debugging where VBA Ignore errors from COM. They simply meant that Excel was busy right this second, come back later.

Well, in code, they just blew up since who would expect it to tell you that. Normal people would think the call could just wait until it wasn’t busy and continue.

I wrote a class that will help you out a bit in automating Excel. Just throw this utility into one of your base assemblies.

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;

namespace Seekford.CommonLibrary
{
    /// <summary>
    /// The delegate to use for the HandleExcelStatement inline delegates
    /// Just cast against an inline delegate.
    /// </summary>
    public delegate void ExcelAutomationStatement();

    /// <summary>
    /// Excel Helper Class for the VBA IGNORE HRESULT Error
    ///
    /// This class is designed to help minimize the coding required to handle the VBA IGNORE error that Excel can generate when it
    /// is in an unknown state. Excel does not use the proper COM notification mechanism to let the COM consumer know that Excel is in
    /// a state that the COM call will is not feasible. This state can be anything from a spell check popup to a cell being edited.
    /// It is very difficult to know when this error will occur and even occurs with no user interaction. (Possibly slow or asynchronous state changes?)
    /// Instead of writing a try loop with retry logic for every line of code that calls Excel automation, you can use this class
    /// to wrap the related lines call the HandleExcelStatement function.
    /// Brian Seekford
    /// </summary>
    /// <example>
    ///
    ///    ExcelAutomationHelperUtility.HandleExcelStatement((ExcelAutomationHelperUtility.ExcelAutomationStatement )delegate()
    ///                {
    ///                    if (askAboutMacros)
    ///                        excelApp.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityByUI;
    ///                    else
    ///                        excelApp.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityLow;
    ///                });
    ///
    /// </example>
    public sealed class ExcelAutomationHelperUtility
    {

        private static int defaultExcelAutomationVBAIgnoreMaximumRetries = 3;
        private static int defaultExcelAutomationSleepTime = 1000;
        private static bool defaultAllowUserDialog = true;
        private const string VBA_IGNORE_HRESULT = "0x800AC472"; //error code for the unknown state HRESULT. COM VBA_IGNORE flag

        /// <summary>
        /// By default, the number of times to retry before user prompt or rethrowing the error
        /// </summary>
        public static int DefaultExcelAutomationVbaIgnoreMaximumRetries
        {
            get { return defaultExcelAutomationVBAIgnoreMaximumRetries; }
            set { defaultExcelAutomationVBAIgnoreMaximumRetries = value; }
        }

        /// <summary>
        /// The number of milliseconds to wait between each retry
        /// </summary>
        public static int DefaultExcelAutomationSleepTime
        {
            get { return defaultExcelAutomationSleepTime; }
            set { defaultExcelAutomationSleepTime = value; }
        }

        /// <summary>
        /// The default value for showing the User the Retry Cancel dialog.
        /// </summary>
        public static bool DefaultAllowUserDialog
        {
            get { return defaultAllowUserDialog; }
            set { defaultAllowUserDialog = value; }
        }

        /// <summary>
        /// Processes a statement block with appropriate handling for the VBA ignore error. Uses default sleeptime, max retries, and default UI value
        /// </summary>
        /// <param name="excelStatement">Code block to execute (use an inline delegate)</param>
        public static void HandleExcelStatement(ExcelAutomationStatement excelStatement)
        {
            HandleExcelStatement(excelStatement, defaultExcelAutomationVBAIgnoreMaximumRetries, defaultExcelAutomationSleepTime, defaultAllowUserDialog);
        }
        /// <summary>
        /// Processes a statement block with appropriate handling for the VBA ignore error. Uses default sleeptime and max retries
        /// </summary>
        /// <param name="excelStatement">Code block to execute (use an inline delegate)</param>
        /// <param name="AllowUserDialog">Should the user be allowed to retry the operation?</param>
        public static void HandleExcelStatement(ExcelAutomationStatement excelStatement, bool allowUserDialog)
        {
            HandleExcelStatement(excelStatement, defaultExcelAutomationVBAIgnoreMaximumRetries, defaultExcelAutomationSleepTime, allowUserDialog);
        }

        /// <summary>
        /// Processes a statement block by executing the passed delegate.
        /// </summary>
        /// <param name="excelStatement">Code block to execute (Use a case inline delegate)</param>
        /// <param name="MaximumRetries">Number of time to rety the code statement</param>
        /// <param name="SleepTime">Total milliseconds to wait between retries</param>
        /// <param name="AllowUserDialog">Allow the user to retry the operation? Possibly letting them close and screens that may have popped up.</param>
        public static void HandleExcelStatement(ExcelAutomationStatement excelStatement, int maximumRetries, int sleepTime, bool allowUserDialog)
        {
            int retryCounter = 0; //number of time to try
            while (retryCounter < maximumRetries) //can we try again? (Could have just put a 1=1 here, but bad form).
            {
                try
                {
                    if (excelStatement != null) //if able, lets execute the code block
                        excelStatement();
                    return;
                }
                catch (Exception ex) //handle all exceptions
                {
                    retryCounter++;
                    if ((!ex.Message.Contains(VBA_IGNORE_HRESULT))) //if not our error, no retries. Just fail.
                        throw;

                    if ((retryCounter >= maximumRetries)) //if past retries
                    {
                        if (allowUserDialog) //can we ask the user to retry?
                        {
                            if (MessageBox.Show(ExcelAutomationHelperResources.ExcelUserWaitPrompt, ExcelAutomationHelperResources.ExcelUserWaitPromptCaption, MessageBoxButtons.RetryCancel, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1, 0) == DialogResult.Retry)
                            {
                                retryCounter = 0;
                                continue;                                       // reloop here so that we don't need to wait another second
                            }
                            else
                                throw;
                        }
                        else
                            throw;
                    }
                    System.Threading.Thread.Sleep(sleepTime); //wait and try again.
                }
            }
        }

        ExcelAutomationHelperUtility()
        { }
    }
}

In order to use this class in your code, just follow the example. Basically, wrap your code in the delegate handler.

Example:

     ExcelAutomationHelperUtility.HandleExcelStatement((ExcelAutomationHelperUtility.ExcelAutomationStatement )delegate()
                    {
                        if (askAboutMacros)
                            excelApp.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityByUI;
                        else
                            excelApp.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityLow;
                    });

How to write your own Windows Communication Foundation proxy

Note: This article assumes you already know how to write the WCF service. I am just giving you an example of how to talk to it without using the goofy auto-generated class.

I see a lot of developers who write WCF services like to use the auto-generated proxy classes for communicating with there service. The problem is: What if you want to add custom code to the proxies? Your changes are going to get wiped when you refresh the proxy class.

If you don’t plan on ever doing anything more complicated with your service, or you simply want to take the easy route, by all means use the auto-generated proxy from Visual Studio. This will work for most people. If you want more control, and to be able to update the proxy on your own terms, then you can use the following code and tips to write your own hand rolled proxy code.

It is still very simple to write and use, plus it looks so much cleaner.

You will need to first create your contracts, which you need no matter how you wish to write your service. The contracts for Window Communication Foundation services are your key to allowing the client application to speak to your service. It is best practice to write explicit contracts so you can separate your business entities from your data transfer objects (DTO’s), but you already knew that.

Let’s pretend that our Service Contract, the contract that represents the methods available for invocation by a client, is defined as IMyService for the sake of our example.

Since you may have multiple services, always plan for the future, you will want to write a base proxy that you can inherit from in your proxy class.

Let’s define the base proxy to be nice and simple. In the future, or a future article, you can expand the proxy to allow for adding to the message headers or utilize more advanced WCF features. Base Proxy Definition:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography.X509Certificates;
using System.Configuration;
using System.ServiceModel;
using System.Windows.Forms;

namespace PwC.Audition.Server.Proxies.ClientServices
{

    /// <summary>
    /// Use for handling some core functionality like cert handling and security
    /// </summary>
    public abstract class BaseProxyClient<TChannel> : System.ServiceModel.ClientBase<TChannel> where TChannel : class
    {
        string userId;

        // I like to tell my service what version I am, I usually throw this into the headers. But that’s for a later article.
        protected static string ClientVersion
        {
            get
            {
                return ConfigurationSettings.AppSettings["ClientVersion"];
            }
        }

        /// <summary>
        /// Creates the secured context scope. Put IMMEDIATELY into a USING statement. Must be disposed.
        /// </summary>
        /// <param name="channel">The channel.</param>
        /// <returns>Active operation context scope prefilled with security descriptors</returns>
        protected OperationContextScope CreateSecuredContextScope()
        {
            OperationContextScope scope = new OperationContextScope((IClientChannel)Channel);

            //Out of scope of this post, but I put all of my header entries here by writing to the operation scope.
            // you then add the userID and other items to the header
            return scope;

        }

        /// <summary>
        /// Inits the security information.
        /// </summary>
        private void InitSecurityInformation()
        {
            //  userId = GetCurrentUser(); simple example of storing the user id from whatever location you wish. This is then used to put into header
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="BaseProxyClient"/> class.
        /// </summary>
        public BaseProxyClient()
        {
            InitSecurityInformation();
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="BaseProxyClient<TChannel>"/> class.
        /// </summary>
        /// <param name="endpointConfigurationName">Name of the endpoint configuration.</param>
        public BaseProxyClient(string endpointConfigurationName) :
            base(endpointConfigurationName)
        {
            InitSecurityInformation();
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="BaseProxyClient<TChannel>"/> class.
        /// </summary>
        /// <param name="endpointConfigurationName">Name of the endpoint configuration.</param>
        /// <param name="remoteAddress">The remote address.</param>
        public BaseProxyClient(string endpointConfigurationName, string remoteAddress) :
            base(endpointConfigurationName, remoteAddress)
        {
            InitSecurityInformation();
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="BaseProxyClient<TChannel>"/> class.
        /// </summary>
        /// <param name="endpointConfigurationName">Name of the endpoint configuration.</param>
        /// <param name="remoteAddress">The remote address.</param>
        public BaseProxyClient(string endpointConfigurationName, System.ServiceModel.EndpointAddress remoteAddress) :
            base(endpointConfigurationName, remoteAddress)
        {
            InitSecurityInformation();
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="BaseProxyClient<TChannel>"/> class.
        /// </summary>
        /// <param name="binding">The binding.</param>
        /// <param name="remoteAddress">The remote address.</param>
        public BaseProxyClient(System.ServiceModel.Channels.Binding binding, System.ServiceModel.EndpointAddress remoteAddress) :
            base(binding, remoteAddress)
        {
            InitSecurityInformation();
        }

    }
}

Using the base proxy, we can now create our class to handle the interface of our service. Note, I included a simple template in the base proxy for adding header information. You can easily omit the InitSecurityInformation call as well as the CreateSecuredContextScope if you don’t ever plan to work with message headers. If there is enough demand, I can cover the usage of the message headers in WCF and how they can be very useful for passing information to the server more easily. The service below shows an example of a contract that has two methods. IsAlive and TotalUptime. You would implement the actual methods and interface for your particular needs. If you notice how easy it is to implement, a simple call to base.Channel.MethodName lets you call your service.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Seekford.App.Server.Contracts.MyService //<---YOUR SERVICE CONTRACT HERE
using System.Collections.ObjectModel;

namespace Seekford.App.Server.Proxies
{
    /// <summary>
    /// Proxy to utilize the My Service.
    /// </summary>
    public class MyServiceClient : BaseProxyClient<IMyService> , IMyService //<-- Implement the interface for the contract. If you don't want to implement every method, you can then remove this and not full implement all of them. Useful for exposing only a subset of your service.
    {

         #region constructors
        /// <summary>
        /// Initializes a new instance of the <see cref=" MyServiceClient "/> class.
        /// </summary>
        /// <param name="endpointConfigurationName">Name of the endpoint configuration.</param>
        public MyServiceClient(string endpointConfigurationName) :
            base(endpointConfigurationName)
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref=" MyServiceClient "/> class.
        /// </summary>
        /// <param name="endpointConfigurationName">Name of the endpoint configuration.</param>
        /// <param name="remoteAddress">The remote address.</param>
        public MyServiceClient (string endpointConfigurationName, string remoteAddress) :
            base(endpointConfigurationName, remoteAddress)
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref=" MyServiceClient "/> class.
        /// </summary>
        /// <param name="endpointConfigurationName">Name of the endpoint configuration.</param>
        /// <param name="remoteAddress">The remote address.</param>
        public MyServiceClient (string endpointConfigurationName, System.ServiceModel.EndpointAddress remoteAddress) :
            base(endpointConfigurationName, remoteAddress)
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref=" MyServiceClient "/> class.
        /// </summary>
        /// <param name="binding">The binding.</param>
        /// <param name="remoteAddress">The remote address.</param>
        public MyServiceClient (System.ServiceModel.Channels.Binding binding, System.ServiceModel.EndpointAddress remoteAddress) :
            base(binding, remoteAddress)
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="MyServiceClient"/> class.
        /// </summary>
        public MyServiceClient ()
            : base("TCP_IMyService")
        {

        }

        #endregion

        /// <summary>
        /// Determines whether this instance is alive.
        /// </summary>
        /// <returns>
        /// 	<c>true</c> if this instance is alive; otherwise, <c>false</c>.
        /// </returns>
        public bool IsAlive()
        {
            using (var scope = CreateSecuredContextScope())
            {
                return base.Channel.IsAlive();
            }
        }

        /// <summary>
        /// Totals the uptime.
        /// </summary>
        /// <returns></returns>
        public TimeSpan TotalUptime()
        {
            using (var scope = CreateSecuredContextScope())
            {
                return base.Channel.TotalUptime();
            }
        }

    }
}

Of course, you need to make sure you either setup your app.config with the connection information or you supply the server information to the constructor at run time. If adding to the configuration file, the following is a simple example of what you would need to do. I bolded the items that are pertinent. You will need to match your configuration to your needs. If you are using http for example.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <system.serviceModel>
    <bindings>
      <netTcpBinding>
        <binding name="TCP_Binding" closeTimeout="00:30:00" openTimeout="00:01:00" receiveTimeout="00:30:00" sendTimeout="00:30:00" maxBufferSize="500000000" maxReceivedMessageSize="500000000">
          <readerQuotas maxDepth="32" maxStringContentLength="2147483647" maxArrayLength="2147483647" maxBytesPerRead="2000000" maxNameTableCharCount="16384"/>
        </binding>
      </netTcpBinding>
    </bindings>
    <client>
     <endpoint address="net.tcp://MyServer:9090/seekford/1.0/MyService"   binding="netTcpBinding"
               bindingConfiguration="TCP_Binding"
               contract="Seekford.App.Server.Contracts.MyService"
               name="TCP_MyService">
        <identity>
          <dns value="localhost" />
        </identity>
      </endpoint>
   </client>
  </system.serviceModel>
</configuration>

Now that you see how simple it is to write your own clean proxy class, you will wonder why you were always using the auto generated proxies.

It is so simple and clean.

Happy coding!