FWIW, this "design flaw" just started for me in the last month. I never had
to add the automation security part until recently. Of course, this means
modifying a bunch of code that opens workbooks because I'd propogated it a
bit.

I just realized that some code that's used by MANY in my organization may
have this same problem, now that presumably microsoft has helped us out
again.
--
HTH,
Barb Reinhardt



"Bob Bridges" wrote:

Ok, I just twigged. I was experimenting with different security settings; if
I set it to AutomationSecurityLow, the macro works as expected (ie it
continues execution after the Open). With it set to
AutomationSecurityForceDisable, we have the problem we're having. Then I
tried it with AutomationSecurityByUI.

The workbook started to open, and Excel warned me that it had macros. Did I
want to allow them?

Somewhat at random I chose the "Disable Macros" option.

It stopped running OpenWorkbook.

I still had to think about it a few seconds, but then I got it. Apparently
when you set Application.AutomationSecurity to
msoAutomationSecurityForceDisable, Excel doesn't take note of the new setting
immediately. If the new workbook has no macros, Excel STILL doesn't notice
the new Application.AutomationSecurity setting. But if the new workbook has
macros in it, then it pauses to look at the Application.AutomationSecurity
setting -- and if it's set to ...ForceDisable, even the current macro is
disabled!

This strikes me as a design flaw: If you're opening a workbook (or any
other Office document) by VBA and that document has a Workbook_Open macro,
how can you keep it from running? Maybe there's some other way.

--- "Barb Reinhardt" wrote:
OK, what am I missing folks. I'm using this to open a workbook as ReadOnly
and so I can edit it at a different time. The workbook I'm trying to open
has VBA code in it and I'm trying to open as Read Only. I've recently had
to add the automation security lines so I don't get a message to enable
macros when I open a workbook that contains them. I don't have the caps
lock key pressed either. If myReadOnly = FALSE, it opens the workbook and
execution ends.

I've done this this way in another workbook and it worked fine.

FWIW, I'm also having issues saving a powerPoint presentation that I used to
be able to save programmatically. Could my laptop have gremlins?

Option Explicit
Sub OpenWorkbook(myWB As Excel.Workbook, myReadOnly As Boolean)
Dim sFile As String
Dim ShortName As String
Dim autoSecurity As MsoAutomationSecurity

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", myXLFilter
.FilterIndex = 1
.Title = "Please Select File to open"
If .Show = False Then Exit Sub
sFile = .SelectedItems(1)
End With

ShortName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))

Set myWB = Nothing
On Error Resume Next
Set myWB = Workbooks(ShortName)
On Error GoTo 0

If myWB Is Nothing Then
autoSecurity = Application.AutomationSecurity
If myReadOnly Then
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Set myWB = Workbooks.Open(sFile, ReadOnly:=True)
Application.AutomationSecurity = autoSecurity
Else
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Set myWB = Workbooks.Open(sFile)
Application.AutomationSecurity = autoSecurity
End If
Debug.Print myWB.Name

Else
'No action
End If

End Sub