Alerting user if file not open

[Guest]

Dear Wise Ones,

I am wanting to prompt users to open the required file if not already open.
There is some nearly-helpful answers in these posts, but I can't quite
achieve what I want.

This code of mine is useless because errorMsgs executes whether error or not.

Set rotaBk = Workbooks("EAA.xls")
rotaBk.Activate
On Error GoTo errorMsgs
errorMsgs:
MsgBox ("Open EAA.xls ")
Exit Sub


If I try to put a condition on error handling execution:

If error then
MsgBox ("Open EAA.xls " & error)
exit sub
end if

I get a "type mismatch" in the error MsgBox

As always, help most appreciated,

matilda

 

[Norman Jones]

Hi Matilda,

Try something like:

'=============>>
Public Sub Tester()
Dim rotaBk As Workbook
Const myPath As String = "C\ata\" '<<==== CHANGE

On Error Resume Next
Set rotaBk = Workbooks("EAA.xls")
On Error GoTo 0

If rotaBk Is Nothing Then
Set rotaBk = Workbooks.Open _
(Filename:=myPath & "EAA.xls")
End If
End Sub
'<<=============

 

[Guest]

Thanks for your suggestion, Norman.

I would adopt it word for word except that the path is not going to be
fixed, and the file can be in one of three folders (I have no control over
that)

Trying to help users all I can... all I can do is tell them that required
folders are not open at the outset, so they can go away, find the version
they want, open it and start again.

tried this:

On Error Resume Next
Set wbk = Workbooks("myFile.xls")
On Error Resume Next
wbk.Activate
On Error GoTo 0

If wbk Is Nothing Then
MsgBox ("Open myFile.xls and restart procedure ")
Exit Sub
End If

with the file open, and again with the file closed.
The error seems to be ignored and code resumes without the prompt if file
is actually closed.

Thanks for your patience,

Matilda

 

[Norman Jones]

Hi Matilda,

Perhaps try something like:
'=============>>
Public Sub Tester()
Dim rotaBk As Workbook
Dim FName As Variant
Const myPath As String = "C\ata\" '<<==== CHANGE

On Error Resume Next
Set rotaBk = Workbooks("EAA.xls")
On Error GoTo 0

If rotaBk Is Nothing Then
FName = Application.GetOpenFilename( _
FileFilter:="File Excel (*.xls),*.xls", _
Title:="SelectFile")

If FName <> False Then
Set rotaBk = Workbooks.Open(FName)
Else
MsgBox "No file selected!"
'do something?
End If
End If
End Sub
'<<=============

 

[Norman Jones]

Hi Matilda,

Const myPath As String = "C\ata\" '<<==== CHANGE

I should have deleted the above, now redundant, code line.

 

[Guest]

Hi Norman,

That's clever!

Opening the dialog for users to choose a file solves the problem, in a way I
hadn't thought of.

Many, many thanks.

Matilda