How to Programmatically Create a Hyperlink on a Form

[ADezii]

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:

1. Table Name: tblSales.
2. Form Name: frmWeeklySales.
3. RecordSource of frmWeeklySales is tblSales.
4. A Field named WeeklyData, Data Type = Hyperlink, exists in tblSales and will actually store the Hyperlink.
5. The Form Field (Text Box) Bound to [WeeklyData] is named txtSalesForWeek.
6. 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:

1. File Name selected from FileDialog:
   

   C:\Invoices\2007\November\Week 1\Weekly Data for Period 11-05-07 to 11-09-07.xls

2. Base File Name generated via fGetBaseFileName()
   

   Weekly Data for Period 11-05-07 to 11-09-07

3. 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

4. String displayed in [txtSalesForWeek] - (Display Text)
   

   Weekly Data for Period 11-05-07 to 11-09-07

5. 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

[Celanese]

When I ran the code I got an error on:
- Line #11 - With Application.FileDialog(msoFileDialogFilePicker).

It was a:
- Run-Time error '-2147467259 (80004005)':
- Method 'FileDialog' of object'_Application' failed

[ADezii]

It's probably due to either 1 of 2 conditions:

1. You did not set a Reference to the Microsoft Office XX.X Object Library
2. You are running an earlier Version of Access that does not support the FileDialog Object

[DamePique]

Dear ADezii

I'm pretty new in this field, so the question I'd make will probably sound evident or even stupid to you.

I'm currently building a medical database containing medical files from patients. So the post you wrote about creating a hyperlink in a form would be really helpful to me in order to alleviate the tedious work of selecting the hyperlink one by one directly on the table.

I think I'm able to adapt the whole code to my database specifications, but I don't have a clue about how to set the reference to the library in the first line.
I've already identified the pathway where the MO XX.X Object Library is stored.

So I'd really appreaciate if you could provide me with the code lines I need to link the pathway of the library in order to set a correct reference.

Thank you very much in advance

[ADezii]

In any Code View Window:

1. Select Tools from the Menu Bar
2. Select References
3. Scroll down to the Microsoft Office XX.X Object Library (Version will vary)
4. Click on OK

[DamePique]

Thanks ADezii it worked perfectly! You really did my working-day!

[ADezii]

Glad it all worked out for you, DamePique.

[Glenton]

One way to get the base file name is to use the FileSystemObject. It might be a little more convenient that the fGetBaseFileName function. It uses Microsoft Scripting Runtime (Tools - References - Microsoft Scripting Runtime).

Code snippets as follows:

Dim fso As New FileSystemObject  'Add Microsoft Scripting Runtime to work with this library (Tools - References - Microsoft Scripting Runtime)
 
strSelectedFile = .SelectedItems(1)  'I tend to do this, rather than the loop
 
filename = fso.GetFileName(strSelectedFile)  'this replaces the fGetBaseFileName function
 

[Steve Hale]

Hello everyone.

I didn't mean to necro-revive this thread but, am I mistaken in believing that this code can accept more than one(1) file?

I am on Access 2007.

'The Show Method returns True if 1 or more files are selected
.AllowMultiSelect = False       'Critical Line

By changing that value to True, I was permitted to select multiple files. The code works, however, the only file that gets displayed is the last file that was selected.

Is there a way that will allow the user to select multiple files?

Thanks in advance everyone!

-Steve

[Glenton]

Hi Steve

According to this:
https://msdn.microsoft.com/en-us/lib...ffice.11).aspx

you should be able to step through all the selected items with a for loop:

For Each vrtSelectedItem In .SelectedItems
 
                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.
                MsgBox "Selected item's path: " & vrtSelectedItem
 
            Next

[Steve Hale]

Hi Glenton.

Thanks for the response.

I'll try this now and return with an update!

-Steve

[Steve Hale]

UPDATE:

After applying this suggestion, I can still select multiple items. The difference is, with the addition of the Msgbox dialog, I can confirm which files will be hyperlinked(this is a wonderful addition for confirming things).

What it's still not doing, is adding the multiple hyperlinks in the field itself.

For Each varItem In .SelectedItems 'There will only be 1
'Display Text + # + Address of Hyperlink (Display Text#Hyperlink Address)
MsgBox "Selected item's path: " & varItem
strSelectedFile = varItem
strHyperlinkFile = fGetBaseFileName(strSelectedFile) & "#" & strSelectedFile
Me![txtScans] = strHyperlinkFile
Next varItem
'If the user presses Cancel...

In the main table, can you add more than one hyperlink on one hyperlink field? All signs right now are pointing to a big "NO", so... if I can't add more than one hyperlink in a hyperlink field, can I just hyperlink to a location instead of the file itself?

[Steve Hale]

UPDATE 2

I realized that the idea of having ,multiple hyperlinks in one hyperlink field may not be feasible.

I chose the route of hyperlinking to the file location instead.

Private Sub cmdPopulateHyperlink_Click()
 
Dim strButtonCaption As String, strDialogTitle As String
Dim vrtSelectedItem As Variant
Dim strTextToDisplay As String
 
strButtonCaption = "Save Hyperlink"
strDialogTitle = "Select File to Create Hyperlink to"
 
'Use a With...End With block to reference the FileDialog object.
With Application.FileDialog(4) '4=msoFileDialogFolderPicker
 
'Allow the selection of multiple file.
.AllowMultiSelect = False
.InitialFileName = "C:\Users\" & [fOSUserName] & "\Desktop"
 
'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
 
'Step through each string in the FileDialogSelectedItems collection
For Each vrtSelectedItem In .SelectedItems
 
'vrtSelectedItem is a String that contains the path of each selected item.
'You can use any file I/O functions that you want to work with this path.
'This example simply displays the path in a message box.
strTextToDisplay = "Click here to go to location of Scans"
Me![Scans] = strTextToDisplay & "#" & vrtSelectedItem
Next vrtSelectedItem
'The user pressed Cancel.
Else
End If
End With
End Sub

Credit goes to hmarcks of UtterAccesshttp://www.utteraccess.com/forum/ind...wtopic=2019838

[Energistics]

Hello... I would like to thank you for your code it works greate! I am using it to open excel templates to create reports. Now the one thing that it is not very desirable is that it opens the Actual Template file and not an Excel .xls file, thus exposing the Main Templates to unintentional modifications by the useres. Is there a way to make it open the template as an excel file just as it would if I were to double click on it in the explorer window? Thanks in advance.