Check and list names of all workbooks that already open by another user

Hi,

I am trying to build a macro that gives me a list of all workbooks that are currently open by another user in a given directory. I want to be able to loop through every file in a given directory (the directory only contains .xlsx files anyway), check to see if it's already open (i.e. in read-only state) and then list the name of it.

Here's what I've come up with so far, but it's giving me some problems:

Sub ListAlreadyOpenFiles()

Dim MyFolder As String
Dim MyFile As String
Dim j As Single

MyFolder = "R:\Spreadsheets"
MyFile = Dir(MyFolder & "\*.xlsx")

j = 1

Do While MyFile <> ""

j = j + 1

Set MyWorkBook = Workbooks.Open(MyFile)

If MyWorkBook.ReadOnly Then
    
    ActiveWorkbook.Close
    Range("A1").Cells(j, 1) = Workbooks(j).Name
    
Else

Workbooks(MyFile).Close False
MyFile = Dir

End If

Loop

End Sub

Any help would be appreciated! Thanks.