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