VBA Open Wkbook Disabling Macros

[Karen53]

Hi,

I need to open a workbook with VBA and disable the macros. How would I do
this?

Workbooks.Open("C:\Documents and Settings\Me\Desktop\New
Workbooks\MywkBook.xls")

 

[Ron de Bruin]

If you want to stop the events from running when you open a workbook with code you can use

Application.EnableEvents = False

Code

Application.EnableEvents = True

 

[Karen53]

Hi Ron,

Thank you for the reply.

Sorry, I wasn't clear with what I needed.

The workbook being opened comes up with the security window asking to
'Enable' or' Disable Macros'. I need to select 'Disable' with vba.
Application.EnableEvents is already false. Is there a way to do this?
--
Thanks for your help.
Karen53

If you want to stop the events from running when you open a workbook with code you can use

Application.EnableEvents = False

Code

Application.EnableEvents = True

 

[Ron de Bruin]

Hi Karen

Look in the VBA help for

Application.AutomationSecurity = msoAutomationSecurityForceDisable

Then open the file

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi Ron,

Thank you for the reply.

Sorry, I wasn't clear with what I needed.

The workbook being opened comes up with the security window asking to
'Enable' or' Disable Macros'. I need to select 'Disable' with vba.
Application.EnableEvents is already false. Is there a way to do this?

 

[Karen53]

Thanks, Ron.

It works. The problem is it is stopping the macros in the calling workbook
as well. Can I stop only the macros in the newly opened workbook? Is it
possible?

 

[Karen53]

Ron,

Here's my code. Do I perhaps have it placed incorrectly?

Set wbkCopyTo = ThisWorkbook

FromwbkPath = Application.GetOpenFilename(Filefilter:="Excel Files,*.xls")

If FromwbkPath = False Then
Exit Sub 'user hit cancel
End If

Call GetNamePath(FromwbkName, FromPath, FromwbkPath)

'just the filename
wbkCopyFromName = Mid(FromwbkPath, InStrRev(FromwbkPath, "\") + 1)

On Error Resume Next

Set wbkCopyFrom = Workbooks(wbkCopyFromName)
On Error GoTo 0

If wbkCopyFrom Is Nothing Then

'save the current security setting
secAutomation = Application.AutomationSecurity
'disable the automatic Security
Application.AutomationSecurity = msoAutomationSecurityForceDisable

Set wbkCopyFrom = Workbooks.Open(FromwbkPath)

'set security back to original setting
Application.AutomationSecurity = secAutomation

On Error GoTo 0
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file--in use?"
Exit Sub
Else

 

[Dave Peterson]

This doesn't sound like excel's usual behavior to me.

If you're opening a workbook from code in another workbook's project, then excel
should already trust that workbook.

I don't have a suggestion why this is happening to you or how you can avoid it,
but something ain't normal.

If you create a couple of small workbooks and do a test, do you see the security
prompt?

Hi Ron,

Thank you for the reply.

Sorry, I wasn't clear with what I needed.

The workbook being opened comes up with the security window asking to
'Enable' or' Disable Macros'. I need to select 'Disable' with vba.
Application.EnableEvents is already false. Is there a way to do this?

 

[Karen53]

Hi Dave,

I want it to trust the opening workbook but not trust the workbook being
opened. It does work as you say, it trusts both workbooks. But, I don't
want the macros in the newly opened workbook to run. I think now it's not
possible, the macos are either on or off for all open workbooks.

Another question though, if my second workbook is already open, my code
does not process. If I have the code open the workbook, it processes. Do I
have something wrong?

 

[Karen53]

Dave,

Never mind. I found why it wouldn't process if the workbook was already open.
Thanks

 

[Dave Peterson]

Just to beat a dead horse...

Ron's suggestion of disabling events should stop any code from running in the
workbook that's being opened in code.

The only other way to run a macro in that second workbook is to explicitly call
it. And that's easy to avoid <vbg>.

Hi Dave,

I want it to trust the opening workbook but not trust the workbook being
opened. It does work as you say, it trusts both workbooks. But, I don't
want the macros in the newly opened workbook to run. I think now it's not
possible, the macos are either on or off for all open workbooks.

Another question though, if my second workbook is already open, my code
does not process. If I have the code open the workbook, it processes. Do I
have something wrong?