Hyperlink Sub

[armsys]

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

[HansV]

Hi Armstrong,

Welcome to Eileen's Lounge!

Do you only want to open files (as opposed to web pages)?

[armsys]

Hi Hans,
It's nice to see you again here.
Yes, only files.
Thanks.
Armstrong

[HansV]

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):

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

In the On Click code of your button, use code like this:

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

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:

Code: Select all

Private Sub Command0_Click()
  Dim strFile As String
  strFile = ... ' specify path and filename of the executable
  Shell strFile, vbNormalFocus
End Sub

If you'd like the executable to be opened in a maximized window, change vbNormalFocus to vbMaximizedFocus.

[armsys]

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

[HansV]

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

[armsys]

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

[HansV]

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)

[armsys]

It won't make any difference. After setting the "Is Hyperlink" to No, the security warning dialog property still pop up?

[HansV]

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.

[armsys]

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

[armsys]

Your code appears to fail on x64 Access 2010:
Public Declare Function ShellExecute _
Please help. Thanks.

[armsys]

Hans,
I just solved the problem.
Public Declare PtrSafe Function ShellExecute _
will do the trick.

[HansV]

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.

[armsys]

Hans,
Thanks for your fast help.
Thanks for the valuable link.