How to display the link to all selected files in a text box using FileDialog.AllowMul

[ChristineT]

Hello - I figured out how to code a button to browse windows explorer for files on a form in Access 2016. I opted to allow multi select because with shape files I will need to select several files. My button works, I can select multiple files, but only one shows up in the text box (named FileLocation) I created to display the link to the file. How can I display the links to all selected files on my access form?
All of these files are on shared drives. Is there a way to code to make the file name display after selecting it in Windows Explorer to do so that does not include the mapped drive letter but still link to the file? The problem is the shared drive is mapped with different letters on employee's individual computers. I need a file path to display, that whoever clicks on it will be able to open it in the shared drive regardless of what letter is used to map the shared drive.

Here is my code:

Private Sub Browse_File_Button_Click()

Dim f As Object
Dim strfile As String
Dim strfolder As String
Dim varItem As Variant

Set f = Application.FileDialog(3)
f.AllowMultiSelect = True
If f.Show Then
For Each varItem In f.SelectedItems
strfile = Dir(varItem)

strfolder = Left(varItem, Len(varItem) - Len(strfile))
MsgBox "Folder" & strfolder & vbCrLf & _
"File: " & strfile

FileLocation = strfolder & strfile
Next
End If
Set f = Nothing

End Sub

Thank you so much in advance for any assistance!

[ADezii]

As each Selected File is processed, you can write the Folder and File Name to a Listbox with certain Properties set to simulate Hyperlinks. When you dbl-click on an entry in the Listbox, you can then use the FollowHyperlink Method of the Application Object to combine the Folder and File Path and Navigate to it. If you are interested, I'll post further details.

P.S. - See attachment for Listbox View of Files selected.

[ChristineT]

Hi Adezii - Thank you much for taking the time to reply to my question. Researching an answer to my question, I have discovered I should possibly be using a listbox. My database is to track projects at work. We want to track any documents or data associated with each project. The idea is for people to go to a data form, click on a browse button to search Windows Explorer to where their file is located, have that file location stored in the database so that others can search on criteria to find data they need that others may have or to be able to compile all of the products produced for a project. I have the browser button working, I can select a file in Explorer, the file location is saved to a text box and saved to my database. The issue comes in with GIS data that usually involves multiple files to create one shape file. With this data, I need to be able to select 8 or so files at one time (which I have done with my browser button), and I have now directed those multiple files to a listbox, however I have discovered the file names are not saved in that listbox for that record in the database. My main goal is for people to be able to search for files, select them, have the file path information saved in a field in my database where the data can be queried by others to find data they need. My issue is how to save the data that has multiple files? I hope this is understandable and not too confusing. Thanks for any direction!

[ADezii]

Let's take things one step at a time. Take a look at the Attached Demo and see if it is, at least initially, what you are looking for.

[twinnyfo]

Also, I would add that you identify the actual server name for your file locations, so that driver letter registry is immaterial.

For example, instead a file path and file name looking like this:

J:\WorkingFiles\Sales\Invoices\2020-03-26-0001.pdf

Your file path and file name would look like this:

\\ServerName\SuperCorp\MechanicalDivision\WorkingFiles\Sales\Invoices\2020-03-26-0001.pdf

This may cause additional work to determine which organizations are mapped to which folders/drive letters. But, I have found in my office, that folks are no longer using the drive letters, but are jumping directly to the fully named locations. By adding shortcuts or links to the Quick Access list, there is no longer a need (in my opinion) for assigned drive letters.

Just my two cents--but I know this does not specifically address your question.