2

I've written a VBScript that automates a load of processing of Excel workbooks.

For some of these workbooks, a MsgBox is triggered when they are opened. This message box is interrupting the automation.

Is there a way to listen for this MsgBox in a VBScript and "click" OK when it pops up?

Griffin
  • 13,184
  • 4
  • 29
  • 43
  • VBScript or VBA? Message boxes are designed to halt and force the user to interact. If you just want a message you will probably want to create a userform (vba) to allow your script to continue. ignore me, i though you wanted the message box :D – NickSlash Feb 21 '13 at 13:12
  • There are options, but they tend to be complex, inelegant and/or unreliable for any kind of production use. If you let us know what messagebox you're trying to get around then we might be able to offer more useful advice. – James Snell Feb 21 '13 at 13:14
  • @JamesSnell The messagebox is called from the VBA code within the sheet. – Griffin Feb 21 '13 at 13:33
  • Well, so it's your code, if you don't want the msgbox then the most elegant solution is to get rid of it in the other sheets as getting around it is going to be a world of dirty hacks. MsgBox should be banned IMHO. – James Snell Feb 21 '13 at 14:04
  • @JamesSnell, I think the whole thing is a dirty hack, unfortunately I'm not in a position to do much about it. I can't get rid of the MsgBox, but I could temporarily disable it if the VBA somehow 'knew' that it was my script that opened the workbook, and not a user. – Griffin Feb 21 '13 at 14:53
  • @Griffin - I hear you... been there, done that. :) The quickest, dirtiest method might be to use a file as a marker. If you create/copy a file into a specific name in the temp folder using vbscript, check for it in vba (showing the msgbox if the file doesn't exist) and delete it after in vbscript. That's going to be about the cleanest option I can come up with off the top of my head. – James Snell Feb 21 '13 at 15:58

1 Answers1

3

Try to use this wrapper:

Application.DisplayAlerts = False
Application.Workbooks.Open ([your code])
Application.DisplayAlerts = True

This will suppress any warnings on opening, e.g. compatibility issues, etc.

UPDATE:

In case MsgBox is generated from workbook code, try to use this:

Application.AutomationSecurity = msoAutomationSecurityForceDisable

In case Late Binding is used, this will work:

Application.AutomationSecurity = 3

Read more: http://support.microsoft.com/kb/886633

Peter Albert
  • 16,917
  • 5
  • 64
  • 88
Maks Gordeev
  • 351
  • 3
  • 16
  • Thanks for your answer, I had no luck with it – Griffin Feb 21 '13 at 13:34
  • @Griffin could you please provide relevant piece of code? Or simply in your books there are macros that run on opening and trigger msgboxes? – Maks Gordeev Feb 21 '13 at 14:03
  • good find, but unfortunately this only applies to Office for the Mac. – Griffin Feb 21 '13 at 14:53
  • 1
    @Griffin I don't think so: http://msdn.microsoft.com/en-us/library/office/bb220857%28v=office.12%29.aspx - link was just a sample. Anyway, it's a matter of seconds to try it out. – Maks Gordeev Feb 21 '13 at 17:22
  • I tried before posting, I got `Variable is underfined: 'msoAutomationSecurityForceDisable', Code:800A01F4` – Griffin Feb 21 '13 at 17:37
  • Could you please provide actual file to play with? – Maks Gordeev Feb 21 '13 at 17:54
  • @Griffin: Replace `msoAutomationSecurityForceDisable` with `3` and it should work. You're using late binding, i.e. the interpreter has no real reference to the Excel library and therefore cannot translate `msoAutomationSecurityForceDisable`. If you go to the Excel VBE's Immediate window, type `? msoAutomationSecurityForceDisable`and you'll get the underlying integer! – Peter Albert Feb 21 '13 at 20:40