Enable Macros for Excel

[erniede]

I have a series of Excel spreadsheets that I need to protect via VB Macros. Specifically, right now I'm disabling Printing. Easy to do with a VB Macro, but of course, depending on their Macro settings, a user might just disable Macros at Open, and print willy-nilly. Not good for me. So.

Currently my solution is to password protect that sheet and open it from another sheet. (Not really sure why that works, but it does...)

However, that solution means the user has to open another spreadsheet first and enable macros there. If s/he doesn't enable macros there, then it doesn't open the password protected sheet and pass it the secret password... I'm just not crazy about using a spreadsheet as a gui, would rather use AutoIt.

So I'd like to create an AutoIt script that does the same thing. I tried to open the first sheet and watch for the window "Security Warning" where you select Disable Macros/Enable Macros/More Info. But no joy -- the script never sees the window! I checked it with Au3Info, but no help, it says the same window title.

Here's the code: (and yes, I know it does no error checking atm, i'll do that later...) If you were to run this, you'll need to make an excel file as shown and make sure it has at least one VB macro in it, with Tools/Macro/Security set to Medium, so it asks what you want to do (this is the standard setting for my clients).

$FileName = "C:\openme.xls"

$oExcelDoc = ObjGet($FileName) ; Get an Excel Object from an existing filename

; need these next lines? Not sure...

$oExcelDoc.Windows(1).Visible = 1; Set the first worksheet in the workbook visible

$oExcelDoc.Application.Visible = 1; Set the application visible (without this Excel will exit?)

WinWaitActive("Security Warning") ; watching for the window to choose Disable/Enable Macros...

Send("{TAB}") ;tab key to get to Enable button...

Send("{ENTER}") ;enter key to select Enable Macros

This script never finds the window...

I've looked for COM commands for Excel, but not finding much, and I found nothing about enabling macros... I've also searched the COM libraries here for Excel and Word, and found nothing so far... did I miss something?

Thanks in advance!

[/dev/null]

I've also searched the COM libraries here for Excel and Word, and found nothing so far... did I miss something?

search for excelcom in the forums.

Cheers

Kurt

[erniede]

search for excelcom in the forums.

Cheers

Kurt

I've done a search in those posts -- there's nothing about enabling macros.

Anyone have any idea how I get a script to see that window?

Edited April 2, 2008 by erniede

[Locodarwin]

$FileName = "C:\openme.xls"

$oExcelDoc = ObjGet($FileName) ; Get an Excel Object from an existing filename

; need these next lines? Not sure...

$oExcelDoc.Windows(1).Visible = 1; Set the first worksheet in the workbook visible

$oExcelDoc.Application.Visible = 1; Set the application visible (without this Excel will exit?)

You need to tell the Excel application object that you're fine and dandy with macros. This has to happen after you create the application object but before you open the workbook document.

Try this:

$FileName = "C:\openme.xls"
$oExcelDoc = ObjCreate("Excel.Application")

$oExcelDoc.AutomationSecurity = 1 ; Explicitly set the macro security level to allow macros
$oExcelDoc.Visible = 1 ; Set the application visible (without it, this instance of Excel is running in the background, invisible)

$oExcelDoc.WorkBooks.Open($FileName)

Untested, but it should work.

Good luck with your Excel endeavors!

-S