I am wondeirng if there is a way to have the file dialog box (or something similar) return a URL value from the selected file. Basically the issue I am having is that I am trying to add hypertext links to files stored on a shared network drive into an access table. Currently, using a pretty basic file dialog approach, the hyperlink text will be based on the file path as defined for each user. In other words, if the user has the network drive mapped as the G drive, the hypertext value returned is "G:\\..." or if the user does not have the drive mapped at all, the network-assigned URL for the drive is returned (i.e. \\DriveName\...). The latter (the user-independent URL) is actually what I desire to have returned as the value, so as to make the hyperlink viable regardless of who the user is. The code I am currently using is (where the Call function references a module that uploads data from the specified file and writes the FileName data, the file path, to the destination table):
Private Sub FileBrowser_Click()
' This requires a reference to the Microsoft Office 11.0 Object Library.
Dim fDialog As Office.FileDialog
Dim vrtselecteditem As Variant
' Clear the list box contents.
Me.FileName = ""
' Turn Warnings Off
' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
' Allow the user to make multiple selections in the dialog box.
.AllowMultiSelect = True
' Set the title of the dialog box.
.Title = "Select Files to be Uploaded"
' Clear out the current filters, and then add your own.
.Filters.Add "Excel Spreadsheets", "*.XLS"
' 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 that is selected and then add it to the list box.
For Each vrtselecteditem In .SelectedItems
Me.FileName.Text = vrtselecteditem
MsgBox "You clicked Cancel in the file dialog box."
MsgBox "All files have been uploaded."
DoCmd.Close acForm, "BatchUploadFileBrowserForm"
Microsoft ISV Community Center Forums2