Open File, Read-Only Warning

[nniedzielski]

I am running a macro that will open a file, add a few lines of data, save the file and close it and continue running.

However with 2-3 users using this file, when the macro runs its possible the file will open Read-Only, and will cause issues trying to save.

Is there a way that when it runs when the file opens, the user will receive a message telling them that the file is Read-Only and they need to tell the other users to get out of the file so they can continue?

thanks,

Dim thisdate As Date
    Dim x As Workbook
    Dim y As Workbook
    Dim FileY As String
    Dim j As Long
    Dim i As Long
    Dim k As Long
    Dim r As Variant
    Dim Found As Range
    Dim NewPath As String, OldFile As String, NewFile As String
   
    thisdate = Date
    FileYear = Year(thisdate)
 
    FileY = [COLOR=#000000]"[/COLOR][URL="file:///\\USBTRWDCN9K9TW1\MENLO\Main%20Billing%20Desk%20Reports\Seal%20Logs\"][COLOR=#000000]\\USBTRWDCN9K9TW1\Reports\Seal Logs\[/COLOR][/URL]" & FileYear & "\Seal Log " & FileYear & ".xlsx"
    Select Case True
        Case Len(Dir(FileY)) > 0
            Workbooks.Open FileY
        Case Len(Dir("J" & Right(FileY, Len(FileY) - 1))) > 1
            FileY = "J" & Right(FileY, Len(FileY) - 1)
            Workbooks.Open FileY
        Case Len(Dir("I" & Right(FileY, Len(FileY) - 1))) > 1
            FileY = "J" & Right(FileY, Len(FileY) - 1)
            Workbooks.Open FileY
        Case Else
    End Select
       
    If Not Dir(FileY, vbDirectory) = vbNullString Then
        Set y = Workbooks.Open(FileY)
            
    Else
        MsgBox "File Not Found", 48, "File Not Found"
    Exit Sub
      
    End If

 

[Logit]

.
This resource identifies the Before / Close / Save aspect. But, you could edit that to the Workbook Open event or even for both.

One way of accomplishing your goal:

Auto notify or mail when excel sheet updated. [Solved]

 

[nniedzielski]

this is not what i am looking for, I need the macro to stop working if the file is already open Read-Only.

 

[V_Malkoti]

You should be able to use ReadOnly property of the workbook to check.

If y.ReadOnly Then
    msgbox "The workbook is already opened by a different user. Please try again later."
    Exit Sub
End If

 

[Logit]

[h=3]RESOURCE LINK : Test if Folder, File or Sheet exists or File is open

Test if file is open[/h]Below are two examples to test if the file "book1.xlsm" is open. The second example use a function(UDF), the advantage of a UDF is that all macros in your workbook can call this function and if you copy the UDF in a add-in all your open workbooks can use the UDF. Less code in your macros.

Sub Test_If_File_Is_Open_1()
    Dim TestWorkbook As Workbook

    Set TestWorkbook = Nothing
    On Error Resume Next
    Set TestWorkbook = Workbooks("Book1.xlsm")
    On Error GoTo 0

    If TestWorkbook Is Nothing Then
        MsgBox "The File is not open!"
    Else
        MsgBox "The File is open!"
    End If

End Sub

Do not forget to copy the function below the macro if you use the example below.

Sub Test_If_File_Is_Open_2()
    If bIsBookOpen("Book1.xlsm") Then
        MsgBox "The File is open!"
    Else
        MsgBox "The File is not open!"
    End If
End Sub

Function bIsBookOpen(ByRef szBookName As String) As Boolean
[COLOR=black]' Rob Bovey[/COLOR]
    On Error Resume Next
    bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Note: if you have more then one Excel instance open and want to test if the workbook is open
in one of them then look at the code in this KB article.
http://support.microsoft.com/?kbid=138621
See also this page from Chip Pearson
Test If A File Is Open

 

[nniedzielski]

v_malkoti, where would i apply this? at the end?

 

[V_Malkoti]

You can add this right after the Workbooks.Open() statement, or before the code block that you do not want to be executed if the workbook is read only.