What am I missing - Workbooks.Open

[Barb Reinhardt]

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?

Thanks,

Barb Reinhardt


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

 

[Bob Bridges]

Ok, I've looked at your code, and I've reread your message...but what's the
question? I mean, I see "what am I missing?" so I know something isn't
working right. But what does it do when you run this logic?

 

[Barb Reinhardt]

If myReadOnly = FALSE, it opens the workbook and execution ends.

It's not supposed to do that! I'm not sure if I have issues if myReadOnly =
TRUE

Barb Reinhardt

 

[Bob Bridges]

Oh, I thought you meant that as part of the program description, not as the
problem. Ok, let me take a closer look at this program...So in the
simplified program as you have it here, the difference is that if myReadOnly
is true the code executes the Debug.Print myWB.Name statement, and if
myReadOnly is false you get no DebugPrint line, right? That's the only
difference?

While you're confirming that I'm going to try it myself. By the way, I
offer this simplification for the last section:

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

 

[Bob Bridges]

By the way, there's something I don't understand about this code: The
calling routine is supposed to pass it a workbook object, which (as far as I
can see) cannot exist unless the workbook has been opened, right? I mean,
the calling routine can pass a file NAME - but not the object itself, without
first opening it.

Yet OpenWorkbook gets the name of that object, then checks to see whether
the workbook is already open, and if not tries to open it. How can the
workbook not already be open if its object is being passed as an argument? I
don't see that this has anything to do with your problem, I just don't
understand what's happening here.

 

[Barb Reinhardt]

I started out with that simplification, but was having difficulties, so split
it out because I wasn't sure if it would work. Either way, I have problems.

 

[Barb Reinhardt]

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

'sFile is the file path (I believe) of the selected file

ShortName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))
'Short name is Workbook.xls

'If the workbook is open, it sets it as myWB
Set myWB = Nothing
On Error Resume Next
Set myWB = Workbooks(ShortName)
On Error GoTo 0

'if it's not open, it opens it
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


Does that help?

 

[Bob Bridges]

I just looked at it and realized my mistake. But there's still a problem
with myWB (and it still seems to have nothing to do with your problem); no
matter what you put in the first argument, OpenWorkbook isn't going to pay
any attention to its contents; it's going to change it to Nothing and then to
the workbook the user chooses.

(Belatedly) Is that what you intended? If this is a way to pass the
workbook object back to the calling routine, it didn't occur to me; I'm used
to writing a Function for that:

Function OpenWorkbook(myReadOnly As Boolean) as Excel.Workbook

Set OpenWorkBook = Nothing
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xls"
.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, "\"))
On Error Resume Next
Set OpenWorkBook = Workbooks(ShortName)
On Error GoTo 0

If Not OpenWorkBook Is Nothing Then Exit Sub
autoSecurity = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable
If myReadOnly _
Then Set OpenWorkBook = Workbooks.Open(sFile, ReadOnly:=True) _
Else Set OpenWorkBook = Workbooks.Open(sFile)
Application.AutomationSecurity = autoSecurity
Debug.Print OpenWorkBook.Name
End Function

 

[Bob Bridges]

Hmm, seems to be doing the same thing with me. That may let you out of the
gremlin hypothesis, unless I have the same ones. I'll experiment a bit.

 

[Barb Reinhardt]

1) I've not used a function, but I supposed I could.
2) I've used something like this for a long time, so I know it works, or
used to. I define myWB in this snippet of code. It's not previously defined
until the code is called.

My problem, as I stated in the first post, is when I open the file where
myReadOnly = FALSE (and maybe true, but I'm not sure), EXECUTION ENDS. It
stops. No more, nada, nothing. The cursor shows up in a module in the
workbook that's opened. That's it. It doesn't continue.

 

[Barb Reinhardt]

I'm thinking I may have turned off macros with SecurityForceDisable. I see
other code I've used recently that has SecurityLow, but I'm getting a similar
problem with that. Something seems to be buggered here.

 

[Bob Bridges]

My copy stops when the 2nd arg is TRUE; I assume it will when it's false too.
Let's see...yep, either way. It works fine when the target workbook has no
macros. I have a few more experiments to try.

 

[Bob Bridges]

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]

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.