Need help getting filepath information via File Dialog

[Guest]

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.
DoCmd.SetWarnings False
DoCmd.OpenQuery ("ClearTempFields")
DoCmd.SetWarnings True
Me.FileName.SetFocus
Me.FileName = ""

' Turn Warnings Off
DoCmd.SetWarnings False

' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' 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.Clear
.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.SetFocus
Me.FileName.Text = vrtselecteditem
Me.Requery
Me.Repaint
Call BatchTechReviewUpload(vrtselecteditem)
Next vrtselecteditem
Else
MsgBox "You clicked Cancel in the file dialog box."
DoCmd.SetWarnings True
Exit Sub
End If

End With

MsgBox "All files have been uploaded."
DoCmd.Close acForm, "BatchUploadFileBrowserForm"
DoCmd.SetWarnings False
DoCmd.OpenQuery "ClearTempFields"
DoCmd.SetWarnings True

End Sub

 

[Douglas J. Steele]

Try navigating to where you want to be through My Network Places, rather
than selecting through the mapped drive.

 

[Dirk Goldgar]

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.

You may be able to adapt the code posted here:

http://www.devx.com/vb2themax/Tip/18960
GetUNCName - Convert a file path to a UNC path

I haven't tried it.

By the way, these lines:

Me.FileName.SetFocus
Me.FileName.Text = vrtselecteditem

can in all probability be replaced by the one line:

Me.FileName = vrtselecteditem

You don't need to set the focus to an Access text box in order to set
its Value property (which is its default property).

 

[Guest]

The problem is that I won't have control over how the individual user
navigates to the file that is to be uploaded. What I am trying to achieve
(and it currently works) is for the user to navigate to a file, information
gets uploaded from the file to the database, and the hyperlink to the file is
also stored in the databse base such that any user can click on the hyperlink
and open the original file. The only part of this that I am having a problem
with is forcing the hyperlink value that is added to be the actual shared
drive path instead of a user-specific filepath that could change depending on
how the user has the drive mapped (or if they have it mapped at all). It is
actually I problem I didn't realize a had, as I don't havfe this drive mapped
on my computer and during development, I thought I was getting the true
filepath names that I was looking for. As a few users have started using the
system, I have noticed that the saved hyperlinks vary based on the user's
drive mapping scheme.

Thanks for your feedback.

 

[Douglas J. Steele]

Dirk's approach is probably more applicable then. You might also look at
http://www.mvps.org/access/api/api0003.htm at "The Access Web"

 

[Dave Rowland]

When I found this thread I realized the guest was seeking the same solution as me, but it never got resolved. I finally nailed it, so I'm posting my VBA code here. This sub opens a file picker, and will return the full UNC path when searching a network share, otherwise it'll return the mapped drive path. It then saves the path for the chosen file to a clickable hyperlink text box on a MS Access subform.

Private Sub cmdPickFile_Click()
'Author: Dave Rowland
'Date added: 12/2/2016
Const msoFileDialogFilePicker As Long = 3
Dim objDialog As Object, strPath As String, strFileName As String

Set objDialog = Application.FileDialog(msoFileDialogFilePicker)
With objDialog
.Title = "Please select a file for this account"
.AllowMultiSelect = False
.Show
If .SelectedItems.Count = 0 Then
MsgBox "No file selected."
Else
strPath = .InitialFileName
strFileName = Dir(.SelectedItems(1))
Me.hypFilePath.Value = strFileName & "#" & strPath & strFileName
End If
End With
End Sub