Check if file is already open

I use the code below to open up my XLSX file - How can I check if the file is already open - I keep track of the names of the files in a two column multi-select Listbox - column 0 is the name of the file and column 1 is the path to the file.

Code:

---

For ii = 0 To LBDataWorkbook.ListCount - 1
        If LBDataWorkbook.Selected(ii) Then
              Workbooks.Open FileName:=LBDataWorkbook.List(ii, 1) & LBDataWorkbook.List(ii)
      End if
Next ii

---

Also - How do you add file headers in a listbox - I can set the property to on - but cannot figure how to get the text into the headers

1:

In a Module:

Code:

---

Function IsFileOpen(FileName As String)
    Dim iFilenum As Long
    Dim iErr As Long
   
    On Error Resume Next
    iFilenum = FreeFile()
    Open FileName For Input Lock Read As #iFilenum
    Close iFilenum
    iErr = Err
    On Error GoTo 0
   
    Select Case iErr
    Case 0:    IsFileOpen = False
    Case 70:  IsFileOpen = True
    Case Else: Error iErr
    End Select
   
End Function

---

Refrence: Here

2:

If In sheet1 from Range("A1")

Paste below Data:

FileName Address Status
abc.xlsm C:\XYZ
pqr.xls C:\XYZ

On form:

Code:

---

Private Sub cmdSubmit_Click()
    Dim lngCounter  As Long
    Dim strAddress  As String
    For lngCounter = 0 To lstFileAddress.ListCount - 1
        strAddress = lstFileAddress.List(lngCounter, 1) & "\" & lstFileAddress.List(lngCounter, 0)
        If lstFileAddress.Selected(lngCounter) Then
            If Not IsFileOpen(strAddress) Then
                Range("TempRange").Find(lstFileAddress.List(lngCounter, 0)).Offset(, 2).Value = "Close"
            Else
                Range("TempRange").Find(lstFileAddress.List(lngCounter, 0)).Offset(, 2).Value = "Open"
            End If
        End If
    Next
    Unload Me
End Sub

---

Code:

---

Private Sub UserForm_Initialize()
    Range("A1").CurrentRegion.Offset(1).Resize(Range("A1").CurrentRegion.Rows.Count - 1, Range("A1").CurrentRegion.Columns.Count - 1).Name = "TempRange"
    With lstFileAddress
        .ColumnHeads = True
        .ColumnCount = 2
        .RowSource = "TempRange"
        .MultiSelect = fmMultiSelectMulti
        .ListStyle = fmListStyleOption
    End With
End Sub

---

Littleiitin

In respect to item 2 - I have tested your code - works great - But is there a way to place a text string into the header of the Listbox - I basically fill the values into mylistbox using the FileDialog - so it is a list of files that I then add to the List box with the .additem property for the first column (name of excel file) and .list(i,1) is the path of that file. But I would like to add the headers "FileName" & "Filepath" without reading a range or region from my sheet.
Below is my code to get the selection of files that I then add into the listbox

Code:

---

Sub UseFileDialogOpen(ByRef SltFile() As String, ByVal MuliSelect As Boolean, ByRef strPath As String)
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = strPath
    End With
    With Application.FileDialog(msoFileDialogOpen)
        If MuliSelect Then
                .AllowMultiSelect = True
            Else
                .AllowMultiSelect = False
        End If
        .Show
        ' Display paths of each file selected
        ReDim SltFile(.SelectedItems.Count)
        For i = 1 To .SelectedItems.Count
            SltFile(i - 1) = .SelectedItems(i)
        Next i
    End With
End Sub

---

Attachment 156

Littleiitin

I have now extracted code that illustrate what I am trying to do - figured that was easier for you - see attachment - simply click the commandB with the 3 little dots - now select a folder that has Excel files - do a multiselect. The first listbox is now being populated - but no header - hehehe - if you now select from the first listbox it will open that sheet and populate the second listbox - again - I am trying to get headers added. You can scroll the listboxes to see the second column - contains the path.
Your help is much appreciated
Thanks
Rasm


PS Ignore my references to a default_settings sheet - that is where I store the last selections by the user - this is really an add-in - but I saved it as a XLSM file. I save my last selection that way rather than writing to the registry - do you have comments on that - it actually works well. As you can see I am a hacker.

Hi Rasm,


In listbox there is no option to insert string in header. There is only one way to make header.

In your Case I suggest, Make headers false and top of the list boxes using labela you can give the headers.

or
in place of "List Boxes" use "List View"

Good to know that you are Hacker.:o

Thanks
Rahul

Rahul
Thanks - I was using Listview - but switched as Listview is not stable. But thank you anyway.
Rasm