PDA

View Full Version : Solved: Stopping a file from being opened



slang
01-29-2010, 11:36 AM
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.... : pray2:

GTO
01-29-2010, 01:23 PM
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
01-31-2010, 06:11 AM
oops, sorry.:doh:
I knew it had to be easier than that.
Just the user please. :yes

GTO
01-31-2010, 06:33 AM
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
01-31-2010, 08:45 AM
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.....