How to open a file in the recent list as read-only

[simr]

In excel by pressing Ctrl-O, I can see the open dialog with two tabs "recent" and "pinned". if I right click on any item in them, I can see "open" and "open a copy", but no open readonly. To open read-only I have to click browse and navigate to the right folder, locate the file and there is the read-only option. Is there anyway to open read-only from the recent list directly?

 

[Alex Blakenburg]

Is there anyway to open read-only from the recent list directly?

It doesn't appear that way. There are quicker ways to achieve what you want though.

Option 1)
You seem to have backstage view enabled so you could.
• Right click on the file in recent files and select "Copy path to clipboard"
• Click on Browse on the left hand pane
• Ctrl+V
• Open Read-Only

Option 2)
Open the file as copy
This opens the file with a "1" added to the end of the file.
Since the file is now a new file, Ctrl+S or Save will open up the "Save As" dialogue box, so you can't accidentally save it over the top of the original file.

 

[Logit]

The following functions as required here. Assign a shortcut key to the macro and run the macro from there.

Option Explicit

Sub OpenRecentFileReadOnlyNew()
    Dim RecentFilesList As String
    Dim SelectedIndex As Variant
    Dim FilePath As String
    Dim FileName As String
    Dim i As Integer
    Dim TotalFiles As Integer
    Dim ValidFiles As Collection
    Dim FileCount As Integer

    ' Initialize a collection to hold valid file paths
    Set ValidFiles = New Collection
    
    ' Get the total number of recent files Excel tracks
    TotalFiles = Application.RecentFiles.Count
    
    ' Check if there are any recent files
    If TotalFiles = 0 Then
        MsgBox "No recent files available.", vbExclamation
        Exit Sub
    End If
    
    ' Create a list of valid recent file names (excluding paths)
    For i = 1 To TotalFiles
        ' Check if the file path is valid and not empty
        If Len(Application.RecentFiles(i).Name) > 0 Then
            On Error Resume Next
            ' Try to get the file name using Dir (check for valid file)
            FileName = Dir(Application.RecentFiles(i).Name)
            If Len(FileName) > 0 Then
                ' Add the valid file to the collection
                ValidFiles.Add Application.RecentFiles(i).Name
            End If
            On Error GoTo 0
        End If
    Next i
    
    ' If no valid recent files, show a message and exit
    If ValidFiles.Count = 0 Then
        MsgBox "No valid recent files available.", vbExclamation
        Exit Sub
    End If
    
    ' Build the prompt list for the InputBox
    RecentFilesList = "Select a file to open as read-only:" & vbCrLf
    For i = 1 To ValidFiles.Count
        FileName = Dir(ValidFiles(i)) ' Get the file name with extension
        RecentFilesList = RecentFilesList & i & ". " & FileName & vbCrLf
    Next i
    
    ' Use InputBox to get the user's choice
    SelectedIndex = InputBox(RecentFilesList, "Open Recent File Read-Only")
    
    ' Exit if the user cancels or provides no input
    If SelectedIndex = "" Then
        Exit Sub
    End If
    
    ' Convert the selection to a number and attempt to open the file
    On Error Resume Next
    FilePath = ValidFiles(CInt(SelectedIndex)) ' Get the valid file path
    If Err.Number <> 0 Or FilePath = "" Then
        MsgBox "Invalid selection. Please choose a valid file number.", vbExclamation
        Err.Clear
        On Error GoTo 0
        Exit Sub
    End If
    
    ' Open the file in read-only mode
    Workbooks.Open FilePath, ReadOnly:=True
    If Err.Number <> 0 Then
        MsgBox "Unable to open the file. It may have been moved or deleted.", vbExclamation
        Err.Clear
    End If
    On Error GoTo 0
End Sub

 

[Alex Blakenburg]

@Logit - "Dir" doesn't like it if the file is on OneDrive. I assume it will be the same issue for a Sharepoint file.
Run time error 52. Bad file name or number.

 

[Logit]

Strange. My files here are on OneDrive (on the computer). Are you referring to a different 'OneDrive' ?

 

[Alex Blakenburg]

Interesting, the file path when it crashed for me was of the http variety. I am about to log off for the night and am probably not going to get a chance to retest it for a while. Hopefully it works for the OP.