Results 1 to 10 of 10

Thread: Shell

  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unanswered: Shell

    Ok, I've exhausted trying different ways to do this and it's really wierd because it works on some lnk's and others it doesn't. Here's my code:

    Select Case PType
    Case "lnk"
    On Error GoTo DD
    FN = Left(FN, Len(FN) - 4)
    Call Shell("MSACCESS.EXE " & Chr(34) & FN & Chr(34), vbNormalFocus)
    Exit Function
    .....

    where FN might be: H:\PH\Environ Health\EHDB\eh_production-2-11-5.mde.lnk

    I can't figure out why it works for some .mde.lnk files but others it doesn't. I've tried different syntax for the Call Shell command including...

    Call Shell("MSACCESS.EXE " & Chr(34) & FN, vbNormalFocus)
    Call Shell("MSACCESS.EXE " & FN, vbNormalFocus)
    Call Shell("MSACCESS.EXE " & " " & FN & " ", vbNormalFocus)
    Call Shell("MSACCESS.EXE " & " " & FN, vbNormalFocus)
    Call Shell(chr(34) & FN & chr(34), vbNormalFocus)
    Call Shell(FN, vbNormalFocus)

    and I also tried not dropping the last 4 characters off of FN with all of the above commands.

    I can't seem to understand why this works for the shortcuts which don't use the /wrkgrp option but does for the ones which don't use this option. The ones it doesn't work for are the ones which have properties for the shortcut like:

    "C:\Program Files\Microsoft Office 2000\Office\MSACCESS.EXE" "H:\PH\PH Nursing\Accounting\Billing\Billing.mdb" /WRKGRP"S:\HS\PH\PH Nursing\Resources for Nurses\_Access Secured MDWs\PHN.mdw"
    but a shortcut property of...

    "H:\PH\Environ Health\EHDB\Marias_db.mdb" (i.e. without the /wrkgrp option)

    works fine.

    Clicking on any of these shortcuts in Windows Explorer works fine.

    On the ones it doesn't work for, it comes up with the error: "Can't find file.......mdb.lnk" which makes me think somehow the shell syntax isn't right for the *.mdb.lnk for a shortcut with a /wrkgrp property. But this works with no problems (for just *.mdb/mde extensions and *.mdb.lnk files without a /wrkgrp in the property)....

    Case "mdb", "mde"
    On Error GoTo DD
    Call Shell("MSAccess.exe " & Chr(34) & FN & Chr(34), vbMaximizedFocus)
    Exit Function

    also works great for opening access files with just *.mdb/mde extension (ie.. no shortcut).

    Can anyone please help?
    Last edited by pkstormy; 07-17-07 at 10:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Judging from the language of Microsoft's Help of the Call keyword, it seems that you can provide an application w/ full path when using Call Shell, but I'm not so sure that you can include switches. Try using Shell without the Call keyword (and w/o the parentheses). You might fare better.

    HTH,
    Sam

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    using just Shell without the Call produces an error (missing =) when I debug unless I do something like retval = shell(....) which gives me the same error "Can't find file .....".
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Try using shell without the parens. You might be getting the error msg (missing =) because of them. It thinks you're using shell as a function. It doesn't need to be used that way. Try

    Code:
    Shell "C:\.....MSACCESS.EXE Database name /ALL YOUR SWITCHES HERE and then finally the closing double-quote."
    HTH,
    Sam

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks for the suggestion Sam Landy, but I still get the "Can't find file..." error. The problem is that I can't incorporate the entire properties of the shortcut (ie..."C:\Program Files\Microsoft Office 2000\Office\MSACCESS.EXE" "H:\PH\PH Nursing\Accounting\Billing\Billing.mdb" /WRKGRP"S:\HS\PH\PH Nursing\Resources for Nurses\_Access Secured MDWs\PHN.mdw") into the shell call but can only make a call to the *.mdb.lnk" shortcut. I know it's just a matter of syntax but I can't seem to come up with some syntax which is correct.
    This...
    Shell "MSACCESS.EXE " & Chr(34) & FN & Chr(34), vbNormalFocus
    or
    Shell FN
    gives me the "Can't find file..." error.

    There's something with the /wrkgrp in the shortcut property which is giving the shell command problems, even though clicking on the shortcut in windows explorer works fine.
    Last edited by pkstormy; 07-17-07 at 11:47.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I got it (finally!!)

    I used the Call fHandleFile(FN, WIN_NORMAL) with an API module.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Paul,

    I'm a clunkhead, perhaps, because I know less than nothing about APIs, but would you mind telling us what your function fHandleFile() does, and what the API does, so we can get educated?

    Thanks,
    Sam

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You bet Sam...
    Here's the module (written by Dev Ashish):

    '************ 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:dash10@hotmail.com",WIN_NORMA L)
    '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 **********

    To use it....Call fHandleFile(<filename>, WIN_NORMAL)

    It's another way to open a file verses using the shell command (even though I believe it still utilizes shell). How good it is or differences, I'm not sure but it works for me in my situation whereas using just shell gave me problems.
    Last edited by pkstormy; 07-17-07 at 15:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Impressive!

    Sam

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I've had it in my slew of goodies for a quite a while and never had the need to use it except in this case. It's amazing when you need something how excited you get about these snippets. But Dev Ashish has developed some impressive snippets which I've kept in my pocket.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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