Handle Alert PopUp Dialog of Macro enabled Excel File using C#

Question

I am trying to edit macro enabled Excel file using c#.

I have done with the editing and need to validate all that data using already defined macro [VBA - Button]. I am able to run macro using code mentioned below:

workbook.Application.Run("Sheet1.validate_Click");

Now, the problem is, whenever the macro is called that will validate the data which I had inserted in Excel sheet and give the output shown below:

Now I need to click on Yes Button Programmatically. which will ask to save that file which will be automatically created by that Validate Button.

Here I am stuck, that how could I programmatically click on Yes Button of Alert Dialog that appears.

Please Help me, I am without clues here, I googled a lot but could find nothing which serves my purpose.

Answer 1

As @Tom mentioned in the comments, the real solution would be to change the VBA code. But since this isn't an option that's available to you, you'll have to use some sort of hackish work-around. I haven't done much testing, but I'd assume that your COM Interop calls are also blocking because of the modal dialog.

About the only way I've come up with to handle dialogs generated by VBA from managed code is to use win32 functions to poll for the target window, then send a simulated mouse click when it's found. This is the class I use:

public class DialogClicker
{
    private delegate bool EnumWindowsProc(int hWnd, int lParam);

    private const int BM_SETSTATE = 0x00F3;
    private const int WM_LBUTTONDOWN = 0x0201;
    private const int WM_LBUTTONUP = 0x0202;

    [DllImport("user32.dll")]
    private static extern int EnumWindows(EnumWindowsProc callbackFunc, int lParam);
    [DllImport("user32.dll")]
    private static extern int EnumChildWindows(int hWnd, EnumWindowsProc callbackFunc, int lParam);
    [DllImport("user32.dll")]
    private static extern int GetWindowText(int hWnd, StringBuilder buff, int maxCount);
    [DllImport("user32.dll")]
    private static extern int SendMessage(int hWnd, int Msg, int wParam, int lParam);

    private const int MsgBufferSize = 256;
    private bool _textFound;
    private int _btnhWnd;
    private readonly Timer _timer;

    public string TargetHeader { get; private set; }
    public string ButtonText { get; private set; }
    public string SearchText { get; private set; }
    public int TimerInterval { get; private set; }

    public DialogClicker(string header, string button, string search, int interval)
    {
        TargetHeader = header;
        ButtonText = button;
        SearchText = search;
        TimerInterval = interval;
        _timer = new Timer(interval);
        _timer.Elapsed += ElapsedHandler;            
    }

    public void Toggle(bool active)
    {
        _timer.Enabled = active;
    }

    private void ElapsedHandler(object sender, ElapsedEventArgs e)
    {
        _btnhWnd = 0;
        _textFound = string.IsNullOrEmpty(SearchText);
        EnumWindows(EnumProc, 0);
    }

    private bool EnumProc(int hWnd, int lParam)
    {
        var heading = new StringBuilder(MsgBufferSize);
        GetWindowText(hWnd, heading, MsgBufferSize);
        var title = heading.ToString();

        if (string.IsNullOrEmpty(title) || !title.Equals(TargetHeader)) return true;
        EnumChildWindows(hWnd, EnumChildProc, 0);
        return false;
    }

    private bool EnumChildProc(int hWnd, int lParam)
    {
        var title = new StringBuilder(MsgBufferSize);
        GetWindowText(hWnd, title, MsgBufferSize);
        var text = title.ToString();

        if (string.IsNullOrEmpty(text)) return true;
        if (!_textFound) _textFound = text.Contains(SearchText);
        if (text.Equals(ButtonText)) _btnhWnd = hWnd;
        if (_btnhWnd <= 0 || !_textFound) return true;

        SendMessage(_btnhWnd, BM_SETSTATE, 1, 0);
        SendMessage(_btnhWnd, WM_LBUTTONDOWN, 0, 0);
        SendMessage(_btnhWnd, WM_LBUTTONUP, 0, 0);
        SendMessage(_btnhWnd, BM_SETSTATE, 1, 0);
        return false;
    }
}

Calling code:

var clicker = new DialogClicker("Microsoft Excel", "&Yes", "No error found in sheet.", 100);
clicker.Toggle(true);  //Start polling.

//Do whatever triggers the dialog.

clicker.Toggle(false); //Stop polling.