By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,123 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

How to Programmatically Create a Hyperlink on a Form

ADezii
Expert 5K+
P: 8,607
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPopulateHyperlink_Click()
  2. 'First, set a Reference to the Microsoft Office XX.X Object Library
  3.  
  4. Dim strButtonCaption As String, strDialogTitle As String
  5. Dim strHyperlinkFile As String, strSelectedFile As String
  6.  
  7. 'Define your own Captions if necessary
  8. strButtonCaption = "Save Hyperlink"
  9. strDialogTitle = "Select File to Create Hyperlink to"
  10.  
  11. With Application.FileDialog(msoFileDialogFilePicker)
  12.   With .Filters
  13.     .Clear
  14.     .Add "All Files", "*.*"     'Allow ALL File types
  15.   End With
  16.   'The Show Method returns True if 1 or more files are selected
  17.     .AllowMultiSelect = False       'Critical Line
  18.     .FilterIndex = 1 'Database files
  19.     .ButtonName = strButtonCaption
  20.     .InitialFileName = vbNullString
  21.     .InitialView = msoFileDialogViewDetails     'Detailed View
  22.     .Title = strDialogTitle
  23.   If .Show Then
  24.     For Each varItem In .SelectedItems 'There will only be 1
  25.       'Display Text + # + Address of Hyperlink (Display Text#Hyperlink Address)
  26.       strSelectedFile = varItem
  27.       strHyperlinkFile = fGetBaseFileName(strSelectedFile) & "#" & strSelectedFile
  28.         Me![txtSalesForWeek] = strHyperlinkFile
  29.     Next varItem
  30.   End If
  31. End With
  32. End Sub
Expand|Select|Wrap|Line Numbers
  1. Public Function fGetBaseFileName(strFilePath As String) As String
  2. 'This Function accepts the Absolute Path to a File and returns the Base File
  3. 'Name (File Name without the Extension)
  4.  
  5. 'Make absolutely sure that it is a valid Path/Filename
  6. If Dir$(strFilePath) = "" Then Exit Function
  7.  
  8. Dim strFileName As String
  9. Dim strBaseFileName As String
  10.  
  11. strFileName = Right$(strFilePath, Len(strFilePath) - InStrRev(strFilePath, "\"))
  12.  
  13. strBaseFileName = Left$(strFileName, InStr(strFileName, ".") - 1)
  14.   fGetBaseFileName = strBaseFileName
  15. 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:
    Expand|Select|Wrap|Line Numbers
    1. 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()
    Expand|Select|Wrap|Line Numbers
    1. Weekly Data for Period 11-05-07 to 11-09-07
  3. String copied to [txtSalesForWeek]:
    Expand|Select|Wrap|Line Numbers
    1. 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)
    Expand|Select|Wrap|Line Numbers
    1. Weekly Data for Period 11-05-07 to 11-09-07
  5. Actual value stored in Table:
    Expand|Select|Wrap|Line Numbers
    1. 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
Nov 26 '07 #1
Share this Article
Share on Google+
13 Comments


P: 2
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
Dec 2 '09 #2

ADezii
Expert 5K+
P: 8,607
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
Dec 2 '09 #3

P: 12
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
Nov 8 '11 #4

ADezii
Expert 5K+
P: 8,607
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
Nov 8 '11 #5

P: 12
Thanks ADezii it worked perfectly! You really did my working-day!
Nov 9 '11 #6

ADezii
Expert 5K+
P: 8,607
Glad it all worked out for you, DamePique.
Nov 9 '11 #7

Expert 100+
P: 391
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:

Expand|Select|Wrap|Line Numbers
  1. Dim fso As New FileSystemObject  'Add Microsoft Scripting Runtime to work with this library (Tools - References - Microsoft Scripting Runtime)
  2.  
  3. strSelectedFile = .SelectedItems(1)  'I tend to do this, rather than the loop
  4.  
  5. filename = fso.GetFileName(strSelectedFile)  'this replaces the fGetBaseFileName function
  6.  
Nov 11 '14 #8

P: 4
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.

Expand|Select|Wrap|Line Numbers
  1. 'The Show Method returns True if 1 or more files are selected
  2. .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
Sep 1 '15 #9

Expert 100+
P: 391
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:

Expand|Select|Wrap|Line Numbers
  1. For Each vrtSelectedItem In .SelectedItems
  2.  
  3.                 'vrtSelectedItem is a String that contains the path of each selected item.
  4.                 'You can use any file I/O functions that you want to work with this path.
  5.                 'This example simply displays the path in a message box.
  6.                 MsgBox "Selected item's path: " & vrtSelectedItem
  7.  
  8.             Next
Sep 1 '15 #10

P: 4
Hi Glenton.

Thanks for the response.

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

-Steve
Sep 1 '15 #11

P: 4
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.

Expand|Select|Wrap|Line Numbers
  1. For Each varItem In .SelectedItems 'There will only be 1
  2. 'Display Text + # + Address of Hyperlink (Display Text#Hyperlink Address)
  3. MsgBox "Selected item's path: " & varItem
  4. strSelectedFile = varItem
  5. strHyperlinkFile = fGetBaseFileName(strSelectedFile) & "#" & strSelectedFile
  6. Me![txtScans] = strHyperlinkFile
  7. Next varItem
  8. '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?
Sep 1 '15 #12

P: 4
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPopulateHyperlink_Click()
  2.  
  3. Dim strButtonCaption As String, strDialogTitle As String
  4. Dim vrtSelectedItem As Variant
  5. Dim strTextToDisplay As String
  6.  
  7. strButtonCaption = "Save Hyperlink"
  8. strDialogTitle = "Select File to Create Hyperlink to"
  9.  
  10. 'Use a With...End With block to reference the FileDialog object.
  11. With Application.FileDialog(4) '4=msoFileDialogFolderPicker
  12.  
  13. 'Allow the selection of multiple file.
  14. .AllowMultiSelect = False
  15. .InitialFileName = "C:\Users\" & [fOSUserName] & "\Desktop"
  16.  
  17. 'Use the Show method to display the File Picker dialog box and return the user's action.
  18. 'The user pressed the action button.
  19. If .Show = -1 Then
  20.  
  21. 'Step through each string in the FileDialogSelectedItems collection
  22. For Each vrtSelectedItem In .SelectedItems
  23.  
  24. 'vrtSelectedItem is a String that contains the path of each selected item.
  25. 'You can use any file I/O functions that you want to work with this path.
  26. 'This example simply displays the path in a message box.
  27. strTextToDisplay = "Click here to go to location of Scans"
  28. Me![Scans] = strTextToDisplay & "#" & vrtSelectedItem
  29. Next vrtSelectedItem
  30. 'The user pressed Cancel.
  31. Else
  32. End If
  33. End With
  34. End Sub
Credit goes to hmarcks of UtterAccesshttp://www.utteraccess.com/forum/ind...wtopic=2019838
Sep 1 '15 #13

P: 1
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.
Apr 6 '16 #14