Recently, there have been several questions and much confusion concerning the Topic of Hyperlinks. Specifically, Users wanted to know how to retrieve a File Name from a FileDialog Box, copy the Name to a Bound Text Box on a Form, and save the Hyperlink to the underlying Table. The code demos below will do just that: retrieve the Absolute Path of of File from a FileDialog Box, use the Base Name (no extension) as the Display Text for the Hyperlink, then store the Display Text and Hyperlink Address in the underlying Table via the Bound Text Box. It is also important to keep in mind that a Hyperlink can consist of up to 4 parts delimited by a # sign: Display Text#Address#Sub-Address#Control Tip Text. Before we begin, a few assumptions:
- Table Name: tblSales.
- Form Name: frmWeeklySales.
- RecordSource of frmWeeklySales is tblSales.
- A Field named WeeklyData, Data Type = Hyperlink, exists in tblSales and will actually store the Hyperlink.
- The Form Field (Text Box) Bound to [WeeklyData] is named txtSalesForWeek.
- For demo purposes, the actual File name retrieved will be: C:\Invoices\2007\November\Week 1\Weekly Data for Period 11-05-07 to 11-09-07.xls.
-
Private Sub cmdPopulateHyperlink_Click()
-
'First, set a Reference to the Microsoft Office XX.X Object Library
-
-
Dim strButtonCaption As String, strDialogTitle As String
-
Dim strHyperlinkFile As String, strSelectedFile As String
-
-
'Define your own Captions if necessary
-
strButtonCaption = "Save Hyperlink"
-
strDialogTitle = "Select File to Create Hyperlink to"
-
-
With Application.FileDialog(msoFileDialogFilePicker)
-
With .Filters
-
.Clear
-
.Add "All Files", "*.*" 'Allow ALL File types
-
End With
-
'The Show Method returns True if 1 or more files are selected
-
.AllowMultiSelect = False 'Critical Line
-
.FilterIndex = 1 'Database files
-
.ButtonName = strButtonCaption
-
.InitialFileName = vbNullString
-
.InitialView = msoFileDialogViewDetails 'Detailed View
-
.Title = strDialogTitle
-
If .Show Then
-
For Each varItem In .SelectedItems 'There will only be 1
-
'Display Text + # + Address of Hyperlink (Display Text#Hyperlink Address)
-
strSelectedFile = varItem
-
strHyperlinkFile = fGetBaseFileName(strSelectedFile) & "#" & strSelectedFile
-
Me![txtSalesForWeek] = strHyperlinkFile
-
Next varItem
-
End If
-
End With
-
End Sub
-
Public Function fGetBaseFileName(strFilePath As String) As String
-
'This Function accepts the Absolute Path to a File and returns the Base File
-
'Name (File Name without the Extension)
-
-
'Make absolutely sure that it is a valid Path/Filename
-
If Dir$(strFilePath) = "" Then Exit Function
-
-
Dim strFileName As String
-
Dim strBaseFileName As String
-
-
strFileName = Right$(strFilePath, Len(strFilePath) - InStrRev(strFilePath, "\"))
-
-
strBaseFileName = Left$(strFileName, InStr(strFileName, ".") - 1)
-
fGetBaseFileName = strBaseFileName
-
End Function
NOTE: The bulk of the code relates to setting up the FileDialog Box and extracting the Base File Name. The critical lines of code for purposes of this discussion are Lines 26 to 28.
Scenario and OUTPUT:- File Name selected from FileDialog:
- C:\Invoices\2007\November\Week 1\Weekly Data for Period 11-05-07 to 11-09-07.xls
- Base File Name generated via fGetBaseFileName()
- Weekly Data for Period 11-05-07 to 11-09-07
- String copied to [txtSalesForWeek]:
- Weekly Data for Period 11-05-07 to 11-09-07#C:\Invoices\2007\November\Week 1\Weekly Data for Period 11-05-07 to 11-09-07.xls
- String displayed in [txtSalesForWeek] - (Display Text)
- Weekly Data for Period 11-05-07 to 11-09-07
- Actual value stored in Table:
- Weekly Data for Period 11-05-07 to 11-09-07#C:\Invoices\2007\November\Week 1\Weekly Data for Period 11-05-07 to 11-09-07.xls
