Jump to content
Sign in to follow this  
Dizzy

Excel 2007 - set macro active

Recommended Posts

Dizzy

Hi togehter,

i want to run macros in excel 2007 but they are disabled by default.

Here --> MSDN <-- i found a possible solution.

VBA-Code

Sub Security()
    Dim secAutomation As MsoAutomationSecurity

    secAutomation = Application.AutomationSecurity

    Application.AutomationSecurity = msoAutomationSecurityForceDisable
    Application.FileDialog(msoFileDialogOpen).Show

    Application.AutomationSecurity = secAutomation

End Sub

But i don't know how to translate this to COM (i'm still a nobody in COM :) )

Thx

Dizzy

Share this post


Link to post
Share on other sites
PsaltyDS

You could try this (not tested):

#include <Excel.au3>

Global Const $XL_msoAutomationSecurityLow = 0x1 ; Allow all macros
Global Const $XL_msoAutomationSecurityByUI = 0x2 ;  As set in the Trust Center UI
Global Const $XL_msoAutomationSecurityForceDisable = 0x3 ; Disable macros (default)

$oExcel = _ExcelBookAttach("Title of Excel Window", "title")

; Save old value
$AutomationSecurity_Save = $oExcel.AutomationSecurity

; Change it
$oExcel.AutomationSecurity = $XL_msoAutomationSecurityLow

:)

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
Dizzy

You could try this (not tested):

#include <Excel.au3>

Global Const $XL_msoAutomationSecurityLow = 0x1 ; Allow all macros
Global Const $XL_msoAutomationSecurityByUI = 0x2 ;  As set in the Trust Center UI
Global Const $XL_msoAutomationSecurityForceDisable = 0x3 ; Disable macros (default)

$oExcel = _ExcelBookAttach("Title of Excel Window", "title")

; Save old value
$AutomationSecurity_Save = $oExcel.AutomationSecurity

; Change it
$oExcel.AutomationSecurity = $XL_msoAutomationSecurityLow

:)

Hi PsaltyDS,

thanks for your help. This looks very good.

I didn't get an error, when i open my excel-file but i can't run my macro.

Where is my mistake? ;)

Greets

Dizzy

Activate_Macros3.au3

NEW.zip

Share this post


Link to post
Share on other sites
99ojo

Hi PsaltyDS,

thanks for your help. This looks very good.

I didn't get an error, when i open my excel-file but i can't run my macro.

Where is my mistake? :)

Greets

Dizzy

Hi,

i think you have to set the security settings before you open the excel sheet with the included macro.

I test it with Excel2003 and a simple Macro. Open Sheet with security high settings -> i'm not able to run makro, even by change the settings to medium or low. Only by changing settings and restart Excel i was able to run the macro.

;-))

Stefan

Share this post


Link to post
Share on other sites
Dizzy

Hi Stefan,

thanks for reply, but the problem is that the error handler says:

"Cannot run the macro 'Tabelle1.delete_values'. The macro may not be available in this workbook or all macros may be disabled."

But as you can see - the macro is inside ... :)

Dizzy

Share this post


Link to post
Share on other sites
99ojo

Hi Stefan,

thanks for reply, but the problem is that the error handler says:

"Cannot run the macro 'Tabelle1.delete_values'. The macro may not be available in this workbook or all macros may be disabled."

But as you can see - the macro is inside ... :)

Dizzy

Hi,

or all macros may be disabled.

This is the same message i got, if i change the security settings in the open sheet.

You have to open excel (not your sheet with the macro), change security settings, open your excel sheet with the macro, start your macro, reset security settings and close excel.

;-))

Stefan

Share this post


Link to post
Share on other sites
Dizzy

Hi,

This is the same message i got, if i change the security settings in the open sheet.

You have to open excel (not your sheet with the macro), change security settings, open your excel sheet with the macro, start your macro, reset security settings and close excel.

;-))

Stefan

Hi Stefan,

thanks for your help!

Can you have a look to my script? I think, i've done it as you said, but:

We intercepted a COM Error !

err.description is: Cannot run the macro 'Tabelle1.delete_values'. The macro may not be available in this workbook or all macros may be disabled.

err.windescription: ?รข???

err.number is: 80020009

err.lastdllerror is: 0

err.scriptline is: 61

err.source is: Microsoft Office Excel

err.helpfile is: C:\Program Files\Microsoft Office\Office12\1033\XLMAIN11.CHM

err.helpcontext is: 0

We intercepted a COM Error !

err.description is: Cannot run the macro 'Tabelle1.delete_values'. The macro may not be available in this workbook or all macros may be disabled.

err.windescription: Unknown name.

err.number is: 80020006

err.lastdllerror is: 0

err.scriptline is: 28

err.source is: Microsoft Office Excel

err.helpfile is: C:\Program Files\Microsoft Office\Office12\1033\XLMAIN11.CHM

err.helpcontext is: 0

+>11:28:54 AutoIT3.exe ended.rc:0

+>11:28:55 AutoIt3Wrapper Finished

>Exit code: 0 Time: 4.845

??? :)

Dizzy

Activate_Macros4a.au3

Share this post


Link to post
Share on other sites
99ojo

Hi Stefan,

thanks for your help!

Can you have a look to my script? I think, i've done it as you said, but:

??? :)

Dizzy

Hi,

your code is working fine with Excel 2003. I think, it must be a problem with Excel2007.

Sry, I haven't installed 2007, so i can't help anymore.

;-((

Stefan

Share this post


Link to post
Share on other sites
Dizzy

Hi,

your code is working fine with Excel 2003. I think, it must be a problem with Excel2007.

Sry, I haven't installed 2007, so i can't help anymore.

;-((

Stefan

Damn! Thanks for your help Stefan.

Anybody who can help me out of this hole? :)

Dizzy

Share this post


Link to post
Share on other sites
99ojo

Damn! Thanks for your help Stefan.

Anybody who can help me out of this hole? :)

Dizzy

Hi,

maybe this link is helpful:

http://www.pcreview.co.uk/forums/thread-3301246.php

You find more links to MS Sites as well in that post.

;-))

Stefan

Edited by 99ojo

Share this post


Link to post
Share on other sites
Dizzy

Hi Stefan,

thanks for the link, but i think this is the false direction.

If i start my script and hold the excel-file open i can manually start my vba.

AutoIT can't find the macro named "delete_values".

Or am i on a wrong way?

Dizzy

Edit: I set the registry setting of

AccessVBOM to 1

and

VBAWarnings to 1

with no effect. Everything should function now, but i get the same error:

Cannot run the macro 'Tabelle1.delete_values'. The macro may not be available in this workbook or all macros may be disabled.

Damn! Damn! Damn! Grrrr ...

Dizzy

Edited by Dizzy

Share this post


Link to post
Share on other sites
Dizzy

I don't know why .... :)

This code works ... ;)

#include <Excel.au3>

$filename = "E:\NEW.xlsm"
$oExcel = ObjCreate("Excel.Application") ;crete excel object
$oExcel.WorkBooks.Open($FileName)        ;open desired file containing the macro
$oExcel.Run("delete_values")             ;run the macro
$oExcel.save
$oExcel.Quit

Now i have to implement this into my script and ...

Maybe this is helpful for others.

Thanks to you folks!

Dizzy

Share this post


Link to post
Share on other sites
99ojo

Hi,

i thought about this, but i don't wanted to shoot into the dark.

;-))))

Stefan

Share this post


Link to post
Share on other sites
PsaltyDS

I don't know why .... ;)

This code works ... B)

#include <Excel.au3>

$filename = "E:\NEW.xlsm"
$oExcel = ObjCreate("Excel.Application") ;crete excel object
$oExcel.WorkBooks.Open($FileName)        ;open desired file containing the macro
$oExcel.Run("delete_values")             ;run the macro
$oExcel.save
$oExcel.Quit

Now i have to implement this into my script and ...

Maybe this is helpful for others.

Thanks to you folks!

Dizzy

Does that mean you never had a problem with macro perms, but just in recognizing the macro?

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
Dizzy

Does that mean you never had a problem with macro perms, but just in recognizing the macro?

:huggles:

Hi PsaltyDS,

sorry for my very late answer! I didn't have a look at this post for a long time :D

This was my first attempt to use a excel macro from autoit. So - yes i never had a problem before :.

Greets

Dizzy

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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.