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