Suppress 'File In Use' Dialogue

Question

I have written a procedure which loops through a large number of .xlsx files on a SharePoint server and aggregates data from each into a master file.

My problem is that at any given time, the individual files may be checked out for editing by another user, producing this message:

I need a VBA solution to use the default "View a read-only copy" option, and UNCHECK the "Receive a notification when the server file is available" option.

Answer 1

Using some of the standard options in the Workbooks.Open method should get you to the point wherw you can open a read-only instance without notification.

Sub open_wbro()
    Dim wb As Workbook, fn As String

    fn = "c:\temp\myWorkbook.xlsx"

    on error goto bm_WB_Open_Error
    Set wb = Workbooks.Open(FileName:=fn, ReadOnly:=True, _
                            IgnoreReadOnlyRecommended:=True, _
                            Notify:=False)
    goto bm_Exit

bm_WB_Open_Error:
    If CBool(Err.Number) Then
        Debug.Print Err.Number & " - " & Err.Description
        Err.Clear
    End If

bm_Exit:
    wb.Close
    Set wb = Nothing
End Sub