Solved: Stopping a file from being opened

[slang]

I need to have some code that prevents the workbook from being opened is another workbook is open.

I looked through some of the posts but it seems you need to know the path where the workbook is. I cant be certain where the workbook in question will be located on the users pc.

I was thinking something like this in the workbook open section

Private Sub Workbook_Open()
If IsFileOpen("????workbookfromhell.xls") Then
ThisWorkbook.Close
Else
Workbooks.Open "thisworkbook.xls"
End If

Using the function below

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long
On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function

Is there an easier way and how do I reference the file no matter where it is phisicaly located on the users machine, both the workbookfromhell and the workbook that the code is in?

Been a while......

Thanks again like always....

[GTO]

Greetings slang,

I need to have some code that prevents the workbook from being opened is another workbook is open.

I looked through some of the posts but it seems you need to know the path where the workbook is. I cant be certain where the workbook in question will be located on the users pc.

...

Is there an easier way and how do I reference the file no matter where it is phisicaly located on the users machine, both the workbookfromhell and the workbook that the code is in?

The code you have, suggests that you are wanting to see if anyone has the file opened. But... the above suggests that you may only be looking to see if the user already has the file open.

Are we looking to see if a workbook located on a network is open (by anyone), or are we looking to see if the user already has a workbook open on his machine (C drive, flashdrive, etc)?

Mark

[slang]

oops, sorry.
I knew it had to be easier than that.
Just the user please.

[GTO]

Try:

Option Explicit
    
Private Sub Workbook_Open()
Dim wbPlague As Workbook
    
    On Error Resume Next
    Set wbPlague = Workbooks("workbookfromhell.xls")
    On Error GoTo 0
    
    If Not wbPlague Is Nothing Then
        ThisWorkbook.Close False
    End If
End Sub

Hope that helps,

Mark

[slang]

Cool!
Thanks again for directing me down the right path.
I can also use the origional code to prevent users from opening a shared workbbok on the network also.

Thanks again.....