Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open a file (v2002)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a file (v2002)

    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

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a file (v2002)

    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!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a file (v2002)

    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)

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a file (v2002)

    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!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a file (v2002)

    The database attached to <post:=304,810>post 304,810</post:> (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

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a file (v2002)

    Beautiful...I truly appreicate it Hans!

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a file (v2002)

    What's the difference between SHELL and SHELLEXECUTE?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a file (v2002)

    7 letters. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    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:
    <code>
    Shell """C:Program FilesMicrosoft OfficeOffice11Winword.exe"" ""CocumentsTest.doc""", vbNormalFocus
    </code>
    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:
    <code>
    ShellExecute(hwnd, lpOperation, lpFile, lpParameters, lpDirectory, nShowCmd)
    </code>
    - 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •