CODEWITHSUNDEEP
c#.netexcelexcel-interop
Jack
Jack

Reputation: 3057

Excel Interop disable Msgbox without disabling all VBA

I'm trying to automate an excel file which has VBA in it. This VBA is protected so I can't access it.

Here is what I need the automated script to do.

  1. Open the workbook
  2. Click / dismiss any Msgbox's (Stuck part)
  3. Enter a cell and let the workbook's vba do it's thing

So I have found I can open the book without popups by using:

var app = new Excel.Application();
app.DisplayAlerts = false;
app.Visible = false;
app.EnableEvents = false;
app.Workbooks.Open(@"path...");

But then the VBA within the book is also disabled so I can't do step 3 above.

How can I just disable all Msg box's then re-enable them at the end?

Upvotes: 0

Views: 879

Answers (1)

MacroMarc
MacroMarc

Reputation: 3324

The technique that can be used is:

  1. Run the Excel code in a function on another thread. This is because there are many things that Excel can put up to block the execution, such as Msgboxes and other dialogs from Excel, and if you do not control the Excel code-behind, then you should wish to abort that Task on a timeout basis.

  2. In your main thread, just check for the completion of the task, and add a timeout too.

  3. I made the WindowHandler as a separate class with the winAPI functions from user32.dll etc from examples here: Close window via SendMessage AND here: FindWindow Function Codes

    class WindowHandler {
 [DllImport("user32.dll", SetLastError = true)]
 static extern IntPtr FindWindow(string lpClassName, string lpWindowName);

 // Find window by Caption only. Note you must pass IntPtr.Zero as the first parameter.
 [DllImport("user32.dll", EntryPoint = "FindWindow", SetLastError = true)]
 static extern IntPtr FindWindowByCaption(IntPtr ZeroOnly, string lpWindowName);

 [DllImport("user32.dll", CharSet = CharSet.Auto)]
 private static extern IntPtr SendMessage(IntPtr hWnd, UInt32 Msg, IntPtr wParam, IntPtr lParam);

 private const UInt32 WM_CLOSE = 0x0010;

 public static void CloseWindow(IntPtr hwnd) {
     SendMessage(hwnd, WM_CLOSE, IntPtr.Zero, IntPtr.Zero);
 }
 public static IntPtr FindWindow(string windowName) {
     var hWnd = FindWindowByCaption(IntPtr.Zero, windowName);
     return hWnd;
 }
 public static void CloseMsgBox() {
     CloseWindow(FindWindow("Microsoft Excel"));
 }
}

So now the code execution looks crudely like:

// The OpenExcel Action would actually be all the Excel code encapsulated into one function to run in a separate thread
Task t = Task.Run(OpenExcel); 
// Be aware that Excel can have many different popups or VBA issues which may cause execution to stall.
TimeSpan timeLimit = new TimeSpan(0, 0, 10);  // 10 secs or acceptable time limit for Excel
DateTime startTime = DateTime.Now;
while (!t.IsCompleted) {
    if (DateTime.Now - startTime > timeLimit)
        break;  //or do other exception routine, if Excel execution is taking an unacceptable amount of time!
    WindowHandler.CloseMsgBox(); //close any Msgboxes
    Thread.Sleep(200);
}

Upvotes: 1

Related Questions