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
13 42802
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
It's probably due to either 1 of 2 conditions: - You did not set a Reference to the Microsoft Office XX.X Object Library
- You are running an earlier Version of Access that does not support the FileDialog Object
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
In any Code View Window: - Select Tools from the Menu Bar
- Select References
- Scroll down to the Microsoft Office XX.X Object Library (Version will vary)
- Click on OK
Thanks ADezii it worked perfectly! You really did my working-day!
Glad it all worked out for you, DamePique.
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
-
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
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
Hi Glenton.
Thanks for the response.
I'll try this now and return with an update!
-Steve
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?
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 UtterAccess http://www.utteraccess.com/forum/ind...wtopic=2019838
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Amadrias |
last post by:
Hi,
In one of my current projects, I am willing to create a secure zone such as
Acronis does in its application to store some confidential information only
accessible to the current machine...
|
by: skgolden |
last post by:
My husband and I own a small temporary labor company and deal with 4
major clients (A,B,C & D), each of which has about 2 dozen units in
our tristate area that we deal with (ie, Company A, units...
|
by: ad |
last post by:
I use the code to create a DataGrid and filled with data,
But the DataGrid1 didn't display in WebForm.
How can I display the DataGrid after creating it?
DataGrid DataGrid1 = new DataGrid();
|
by: SaT |
last post by:
Does anyone know how to create hyperlink labels within a
datagrid, on the fly?
Thanks
SaT
|
by: Michael Creager |
last post by:
I am using VB NET 2002. How can I programmatically create a new windows
form using the value of a string variable as the name of the new form?
Public FrmName As String = "MDIChildFrm1"
Public...
|
by: Chris Thunell |
last post by:
Is there any way to programmatically create an odbc system dsn in vb.net?
I have a bunch of DSNs that need to be created for SQL, Access, and
Timberline and i thought that it would make my life...
|
by: theine |
last post by:
How do I create Hyperlink based of a specific field in the same row?
In Excel it done like this:
=HYPERLINK("", A273)
where the anything inside the " " is the value it's looking for in that...
|
by: lmeyers |
last post by:
I implemented a URL rewriter which worked reasonably well, but now I am creating other sites on the same server (single IP). Because IIS will not accept subdomain wildcards (*.subdomain.com), it...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |