Followers 0

# ExcelCOM and Enable Macros warning

## 10 posts in this topic

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

##### Share on other sites

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.

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

##### Share on other sites

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 1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz #### Share this post ##### Link to post ##### Share on other sites #4 · Posted (edited) 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 by randallc

##### Share on other sites

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... 1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz #### Share this post ##### Link to post ##### Share on other sites #6 · Posted (edited) 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 by randallc #### Share this post ##### Link to post ##### Share on other sites 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. Dr SherlockAlways a way #### Share this post ##### Link to post ##### Share on other sites 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 [center]Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.[/center] #### Share this post ##### Link to post ##### Share on other sites 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?

Dr SherlockAlways a way