Check if file is already open

[Rasm]

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.



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

[littleiitin]

1:

In a Module:


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 (http://www.vbaexpress.com/kb/getarticle.php?kb_id=468)

2:

If In sheet1 from Range("A1")

Paste below Data:

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

On form:


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




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

[Rasm]

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



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

[Rasm]

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.

[littleiitin]

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

[Rasm]

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