ExcelCOM and Enable Macros warning

[Cush]

Hi,

I'm using ExcelCOM to update a spreadsheet that has macros attached. I don't want to run the macro, I just want to get rid of the dialogue box that gices you the 'Disable Macros/Enable Macros/More Info' choices, as I want to stop the user being asked this.

I don't want to reduce the security level as this will affect other spreadsheets, not just the one I'm interested in.

Any ideas how I can resolve this?

Cheers

Paul

[seandisanti]

Hi,

I'm using ExcelCOM to update a spreadsheet that has macros attached. I don't want to run the macro, I just want to get rid of the dialogue box that gices you the 'Disable Macros/Enable Macros/More Info' choices, as I want to stop the user being asked this.

I don't want to reduce the security level as this will affect other spreadsheets, not just the one I'm interested in.

Any ideas how I can resolve this?

Cheers

Paul

you can set the security level programmatically through COM, what i would suggest if you really want to make sure they're not asked, would be to set it to low, then open the workbook, then set it back to default after the sheet is open. so it only affects the security level in the time while the first page is being opened. i'll write up some code real quick.

[seandisanti]

you can set the security level programmatically through COM, what i would suggest if you really want to make sure they're not asked, would be to set it to low, then open the workbook, then set it back to default after the sheet is open. so it only affects the security level in the time while the first page is being opened. i'll write up some code real quick.

this snippet does exactly what i described. to test, change "book1.xls" to the full path and name of a workbook with macros, and give it a run. then open another book with macros (without closing the one opened programatically) and you will be prompted to enable/disable...

$oEX = ObjCreate("excel.application")
$oEX.AutomationSecurity = 1; 1 = allow, 2 = ask, 3 = deny
$oEX.workbooks.open("book1.xls")
$oEX.AutomationSecurity = 2
$oEX.visible = True

[randallc]

Hi,

I think the alternative he is asking for is simply to suppress the warning, rather than change the level;

I have added that to ExcelCom now.

[i have just opened another instance of excel for that specific purpose, and leave that open while processing other commands; revert when I complete each command. - Do you think that will work? (lets me run ExcelCom scripts on high level !)]

Best, randall

At Startup;

$oxl=ObjCreate("Excel.Application")
$oxl.DisplayAlerts = 0
$oxl.ScreenUpdating = 0

Edited June 17, 2006 by randallc

[seandisanti]

Hi,

I think the alternative he is asking for is simply to suppress the warning, rather than change the level;

I have added that to ExcelCom now.

[i have just opened another instance of excel for that specific purpose, and leave that open while processing other commands; revert when I complete each command. - Do you think that will work? (lets me run ExcelCom scripts on high level !)]

Best, randall

At Startup;

$oxl=ObjCreate("Excel.Application")
$oxl.DisplayAlerts = 0
$oxl.ScreenUpdating = 0

that actually will not work on every version of excel. at work i use office xp and that one has in the helpfile that the example i listed is the only way to do it (that's why i went that route), but i see on my home machine (with office 2k) right now that it doesn't have the same property, and setting the displayalerts to false does what he intends. so dependant on the version of excel, it has to be handled differently...

[randallc]

interesting; it all works on Win XP PRO with Office 2003 for me; I see you mean "Office XP"'; I thought you meant "Win XP" at the office and Win2K at home... Sorry.

I have never claimed ExcelCOM would work at all if not using Office 2003... I'm not bothering to support anything earlier.

Best, Randall

Randall

Edited June 17, 2006 by randallc

[nugame]

I downloaded the ExcelCOM and finally started to try to use it. I have everything else automated and working. For some reason, I am getting this message when executing:

C:\Program Files\AutoIt3\beta\Include\ExcelCom.au3 (474) : ==> "Case" statement with no matching "Select" statement.:

I thought to download again incase I accidently corrupted the ExcelCOM.au3 file. I cannot find where to download. I see your samples but not the ExcelCOM file. Also, would I be wasting my time using this file fir Office XP? thanks.

[SmOke_N]

I downloaded the ExcelCOM and finally started to try to use it. I have everything else automated and working. For some reason, I am getting this message when executing:

C:\Program Files\AutoIt3\beta\Include\ExcelCom.au3 (474) : ==> "Case" statement with no matching "Select" statement.:

I thought to download again incase I accidently corrupted the ExcelCOM.au3 file. I cannot find where to download. I see your samples but not the ExcelCOM file. Also, would I be wasting my time using this file fir Office XP? thanks.

It's in the zip:

http://www.autoitscript.com/forum/index.ph...st&id=10036

[nugame]

Thanks for the link. it is in this list, just did not look well there. I replaced the ExcelCOM and now I get these errors:

C:\PROGRA~1\AutoIt3\beta\Include\ExcelCom.au3(169,42) : ERROR: StringRegExp(): undefined function.

If StringRegExp($s_i_Column, "[0-9]", 0)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\PROGRA~1\AutoIt3\beta\Include\ExcelCom.au3(171,67) : ERROR: StringRegExpReplace(): undefined function.

$ColLettStart = StringRegExpReplace($s_i_Column, "[0-9]", "", 0)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Program Files\AutoIt3\testfiles\exceltest.au3 - 2 error(s), 0 warning(s)

I search for this StringRegExp function in the autoit directory for other include files. What's missing?

[randallc]

Just click "continue" in scite; its the error checking in scite that has been downgraded, nothing to do with autoit3 or excelcom

Best, Randall