How to open Locked for Editing file as Read Only?

Question

I have a macro that opens multiple files. If it comes to a file "Locked for Editing" it will give me an error saying

FileName is currently in use. Try again later.

How can I make it open said file as read only? I tried:

Workbooks.Open FileName:=Selected_EOS_Report_File, ReadOnly:=True

and

Workbooks.Open FileName:=Selected_EOS_Report_File, ReadOnly:=True, IgnoreReadOnlyRecommended:=True

Update: The first method does work. My code runs on multiple files that pass through the "Selected_EOS_Report_File" variable. At some point a file passed through that was an Excel temp file (begins the filename with "~$"). I created an if/then statement to skip over any such files.

Answer 1

Try this?

Dim wb As Workbook

Set wb = GetObject(Selected_EOS_Report_File)

wb.Open 'ReadOnly:=True (removed the readonly part)

Derived from this post: Opening .xlsx with VBA, File in Use error. Read-only not working

edit

A post here indicates a similar issue for older versions, and that if you undate to xlsx then it goes away:

https://social.technet.microsoft.com/Forums/en-US/5c9f7444-a2c7-4598-beca-21a6d5575d94/excel-file-currently-in-use

Answer 2

As far as I know, you need Notify:= True

MSDN link

Notify
If the file cannot be opened in read/write mode, this argument is True to add the file to the file notification list. Microsoft Excel will open the file as read-only, poll the file notification list, and then notify the user when the file becomes available. If this argument is False or omitted, no notification is requested, and any attempts to open an unavailable file will fail.

Answer 3

The code below worked for a similar Problem I had. This will set the ReadOnly and IgnoreReadOnlyRecommended parameters.

I tested this for Excel 365.

ReadOnly: True to open the workbook in read-only mode.

IgnoreReadOnlyRecommended: True to have Microsoft Excel not display the read-only recommended message (if the workbook was saved with the Read-Only Recommended option).

dim wbReadOnly as Workbook
Set wbReadOnly = Workbooks.Open(strXLSFileName, , True, , , , True)

link to VBA Documentation