Stop Macro if file is already open


E

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
 
Ad

Advertisements

G

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
 
B

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top