Need help getting filepath information via File Dialog  
Author Message
mikebrant





PostPosted: Visual Basic for Applications (VBA), Need help getting filepath information via File Dialog Top

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



Microsoft ISV Community Center Forums3  
 
 
ahmedilyas





PostPosted: Visual Basic for Applications (VBA), Need help getting filepath information via File Dialog Top

Have you tried this

 

Dim theOpenFileDialog as new OpenFileDialog()

theOpenFileDialog.ShowDialog()

if theOpenFileDialog.FileName.Length > 0 then

   Dim theUrl as new Uri(theFileName)

    MessageBox.Show(theUrl.ToString())

end if

this will create a proper link to the file which you can use in a hyperlink



 
 
mikebrant





PostPosted: Visual Basic for Applications (VBA), Need help getting filepath information via File Dialog Top

I am not quite clear as to the use of the code you mentioned. Would this be located within the For Each loop that I am currently using or would this be something that would entirely replace the current filedialog procedure that I have. Additionally, I do not have the OpenFileDialog() and Uri (is this suppoed to be Url ) pbjects available to me currently. I am running VBA 6.3. I am wondernig if these objects are not amongst the libraries I currently have loaded.

Thanks for help.


 
 
ahmedilyas





PostPosted: Visual Basic for Applications (VBA), Need help getting filepath information via File Dialog Top

ah! In that case you are in the incorrect forum. I will now move the thread to the correct forum. The forums you posted to originally are for VB.NET

 
 
mikebrant





PostPosted: Visual Basic for Applications (VBA), Need help getting filepath information via File Dialog Top

Hmm.. I thought I went in through the VBA forum. Thanks for putting in correct place.