By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,437 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

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

P: 30
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.

Expand|Select|Wrap|Line Numbers
  1. Dim fDialog As Office.FileDialog
  2.  
  3.    Dim varFile As Variant
  4.  
  5.    'Clear listbox contents.
  6.    'Me.FileList.RowSource = ""
  7.  
  8.    'Set up the File Dialog.
  9.    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
  10.  
  11.    With fDialog
  12.  
  13.       'Allow user to make multiple selections in dialog box.
  14.       .AllowMultiSelect = True
  15.  
  16.       'Set the title of the dialog box.
  17.       .Title = "Please select one or more files"
  18.  
  19.       'Clear out the current filters, and add our own.
  20.       .Filters.Clear
  21.       .Filters.Add "Access Databases", "*.MDB; *.ACCDB"
  22.       .Filters.Add "Access Projects", "*.ADP"
  23.       .Filters.Add "All Files", "*.*"
  24.  
  25.       'Show the dialog box. If the .Show method returns True, the
  26.       'user picked at least one file. If the .Show method returns
  27.       'False, the user clicked Cancel.
  28.       If .Show = True Then
  29.          'Loop through each file selected and add it to the list box.
  30.          For Each varFile In .SelectedItems
  31.             If IsNull(Me.addAttachmenttbox) Then
  32.             Me.addAttachmenttbox = varFile
  33.             Else
  34.             Me.addAttachmenttbox = Me.addAttachmenttbox & ";" & varFile
  35.             End If
  36.         Next
  37.       Else
  38.          MsgBox "You clicked Cancel in the file dialog box."
  39.       End If
  40.    End With
  41.  
Feb 14 '10 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,601
I posted only the relevant code, Lines 3 and 27 will do the trick:
Expand|Select|Wrap|Line Numbers
  1. Dim fDialog As Office.FileDialog
  2. Dim varFile As Variant
  3. Dim strFileName As String
  4.  
  5. 'Set up the File Dialog.
  6. Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
  7.  
  8. With fDialog
  9.   'Allow user to make multiple selections in dialog box.
  10.   .AllowMultiSelect = True
  11.  
  12.   'Set the title of the dialog box.
  13.   .Title = "Please select one or more files"
  14.  
  15.   'Clear out the current filters, and add our own.
  16.   .filters.Clear
  17.   .filters.Add "Access Databases", "*.MDB; *.ACCDB"
  18.   .filters.Add "Access Projects", "*.ADP"
  19.   .filters.Add "All Files", "*.*"
  20.  
  21.   'Show the dialog box. If the .Show method returns True, the
  22.   'user picked at least one file. If the .Show method returns
  23.   'False, the user clicked Cancel.
  24.   If .Show = True Then
  25.     'Loop through each file selected and add it to the list box.
  26.     For Each varFile In .SelectedItems
  27.       strFileName = Mid$(varFile, InStrRev(varFile, "\") + 1)
  28.         Debug.Print strFileName        'Testamundo!
  29.     Next
  30.   Else
  31.          MsgBox "You clicked Cancel in the file dialog box."
  32.   End If
  33. End With
Feb 14 '10 #2

P: 30
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function removeAttachements(ctlList As ListBox)
  2.  
  3.     iCount = 0
  4.  
  5.  
  6.     If ctlList.ItemsSelected.Count <> 0 Then
  7.         For i = ctlList.ListCount - 1 To 0 Step -1
  8.  
  9.             If ctlList.Selected(i) Then
  10.             MsgBox i
  11.                 ctlList.RemoveItem (i)
  12.  
  13.             End If
  14.         Next i
  15.     Else
  16.         MsgBox "Nothing was selected from the list", vbInformation
  17.         Exit Function  'Nothing was selected
  18.     End If
  19.  
  20. End Function
or
Expand|Select|Wrap|Line Numbers
  1. Public Function removeAttachements(ctlList As ListBox)
  2.     Dim oItem As Variant
  3.     Dim sTemp As String
  4.     Dim iCount As Integer
  5.  
  6.     iCount = 0
  7.     oItem = varTemp
  8.  
  9.     If ctlList.ItemsSelected.Count <> 0 Then
  10.     For Each oItem In ctlList.ItemsSelected
  11.             ctlList.RemoveItem Index:=oItem
  12.         Next oItem
  13.     Else
  14.         MsgBox "Nothing was selected from the list", vbInformation
  15.         Exit Function  'Nothing was selected
  16.     End If
  17.  
  18. End Function
Both codes are only removing one item. Is there something I am doing wrong
Feb 14 '10 #3

ADezii
Expert 5K+
P: 8,601
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?
Expand|Select|Wrap|Line Numbers
  1. Dim lst As ListBox
  2. Dim i As Integer
  3. Dim strBuild As String
  4.  
  5. Set lst = Me![ctlList]
  6.  
  7. With lst
  8.   If .ItemsSelected.Count <> 0 Then
  9.     For i = 0 To .ListCount - 1
  10.       If .Selected(i) = True Then
  11.         'do nothing, don't want it included in strBuild
  12.       Else
  13.         strBuild = strBuild & .ItemData(i) & ";"
  14.       End If
  15.     Next i
  16.   Else
  17.     MsgBox "Nothing was selected from the list", vbInformation
  18.       Exit Sub
  19.   End If
  20. End With
  21.  
  22. Me![ctlList].RowSource = Left$(strBuild, Len(strBuild) - 1)
Feb 15 '10 #4

P: 30
Hi Adezii,

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

Thank you,
Feb 15 '10 #5

ADezii
Expert 5K+
P: 8,601
You're really gonna make me work on this one, aren't you? (LOL). The comparable logic for a 3-Column List Box is:
Expand|Select|Wrap|Line Numbers
  1. Dim lst As ListBox
  2. Dim i As Integer
  3. Dim strBuild As String
  4.  
  5. Set lst = Me![ctlList]
  6.  
  7. With lst
  8.   If .ItemsSelected.Count <> 0 Then
  9.     For i = 0 To .ListCount - 1
  10.       If .Selected(i) = True Then
  11.       Else
  12.         strBuild = strBuild & .Column(0, i) & ";" & .Column(1, i) & _
  13.                                ";" & .Column(2, i) & ";"
  14.       End If
  15.     Next i
  16.   Else
  17.     MsgBox "Nothing was selected from the list", vbInformation
  18.       Exit Sub
  19.   End If
  20. End With
  21.  
  22. Me![ctlList].RowSource = Left$(strBuild, Len(strBuild) - 1)
P.S. - Make the necessary adjustments for a Column Count <> 3
Feb 15 '10 #6

P: 30
Hi Adezii

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

I had tried this
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 15 '10 #7

ADezii
Expert 5K+
P: 8,601
The pleasure is all mine, take care.
Feb 15 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.