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

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" 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

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" wrote:

> 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" 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

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

--- "Barb Reinhardt" wrote:
> 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
>
> --- "Bob Bridges" wrote:
> > 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" 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

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" 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

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.
--
HTH,
Barb Reinhardt



"Bob Bridges" wrote:

> 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
>
> --- "Barb Reinhardt" wrote:
> > 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
> >
> > --- "Bob Bridges" wrote:
> > > 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" 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

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?
--
HTH,
Barb Reinhardt



"Bob Bridges" wrote:

> 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" 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

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

--- "Barb Reinhardt" wrote:
> 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" wrote:
> > 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" 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?

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" 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

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.

--
HTH,
Barb Reinhardt



"Bob Bridges" wrote:

> 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
>
> --- "Barb Reinhardt" wrote:
> > 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" wrote:
> > > 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" 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?