How do I open an Excel-workbook in read-only mode without a user-prompt-CodePudding

I use the VBA code below to open a workbook but I get a user-prompt when the workbook is already-opened by another user asking if I would like to open in read-only mode. I'd like to open the workbook in read-only mode directly without the read-only user prompt.

Sub Report()

    Dim fd As FileDialog
    Dim Filechosen As Boolean
    Dim Savebutton As Boolean
    Dim sh As Worksheet
    Dim book As Workbook
    
    
    Set fd = Application.FileDialog(msoFileDialogOpen)
    
    fd.Filters.Clear
    fd.Filters.Add "Old Excel Files", "*.xls"
    fd.Filters.Add "New Excel Files", "*.xlsx"
    fd.Filters.Add "macro Excel Files", "*.xlsm"
    fd.Filters.Add "any Excel Files", "*.xl*"
    
    fd.FilterIndex = 4
    
    fd.AllowMultiSelect = False
    
    fd.InitialFileName = "https:sharepoint address/BFs/"
        
    Filechosen = fd.Show
    

    If Not Filechosen Then
        MsgBox " No File Selected"
        Exit Sub
    End If
        
    fd.Execute
    
    
    
For Each sh In Worksheets: sh.Visible = True
  
Next sh




End Sub

CodePudding user response：

When you are giving the command to open the file, it has an optional command to open it in readonly

Just mention it as Readonly:=True

It will not prompt the user

Let me know if you need further clarification

CodePudding user response：

You can open the workbook yourself using the VBA workbooks.open method once you get the filename from the dialog.

Replace in your code as needed. You'll get the idea

Dim xlFileName As String
Dim wb as Workbook

Set fd = Application.FileDialog(msoFileDialogOpen)

With fd
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1

      If .Show Then
           xlFileName = .SelectedItems(1)                   
      Else
           MsgBox " No File Selected"
           Exit Sub
      End If

End With

    wb.Workbooks.Open (xlFileName), ReadOnly:=True

You can then copy the worksheets in that workbook from the wb.worksheets collection.