The other day a customer had a problem. He wanted to use this VBScript code to trigger macros in Microsoft Excel workbooks. The trouble is that his company’s Excel security settings are set so that macros in non-signed Excel workbooks pop up a warning dialog.
The customer has to click a button to enable macros (or press the ‘E’ key). This dialog holds up the VBScript code until the button is clicked.
He didn’t want to have to digitally sign every single workbook and he wasn’t allowed to change the security settings.
Now, with regular Macro Scheduler code you could start Excel and send keystrokes to it to run the macro and therefore you could have the script send keystrokes to the warning dialog. But if you are using Excel’s ActiveX interface to run the macro via VBScript, the VBScript gets held up by that dialog.
The solution is to use an “asynchronous process” which does nothing but waits for the dialog to appear and then hits the button (sends the ‘E’ key). This process is launched just before the VBScript code that runs the macro is called. Then, although the main script is held up, the other script can cancel the warning, allowing the main script to continue.
We can do this by creating another macro and compiling it to an executable:
WaitWindowOpen>Security Warning Wait>0.5 Press ALT Send>e Release ALT
We compile that code to an executable and then have our main script launch it – without waiting for it to complete – before calling the VBScript code:
VBSTART
Sub RunXLMacro(xlfile,macro)
Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(xlfile)
xlApp.visible = True
xlApp.Run macro
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
VBEND
//Launch the dialog cancel process
Run>%SCRIPT_DIR%\ClearWarning.exe -HIDE -NOSYSTRAY
VBRun>RunXLMacro,C:\Documents\MyWorkBook.xls,SampleMacro
Another way to do this would be to use Macro Scheduler’s Triggers. Instead of writing and launching a second macro, just create another macro and under Macro Properties specify a Window Open trigger. This macro would then detect the warning dialog and clear it. But some benefits of the second “asynchronous” macro process are that you only run it when you need it (you might WANT to have to manually clear the warning dialog when using Excel manually) and it is also portable – you can use this technique with compiled macros that you distribute, and Macro Scheduler does not need to be installed.
Now, as stated earlier, we could have coded the whole thing – open Excel and launch the macro – using regular Macro Scheduler code and sending keystrokes, instead of using VBScript, therefore avoiding the problem. But VBScript is a nice way to run Excel macros, and I’m sure there are other scenarios where the “asynchronous process” technique may be useful.
