Handling Excel dialog boxes

[jlegan]

I have written an enterprise level utility whose sole purpose in life is to seek out Excel and Visio documents and alter their paper sizes based on criteria specified in a configuration file (per my customers request).

The utility performs surprisingly well on Office XP and Office 2003. In Office XP and above, there is a method called EnableEvents (True / False) that determines whether or not macros fire. For obvious reasons, when the program is going through 10,000+ files, you do not want the macros executing.

However, on Office 2000, this method does not exist. As a result, you cannot prevent the macros from executing. Furthermore, if you try and trick the system by raising the security level of Excel to high for macros temporarily, the macros still execute as the documents are opened programmatically and as such inherit the trust given to the application that called it. This is a bad design and I have already submitted a request to change this behavior in future versions of Office as the behavior exists to this day.

I have been working an SRX with MS Dev's for a few weeks now on a solution to this problem and every solution we have come up with has hit one wall or another.

So I had a new thought that I want to test, but I have never done anything like it so I need some help getting started. The one document in particular that is causing problems is a time card that has several dialog box prompts. Now, regardless of what you answer (Y, N, Cancel, etc...), as long as you navigate through them, my program continues to execute.

What I would like to do is handle the dialog boxes and pass values to them. I have done some reading up on SendKeys, but I am unsure on how to implement them in my code.

Keep in mind that this one particular document has 3 "y/n" / OK dialog boxes in it so I need to be able to handle multiples.

Thank you for any assistance you can provide.

Jim

[RobDog888]

Moved from Classic VB forum.

[RobDog888]

Have you tried using .DisplayAerts ? It will "turn off" the msgbox's for warnings etc. but the only drawback is that it will select the default button on the alert if it was actually displayed, except in one case with the save as dialog box. The default is Yes when displayed but No when not displayed. This could make it difficult if that is one you need.

[jlegan]

Rob,

The tool sets the following before opening the first Excel file.

xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.EnableEvents = False ' Works on Office XP and above
xlApp.Visible = False

Here is the bug with Office 2000, there is a KB article on the topic.

When display alerts is set to False, the first line of an embedded macro does not fire. However, the next line flips the value to true. As a result, the next dialog box / warning will be displayed without hesitation.

We pursued the Excel Add-On route last week (myself and Microsoft) and I truly believe the only viable solution at this point is to handle the dialog boxes myself.

Thanks,

Jim

[RobDog888]

Yes, so if your needing to dismiss the alerts then your going to have to use APIs to programmatically click the choice you want. Check out FindWindow, FindWindowEx, and SendMessage APIs.