PDA

View Full Version : check if workbook open - if not open it



kellyhell
06-21-2012, 01:06 AM
I have some code that opens the "Open file" dialog box for the user to navigate to a folder to open a specific file.

The code should then confirm if the file is already in use - if it is it notifies the user, if it isn't then it opens that file.

I am having difficulty with this code and would apprecaite some help it doesn't throw up the message box if the chosen file is open or opens the file- the code is as follows - TIA

Sub Open_SMP_Template()

NewFn = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select the current STATS Return file")

If NewFn = False Then

' They pressed Cancel

MsgBox "You have pressed Cancel - no further action will be taken", vbInformation, "STATS Return"

Exit Sub

Else

Dim wBook As Workbook

'Check that worksheet is available for update.

On Error Resume Next

'(when not found ignore error and complete sub)

'if wbook not open this will throw the error

Set wBook = NewFn

If wBook Is Nothing Then 'Not open
'Do nothing
Else 'It is open
MsgBox ("It is open")
Exit Sub

End If
End If
End Sub

xld
06-21-2012, 01:20 AM
Sub Open_SMP_Template()
Dim wBook As Workbook
Dim NewFn As Variant

NewFn = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Please select the current STATS Return file")

If NewFn = False Then

' They pressed Cancel
MsgBox "You have pressed Cancel - no further action will be taken", vbInformation, "STATS Return"
Else

'Check that worksheet is available for update.
On Error Resume Next
'(when not found ignore error and complete sub)
'if wbook not open this will throw the error
Set wBook = Workbooks(Right$(NewFn, Len(NewFn) - InStrRev(NewFn, "\")))
If Not wBook Is Nothing Then
MsgBox ("It is open")
End If
On Error GoTo 0
End If
End Sub

kellyhell
06-21-2012, 02:08 AM
Thanks for that xld.

Can you advise how to open the sheet if it is closed?

TIA