Excel 2007 - set macro active

[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

[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 November 11, 2009 by PsaltyDS

[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

[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

[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

[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

[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

[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

[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

[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 November 12, 2009 by 99ojo

[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 November 12, 2009 by Dizzy

[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

[99ojo]

Hi,

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

;-))))

Stefan

[PsaltyDS]

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

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

[Dizzy]

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

Hi PsaltyDS,

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

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

Greets

Dizzy