I would like to be able to retrieve the file name when using the File Dialog Property

[xraive]

Currently I only get the file path. Is there way to retrieve the file name or do I have to just use the split function.

Dim fDialog As Office.FileDialog
   
   Dim varFile As Variant

   'Clear listbox contents.
   'Me.FileList.RowSource = ""

   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   
   With fDialog
        
      'Allow user to make multiple selections in dialog box.
      .AllowMultiSelect = True
            
      'Set the title of the dialog box.
      .Title = "Please select one or more files"

      'Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Access Databases", "*.MDB; *.ACCDB"
      .Filters.Add "Access Projects", "*.ADP"
      .Filters.Add "All Files", "*.*"

      'Show the dialog box. If the .Show method returns True, the
      'user picked at least one file. If the .Show method returns
      'False, the user clicked Cancel.
      If .Show = True Then
         'Loop through each file selected and add it to the list box.
         For Each varFile In .SelectedItems
            If IsNull(Me.addAttachmenttbox) Then
            Me.addAttachmenttbox = varFile
            Else
            Me.addAttachmenttbox = Me.addAttachmenttbox & ";" & varFile
            End If
        Next
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

[ADezii]

I posted only the relevant code, Lines 3 and 27 will do the trick:

Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim strFileName As String
  
'Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
  
With fDialog
  'Allow user to make multiple selections in dialog box.
  .AllowMultiSelect = True
  
  'Set the title of the dialog box.
  .Title = "Please select one or more files"
  
  'Clear out the current filters, and add our own.
  .filters.Clear
  .filters.Add "Access Databases", "*.MDB; *.ACCDB"
  .filters.Add "Access Projects", "*.ADP"
  .filters.Add "All Files", "*.*"
  
  'Show the dialog box. If the .Show method returns True, the
  'user picked at least one file. If the .Show method returns
  'False, the user clicked Cancel.
  If .Show = True Then
    'Loop through each file selected and add it to the list box.
    For Each varFile In .SelectedItems
      strFileName = Mid$(varFile, InStrRev(varFile, "\") + 1)
        Debug.Print strFileName        'Testamundo!
    Next
  Else
         MsgBox "You clicked Cancel in the file dialog box."
  End If
End With

[xraive]

Deleting selected items from a listbox

Thank you so much Adezii. I had used the split function and passed into an array but your way is much simpler.

I don't know if i have to open another question. Talking about the same File Dialog property, once a user selects multiple files I have added the file names to a list box (value list). I would like to set something up where the user can select multiple items to be deleted from the list box. Currently whith my code it only deletes one item instead of the selected items. The multiselect property is set to extended. See below I tried two ways.

Public Function removeAttachements(ctlList As ListBox)
    
    iCount = 0
    
           
    If ctlList.ItemsSelected.Count <> 0 Then
        For i = ctlList.ListCount - 1 To 0 Step -1
            
            If ctlList.Selected(i) Then
            MsgBox i
                ctlList.RemoveItem (i)
                
            End If
        Next i
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Function  'Nothing was selected
    End If
    
End Function

or

Public Function removeAttachements(ctlList As ListBox)
    Dim oItem As Variant
    Dim sTemp As String
    Dim iCount As Integer
    
    iCount = 0
    oItem = varTemp
     
    If ctlList.ItemsSelected.Count <> 0 Then
    For Each oItem In ctlList.ItemsSelected
            ctlList.RemoveItem Index:=oItem
        Next oItem
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Function  'Nothing was selected
    End If
    
End Function

Both codes are only removing one item. Is there something I am doing wrong

[ADezii]

Dynamically removing Multiple Items from a List Box at Runtime can be tricky because of the Re-indexing of the contents. If the RowSourceType of the List Box is Value List, why not simply rebuild the Row Source of the List Box, eliminating those Items that are currently selected?

Dim lst As ListBox
Dim i As Integer
Dim strBuild As String

Set lst = Me![ctlList]

With lst
  If .ItemsSelected.Count <> 0 Then
    For i = 0 To .ListCount - 1
      If .Selected(i) = True Then
        'do nothing, don't want it included in strBuild
      Else
        strBuild = strBuild & .ItemData(i) & ";"
      End If
    Next i
  Else
    MsgBox "Nothing was selected from the list", vbInformation
      Exit Sub
  End If
End With

Me![ctlList].RowSource = Left$(strBuild, Len(strBuild) - 1)

[xraive]

listbox multiple column

Hi Adezii,

My current list box has multiple columns. How would i be able to make this work.

Thank you,

[ADezii]

You're really gonna make me work on this one, aren't you? (LOL). The comparable logic for a 3-Column List Box is:

Dim lst As ListBox
Dim i As Integer
Dim strBuild As String

Set lst = Me![ctlList]

With lst
  If .ItemsSelected.Count <> 0 Then
    For i = 0 To .ListCount - 1
      If .Selected(i) = True Then
      Else
        strBuild = strBuild & .Column(0, i) & ";" & .Column(1, i) & _
                               ";" & .Column(2, i) & ";"
      End If
    Next i
  Else
    MsgBox "Nothing was selected from the list", vbInformation
      Exit Sub
  End If
End With

Me![ctlList].RowSource = Left$(strBuild, Len(strBuild) - 1)

P.S. - Make the necessary adjustments for a Column Count <> 3

[xraive]

Thank you !!!

Hi Adezii

What can I say but just Damn you're good!

I had tried this

strBuild = strBuild & lst.Column(1, i) & ";" & lst.Column(2, i)

and I was getting an error looking at your code I see what I did wrong.

I wish there was a way I can thank you, you have saved me some time.

Time and time again you always come through.

Thank you very much. Untill next time (LOL), and don't worry you can sleep easy tonight. No more questions from me (LOL).

Thank you.

[ADezii]

The pleasure is all mine, take care.