Open a linked file

[BruceM]

I have a database with tblMain and a related tblLink, represented in my
application by a form/subform. The subform fsubLink is for adding links to
files that are relevant to the main form's record. The trouble comes when I
attempt to view the links using Application.FollowHyperlink. Registered
non-office files such as pdf open OK. Excel opens, but with a "Reference is
not valid" message, which occurs even when the file is a blank workbook.
Powerpoint opens with no problem (links to PowerPoint files are unlikely,
but I tested anyhow). The biggest problem comes with Word files, which open
very, very slowly, with much flickering of the screen if I attempt to do
anything (such as navigate to another record) other than wait. The
flickering seems to be an error message about the system being busy, so
whatever I requested (e.g. going to another record) will have to wait. The
problem only seems to occur with a document on a network UNC path. Mapped
drive letters do not present a problem, nor do files on my local hard drive.

I have searched around for a while and learned that a ShellExecute API (like
the one at the MVP site) is a good way to go about opening files, but before
I go about trying to implement that code, is it a worthwhile course to take?
I have posted the code below for easy reference. The trouble for me is that
the posted code, along with its explanation, leave unanswered questions that
I would attempt to solve by trial and error. For instance, the web page
says that I need to:
"Pass the filename to fHandleFile function."
However, I am unclear on how to do that. I expect the full UNC path would
need to be passed to fHandleFile. The link is stored in a hyperlink field
(to which a text box is bound), which is populated via the standard Windows
hyperlink dialog. Would I pass the text box name (or the name of the
hyperlink field) to fHandleFile?

Or am I missing something simple like a library reference that would enable
FollowHyperlink to work in the cases that are causing problems? Or is there
another approach I have not mentioned that works?

Here is the code from the Access MVP web site:

'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiShellExecute 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

'***App Window Constants***
Public Const WIN_NORMAL = 1 'Open Normal
Public Const WIN_MAX = 3 'Open Maximized
Public Const WIN_MIN = 2 'Open Minimized

'***Error Codes***
Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

'***************Usage Examples***********************
'Open a folder: ?fHandleFile("C:\TEMP\",WIN_NORMAL)
'Call Email app: ?fHandleFile("mailto:[email protected]",WIN_NORMAL)
'Open URL: ?fHandleFile("http://home.att.net/~dashish", WIN_NORMAL)
'Handle Unknown extensions (call Open With Dialog):
' ?fHandleFile("C:\TEMP\TestThis",Win_Normal)
'Start Access instance:
' ?fHandleFile("I:\mdbs\CodeNStuff.mdb", Win_NORMAL)
'****************************************************

Function fHandleFile(stFile As String, lShowHow As Long)
Dim lRet As Long, varTaskID As Variant
Dim stRet As String
'First try ShellExecute
lRet = apiShellExecute(hWndAccessApp, vbNullString, _
stFile, vbNullString, vbNullString, lShowHow)

If lRet > ERROR_SUCCESS Then
stRet = vbNullString
lRet = -1
Else
Select Case lRet
Case ERROR_NO_ASSOC:
'Try the OpenWith dialog
varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL "
_
& stFile, WIN_NORMAL)
lRet = (varTaskID <> 0)
Case ERROR_OUT_OF_MEM:
stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
Case ERROR_FILE_NOT_FOUND:
stRet = "Error: File not found. Couldn't Execute!"
Case ERROR_PATH_NOT_FOUND:
stRet = "Error: Path not found. Couldn't Execute!"
Case ERROR_BAD_FORMAT:
stRet = "Error: Bad File Format. Couldn't Execute!"
Case Else:
End Select
End If
fHandleFile = lRet & _
IIf(stRet = "", vbNullString, ", " & stRet)
End Function
'************ Code End **********

 

[Daniel]

Check the following site out, it may solve your problem

http://www.pacificdb.com.au/MVP/Code/ExeFile.htm
--
Hope this helps,

Daniel P

 

[BruceM]

Thanks for the link. I had to do some work on it in order to parse the file
name and path from the hyperlink field. I need to run now, but I will post
details on Monday. Bottom line, it works.

 

[BruceM]

Here is the modified code:

Public Const SW_HIDE = 0
Public Const SW_MINIMIZE = 6
Public Const SW_RESTORE = 9
Public Const SW_SHOW = 5
Public Const SW_SHOWMAXIMIZED = 3
Public Const SW_SHOWMINIMIZED = 2
Public Const SW_SHOWMINNOACTIVE = 7
Public Const SW_SHOWNA = 8
Public Const SW_SHOWNOACTIVATE = 4
Public Const SW_SHOWNORMAL = 1

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 Sub ExecuteFile(strFullpath As String)

Dim strFilename As String
Dim strPath As String
Dim strArray() As String

strArray = Split(strFullpath, "\", -1, vbTextCompare)

strFilename = strArray(UBound(strArray))

strPath = Left(strFullpath, Len(strFullpath) - Len(strFilename))

If ShellExecute(Access.hWndAccessApp, "open", strFilename, vbNullString,
strPath, SW_SHOWNORMAL) < 33 Then
DoCmd.Beep
MsgBox "File not found."
End If

End Sub

In a command button click event, to view the link:

Dim strLink As String
strLink = Me.txtEvLink.Hyperlink.Address

Call ExecuteFile(strLink)

I eliminated the option to Open or Print (users can choose to print once the
document is open, but that will probably be an infrequent choice.
Since I was using a hyperlink field to establish the path to the document,
it was necessary to parse the field into Path and FileName. To do so, I
used Me.txtEvLink.Hyperlink.Address; otherwise the path included the path
followed by the address bracketed by # signs:
\\Servername\FolderName\FileName#\\Servername\FolderName\FileName# (or
something like that).
The Split function in strArray breaks the path into sections. UBound (in
strFileName) identifies the section that consists of the file name. strPath
parses the path from the hyperlink field. These variables are inserted into
ShellExecute.

In the interest of full disclosure, a computer science student who is
working here this summer identified the solution to parsing the path and
file name.