Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
On File open - how to stop conversion of postcodes to numbers | Excel Worksheet Functions | |||
How to stop file open macro prompt after deleting all macros? | Excel Worksheet Functions | |||
How do I stop Excel from closing the open file each time I open a. | Setting up and Configuration of Excel | |||
vba just seems to stop running when I open a file... | Excel Programming | |||
Stop open file through Document Property | Excel Programming |