Stop Macro if file is already open
 

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

Stop Macro if file is already open
 

On Jun 16, 9:06*am, ETLahrs wrote:
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
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

Stop Macro if file is already open
 

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


--
__________________________________
HTH

Bob

"ETLahrs" wrote in message
...
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

Stop Macro if file is already open
 

Maybe you can get some ideas from this site.

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

"ETLahrs" wrote:

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

Stop Macro if file is already open
 

Thank you, that worked great!!!

"JLGWhiz" wrote:

Maybe you can get some ideas from this site.

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

"ETLahrs" wrote:

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