Open a file (v2002)

Topic

New Reply

I have some code I pulled from Knowledge Base that opens a file in a text field when the text field is double clicked. It works quite nicely if the file is in the proper spot. I’d like to revise the code so it looks in one folder for the file, then in a second folder if the file is not in the first folder. If it doesn’t find it in the second folder, a browse dialog should appear and allow the user to navigate to the file. Any help would be appreciated.
Thanks!!

Current setup:
Module:
Option Explicit

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

Global Const SW_SHOWNORMAL = 1

Command:
Private Sub Voucher_DblClick(Cancel As Integer)
Dim strMyDir As String
strMyDir = “W:SHAREDDBase WorkScannedImages”
‘Application.FollowHyperlink strFileName, , True
Dim DocName As String
DocName = Me.Voucher
StartDoc = ShellExecute(Application.hWndAccessApp, “Open”, DocName, _
“”, strMyDir, SW_SHOWNORMAL)

End Sub

Reply | Quote

Replies

Try something like this:

Private Sub Voucher_DblClick(Cancel As Integer)
Const strPath1 = “W:SHAREDDBase WorkScannedImages”
Const strPath2 = “W:SHAREDDBase WorkOtherImages”
Dim DocName As String
DocName = Me.Voucher
If ShellExecute(Application.hWndAccessApp, “Open”, _
strPath1 & DocName, 0&, 0&, SW_SHOWNORMAL) < 32 Then
If ShellExecute(Application.hWndAccessApp, "Open", _
strPath2 & DocName, 0&, 0&, SW_SHOWNORMAL) < 32 Then
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = DocName
If .Show = True Then
DocName = .SelectedItems(1)
If ShellExecute(Application.hWndAccessApp, "Open", _
DocName, 0&, 0&, SW_SHOWNORMAL) < 32 Then
MsgBox "Failed to open file.", vbExclamation
End If
Else
MsgBox "Canceled.", vbExclamation
End If
End With
End If
End If
End Sub

Reply | Quote

Hans, It works great except for the dialog box. It returns the error:
Method of ‘FileDialog’ of object ‘_Application’ failed
and the error line is:
With Application.FileDialog(msoFileDialogOpen)
Is there a reference I need?

Thanks again!

Reply | Quote

You need Access 2002 or later, and a reference to the Microsoft Office n.0 Object Library (n=10 for Office XP/2002, n=11 for Office 2003)

Reply | Quote

Hans,
We’re running 2002 but are developing in v2000 (sorry, I should have noted that). Since we have a couple of Access 2000 users, is there a way to adapt the code for them?
Thanks!

Reply | Quote

The database attached to post 304,810 (Access 97 format) contains a class module and a module that you can import into your database. They provide a File Open and File Save dialog that will work in any version of Access. The code then becomes:

Private Sub Voucher_DblClick(Cancel As Integer)
Const strPath1 = “W:SHAREDDBase WorkScannedImages”
Const strPath2 = “W:SHAREDDBase WorkOtherImages”
Dim DocName As String
Dim dlg As CommonDialog
DocName = Me.Voucher
If ShellExecute(Application.hWndAccessApp, “Open”, _
strPath1 & DocName, 0&, 0&, SW_SHOWNORMAL) < 32 Then
If ShellExecute(Application.hWndAccessApp, "Open", _
strPath2 & DocName, 0&, 0&, SW_SHOWNORMAL) < 32 Then
Set dlg = New CommonDialog
With dlg
.FileName = DocName
If .OpenDialog = True Then
DocName = .FileName
If ShellExecute(Application.hWndAccessApp, "Open", _
DocName, 0&, 0&, SW_SHOWNORMAL) < 32 Then
MsgBox "Failed to open file.", vbExclamation
End If
Else
MsgBox "Canceled.", vbExclamation
End If
End With
Set dlg = Nothing
End If
End If
End Sub

Reply | Quote

Beautiful…I truly appreicate it Hans!

Reply | Quote

What’s the difference between SHELL and SHELLEXECUTE?

Reply | Quote

7 letters.

But seriously:

1) Shell is an old Basic command with 2 arguments:

Shell(pathname, windowstyle)

– pathname must be an MS-DOS style command line that calls an application (.exe); command line switches such as the name of a file to open can be added.
– windowstyle is an Integer that determines how the application will be opened.

Example:

Shell """C:Program FilesMicrosoft OfficeOffice11Winword.exe"" ""C:DocumentsTest.doc""", vbNormalFocus

To open a file, you *must* specify the application to use.

2) ShellExecute is a newer Windows API function that can execute various operations on files, such as opening and printing them. To do so, it uses the application that is associated with the file type, i.e. for ,doc it used Word, for .xls it uses Excel etc. In other words, you don’t have to specify the application in your code. ShellExecute has 6 arguments:

ShellExecute(hwnd, lpOperation, lpFile, lpParameters, lpDirectory, nShowCmd)

– hWnd is the window “handle” of the parent window. In Access, you can use Application.hWndAccessApp.
– lpOperation is a string that specifies the operation. “Open” and “Print” are the most common ones.
– lpFile is a string that specifies the file to operate on (including the path).
– lpParameters can be used to pass parameters to an application. For a document, it must be 0&.
– lpDirectory can be used to specify a working directory. Pass 0& if this is not important.
– nShowCmd is a number that determines how the window will be displayed, e.g. 1 = normal, 3 = maximized.

Reply | Quote