File.ReadAllLines() fails to read from a file that is opened by Excel

Question

Say I open a file in Excel, I know I cannot write anything to it as it will be "locked" by Excel.

But can I read it? Or that's not possible as well?

I'm using the following code:

If System.IO.File.Exists(file) Then
    output = System.IO.File.ReadAllLines(file).ToList
    If unique Then
        output = output.Distinct.ToList
    End If
Else
    output = New Generic.List(Of String)
End If

How to make it work?

Can I open the file read-only in Excel? Will that work?

Answer 1

First of all, you need to be aware of the following points:

• Whenever a file is open by a process/thread, the process/thread can either access the file for reading only, writing only, or for both. Check the FileAccess Enum for more info.
• Also, the process/thread can specify whether the access to the file is shared or not (e.g., shared for reading only, for writing only, for both, or no shared access at all). Check the FileShare Enum for more.
• If the other process doesn't share access to the file at all, then you wouldn't be able to access the file whether it's for reading or writing.

Now AFAIK, Excel does share the file access for reading, (but it doesn't share for writing). So, in order to be able to access the file while it's open by Excel, you need to do the following:

• Open the file for reading only (since you don't have access to write).
• Allow access to the file for both reading and writing since the other process (i.e., Excel) needs to have both.

The thing is, although File.ReadAllLines() opens the file for reading only, it does not share the access to the file for writing (only for reading). To clarify more, File.ReadAllLines() uses a StreamReader internally ¹, which --also internally-- uses a FileStream with the following values by default: ²

New FileStream(path, 
               FileMode.Open,
               FileAccess.Read,     ' This is good.
               FileShare.Read,      ' This is the problem (see the code below).
               ' ...

Which works unless the file is open by another process that requires write access to the file. Therefore, you need to create a FileStream and set the appropriate values for FileAccess and FileShare Enums. So, your code should look something like this:

Dim output As New List(Of String)
If IO.File.Exists(file) Then
    Using fs As New IO.FileStream(file,
                                  IO.FileMode.Open,
                                  IO.FileAccess.Read,       ' Open for reading only.
                                  IO.FileShare.ReadWrite)   ' Allow access for read/write.
        Using reader As New IO.StreamReader(fs)
            While Not reader.EndOfStream
                Dim currentLine As String = reader.ReadLine()
                If unique AndAlso output.Contains(currentLine) Then Continue While
                output.Add(currentLine)
            End While
        End Using
    End Using
End If

Hope that helps.

---

References:

¹ InternalReadAllLines() source.

² StreamReader internal constructor source.