How to open Locked for Editing file as Read Only?

excel vba readonly locked-files
mitchmitch24 picture mitchmitch24 · Dec 14, 2017 · Viewed 8.3k times · Source

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

dadler picture dadler · Dec 14, 2017

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.

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More