Hyperlink Sub
-
- 2StarLounger
- Posts: 105
- Joined: 19 Apr 2010, 10:25
- Location: Hong Kong
Hyperlink Sub
How to write a sub for executing a hyperlink, say, sub Hyperlink_click()?
Purpose: Avoid annoying Microsoft security warning especially for files such as .chm and .exe.
Thanks.
Armstrong
Purpose: Avoid annoying Microsoft security warning especially for files such as .chm and .exe.
Thanks.
Armstrong
Regards,
Armstrong
Armstrong
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Hyperlink Sub
Hi Armstrong,
Welcome to Eileen's Lounge!
Do you only want to open files (as opposed to web pages)?
Welcome to Eileen's Lounge!
Do you only want to open files (as opposed to web pages)?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 105
- Joined: 19 Apr 2010, 10:25
- Location: Hong Kong
Re: Hyperlink Sub
Hi Hans,
It's nice to see you again here.
Yes, only files.
Thanks.
Armstrong
It's nice to see you again here.
Yes, only files.
Thanks.
Armstrong
Regards,
Armstrong
Armstrong
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Hyperlink Sub
You can use the ShellExecute function for documents (i.e. files that are opened in an application).
Place the following code in a standard module (the kind that you create by selecting Insert | Module in the Visual Basic Editor):
In the On Click code of your button, use code like this:
If you'd like the file to be opened in a maximized window, change SW_SHOWNORMAL to SW_SHOWMAXIMIZED.
For executable files such as a .exe, you can use Shell:
If you'd like the executable to be opened in a maximized window, change vbNormalFocus to vbMaximizedFocus.
Place the following code in a standard module (the kind that you create by selecting Insert | Module in the Visual Basic Editor):
Code: Select all
Public Declare Function ShellExecute _
Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Public Const SW_SHOWNORMAL = 1
Public Const SW_SHOWMAXIMIZED = 3
Code: Select all
Private Sub cmdSomething_Click()
Dim strFile As String
strFile = ... ' specify path and filename
ShellExecute Application.hWndAccessApp, "Open", _
strFile, 0&, 0&, SW_SHOWNORMAL
End Sub
For executable files such as a .exe, you can use Shell:
Code: Select all
Private Sub Command0_Click()
Dim strFile As String
strFile = ... ' specify path and filename of the executable
Shell strFile, vbNormalFocus
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 105
- Joined: 19 Apr 2010, 10:25
- Location: Hong Kong
Re: Hyperlink Sub
Yes, that's exactly what I need.
Suppose a field, named URL, is of hyperlink date type.
Can I simply do this:
ShellExecute Application.hWndAccessApp, "Open", me.URL, 0&, 0&, SW_SHOWNORMAL
Thanks.
Armstrong
Suppose a field, named URL, is of hyperlink date type.
Can I simply do this:
ShellExecute Application.hWndAccessApp, "Open", me.URL, 0&, 0&, SW_SHOWNORMAL
Thanks.
Armstrong
Regards,
Armstrong
Armstrong
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Hyperlink Sub
If you're going to use code to open the file, it'd be better not to use a Hyperlink field but a simple text field.
A Hyperlink field is actually a memo field that contains text of the following form:
description#address#subaddress
So you can't use the entire value, you have to extract the address. For example:
Dim p1 As Long
Dim p2 As Long
Dim strAddress As String
strAddress = Me.URL
p1 = InStr(strAddress, "#")
p2 = InStr(p1 + 1, strAddress, "#")
strAddress = Mid(strAddress, p1 + 1, p2 - p1 - 1)
ShellExecute Application.hWndAccessApp, "Open", strAddress, 0&, 0&, SW_SHOWNORMAL
A Hyperlink field is actually a memo field that contains text of the following form:
description#address#subaddress
So you can't use the entire value, you have to extract the address. For example:
Dim p1 As Long
Dim p2 As Long
Dim strAddress As String
strAddress = Me.URL
p1 = InStr(strAddress, "#")
p2 = InStr(p1 + 1, strAddress, "#")
strAddress = Mid(strAddress, p1 + 1, p2 - p1 - 1)
ShellExecute Application.hWndAccessApp, "Open", strAddress, 0&, 0&, SW_SHOWNORMAL
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 105
- Joined: 19 Apr 2010, 10:25
- Location: Hong Kong
Re: Hyperlink Sub
Your code works perfectly.
Thanks a lot.
Because the field is of hyperlink data type, when clicking it, Access still processes the hyperlink and pops up the security warning dialog box. How could I suppress Access from processing the hyperlink? How can I set it back to Ctrl+Click?
Armstrong
Thanks a lot.
Because the field is of hyperlink data type, when clicking it, Access still processes the hyperlink and pops up the security warning dialog box. How could I suppress Access from processing the hyperlink? How can I set it back to Ctrl+Click?
Armstrong
Regards,
Armstrong
Armstrong
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Hyperlink Sub
Set the Is Hyperlink property of the text box bound to the hyperlink field to No. (Neither click nor Ctrl+click will activate the hyperlink)
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 105
- Joined: 19 Apr 2010, 10:25
- Location: Hong Kong
Re: Hyperlink Sub
It won't make any difference. After setting the "Is Hyperlink" to No, the security warning dialog property still pop up?
Regards,
Armstrong
Armstrong
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Hyperlink Sub
What a stupid bug in Access!
I fear that you'll either have to disable the text box, or change the underlying field to a text field (which would be better anyway).
There are probably ways of suppressing the security warning, but they will only apply to you, not to other users of your database.
I fear that you'll either have to disable the text box, or change the underlying field to a text field (which would be better anyway).
There are probably ways of suppressing the security warning, but they will only apply to you, not to other users of your database.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 105
- Joined: 19 Apr 2010, 10:25
- Location: Hong Kong
Re: Hyperlink Sub
Hans,
Thanks for your usual quick reply.
Setting Enabled property to No will not respond to any event.
There's no solution in the world to suppress the security warning in Access 2007. Your code is the only successful solution against the annoying security warning.
Referring to my previous comment about setting "Is Hyperlink" property, I misunderstood you. After changing the data type from hyperlink to text, yes, setting the property does work.
Thanks for your help.
Armstrong
Thanks for your usual quick reply.
Setting Enabled property to No will not respond to any event.
There's no solution in the world to suppress the security warning in Access 2007. Your code is the only successful solution against the annoying security warning.
Referring to my previous comment about setting "Is Hyperlink" property, I misunderstood you. After changing the data type from hyperlink to text, yes, setting the property does work.
Thanks for your help.
Armstrong
Regards,
Armstrong
Armstrong
-
- 2StarLounger
- Posts: 105
- Joined: 19 Apr 2010, 10:25
- Location: Hong Kong
Re: Hyperlink Sub
Your code appears to fail on x64 Access 2010:
Public Declare Function ShellExecute _
Please help. Thanks.
Public Declare Function ShellExecute _
Please help. Thanks.
Last edited by armsys on 22 Apr 2010, 12:52, edited 1 time in total.
Regards,
Armstrong
Armstrong
-
- 2StarLounger
- Posts: 105
- Joined: 19 Apr 2010, 10:25
- Location: Hong Kong
Re: Hyperlink Sub
Hans,
I just solved the problem.
Public Declare PtrSafe Function ShellExecute _
will do the trick.
I just solved the problem.
Public Declare PtrSafe Function ShellExecute _
will do the trick.
Regards,
Armstrong
Armstrong
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Hyperlink Sub
That is correct - Windows API declarations must be modified to work in a 64-bit application. See Jan Karel Pieterse's article Declaring API functions in 64 bit Office and the links provided there.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 105
- Joined: 19 Apr 2010, 10:25
- Location: Hong Kong