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
Need help getting URL information via File Dialog
MartinAlvarez
Simonth
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
hellomcv
Stuman99
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.