Stop Macro if file is already open

[ETLahrs]

I am working with Excel files saved on a shared network drive. Each employee
has their own sheet to track errors. To prevent any other employee from
seeing the errors, I have created a macro to take the individuals sheet and
move the data to the ADMIN file which only the manager has access to view the
hidden sheets. However, I want to make a check that will stop the macro if
the ADMIN file is already open to avoid the data from not being saved and the
employee just closing out with the new data not added.

How would I go about adding this type a check and stop into the current macro.

Thanks,
Ed

 

[GTVT06]

I am working with Excel files saved on a shared network drive.  Each employee
has their own sheet to track errors.  To prevent any other employee from
seeing the errors, I have created a macro to take the individuals sheet and
move the data to the ADMIN file which only the manager has access to view the
hidden sheets.  However, I want to make a check that will stop the macroif
the ADMIN file is already open to avoid the data from not being saved and the
employee just closing out with the new data not added.  

How would I go about adding this type a check and stop into the current macro.

Thanks,
Ed

Hello Try this code. paste your code at the bottom. If ADMIN is
already open it will end the macro.

Sub test()
Dim n As Single
For n = 1 To Workbooks.Count
Workbooks(n).Activate
If ActiveWorkbook.Name = "ADMIN" Then Exit Sub
Next n
'The rest of your code
End Sub

 

[Bob Phillips]

Here is a simple function to check with

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

 

[JLGWhiz]

Maybe you can get some ideas from this site.

http://support.microsoft.com/?kbid=138621

 

[ETLahrs]

Thank you, that worked great!!!

Maybe you can get some ideas from this site.

http://support.microsoft.com/?kbid=138621