Macros disabled on application.run() in another WB

[nathansav]

HI,

I work for a company that has recently migrated from Excel 2003 to Excel 2010.

However, one Excel sheet, opens antohter, and then calls a macro in that sheet. This isnt possible because the macros are disabled. I have tried adding as a trusted document in the registry, but still nothing.

Can anyone advise?

Thanks

[:) Sixthsense :)]

Check whether this helps you

Here is the Microsoft Help Text

Workbooks.Open()

By default, macros are enabled when opening files programmatically. Use the AutomationSecurity property to set the macro security mode used when opening files programmatically.

Please Login or Register  to view this content.

Application.AutomationSecurity Property
Returns or sets an MsoAutomationSecurity constant that represents the security mode Microsoft Excel uses when programmatically opening files. Read/write.
Syntax

expression.AutomationSecurity

expression A variable that represents an Application object.

Remarks


This property is automatically set to msoAutomationSecurityLow when the application is started. Therefore, to avoid breaking solutions that rely on the default setting, you should be careful to reset this property to msoAutomationSecurityLow after programmatically opening a file. Also, this property should be set immediately before and after opening a file programmatically to avoid malicious subversion.

MsoAutomationSecurity can be one of these MsoAutomationSecurity constants.
msoAutomationSecurityByUI. Uses the security setting specified in the Security dialog box.
msoAutomationSecurityForceDisable. Disables all macros in all files opened programmatically without showing any security alerts.
Note
This setting does not disable Microsoft Excel 4.0 macros. If a file that contains Microsoft Excel 4.0 macros is opened programmatically, the user will be prompted to decide whether or not to open the file.

msoAutomationSecurityLow. Enables all macros. This is the default value when the application is started.

Setting ScreenUpdating to False does not affect alerts and will not affect security warnings. The DisplayAlerts setting will not apply to security warnings. For example, if the user sets DisplayAlerts equal to False and AutomationSecurity to msoAutomationSecurityByUI, while the user is on Medium security level, then there will be security warnings while the macro is running. This allows the macro to trap file open errors, while still showing the security warning if the file open succeeds.


Example


This example captures the current automation security setting, changes the setting to disable macros, displays the Open dialog box, and after opening the selected document, sets the automation security back to its original setting.

Visual Basic for Applications

Please Login or Register  to view this content.

[ELeGault]

Registry or Trust Center. I know I ran into an issue that when on the network, if it was not set as a 'Trusted Location' Macros were disabled regardless of trust center through Excel. Also see Enable/Disable Macros. Sounds like you have exhausted enabling options but doesn't hurt to run through just to confirm.

[nathansav]

Hi

I have added the folder that contains the file that is opened, containing the macro, to trusted locations.

I have the opening of this sheet in the workbook_open event, this still fails.

However, if i close the sheet with the macro, and the one that calls it, but leave excel open, then reopen the macro caller sheet, it all works fine!

I have this happening with registration of AddIn as reference, it errors at 1st, then re-opening it sails through.

Can anyone advise?