Sign in to follow this  
Followers 0
erniede

Enable Macros for Excel

4 posts in this topic

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!

Share this post


Link to post
Share on other sites



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


__________________________________________________________(l)user: Hey admin slave, how can I recover my deleted files?admin: No problem, there is a nice tool. It's called rm, like recovery method. Make sure to call it with the "recover fast" option like this: rm -rf *

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by erniede

Share this post


Link to post
Share on other sites

$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

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0