Shell command

[nickellis]

Hi,

can anyone tell me why the following doesn't work?

Private Sub cmdOpenFile_Click()
On Error GoTo Err_cmdOpenFile_Click

    Dim stAppName
    Dim docType As Integer
    Dim ext As String
    
    docType = DLookup("documentType", "tblDocuments", "documentID = '" & Me.documentID.Value & "'")
    ext = DLookup("extension", "tblDocTypes", "docTypeID = '" & docType & "'")

    stAppName = Me.documentPath.Value & "\" & Me.documentName.Value & "." & ext
    
    Call Shell(stAppName, vbNormalFocus)

Exit_cmdOpenFile_Click:
    Exit Sub

Err_cmdOpenFile_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenFile_Click
    
End Sub

It was, as you can see, generated by the command button wizard and I then went back in and jigged the code to make it dynamic based on the selected record on the form. I know it generates a correct path, but I get then "Invalid procedure call or argument error" whenever I run it. This happens even if I manually type the path into the code.

On the other hand, if I put "calc" as the path it runs calc perfectly. So it's something in the variable, but I can't see what.

Thanks

Nick

 

[modest]

what's your stAppName?

You need to give some more info. If the call works for one thing and not for the other, then your code works and there's a problem with your variable, which means your variable name is probably not correct.

So add either your database or what the string's value is.

-------------------------------------------------------
Also:

Dim docType As Integer -1
docType = DLookup("documentType -2", "tblDocuments", "documentID = '" & Me.documentID.Value & "'")
ext = DLookup("extension", "tblDocTypes", "docTypeID = '" & docType & "'" -3 )

1: You declared docType as an integer
2: The "documentType" field should be returning a number
3: If "docTypeID" is a number field, then you don't need the single quotes around docType

 

[ghudson]

Like modest stated you have a problem with your string.

Use a message box to "see" what is being passed to the shell.

Private Sub cmdOpenFile_Click()
On Error GoTo Err_cmdOpenFile_Click

    Dim stAppName
    Dim docType As Integer
    Dim ext As String
    
    docType = DLookup("documentType", "tblDocuments", "documentID = '" & Me.documentID.Value & "'")
    msgbox "docType = " & docType

    ext = DLookup("extension", "tblDocTypes", "docTypeID = '" & docType & "'")
    msgbox "ext = " & ext

    stAppName = Me.documentPath.Value & "\" & Me.documentName.Value & "." & ext
    msgbox "stAppName = " & stAppName
    
    Call Shell(stAppName, vbNormalFocus)

Exit_cmdOpenFile_Click:
    Exit Sub

Err_cmdOpenFile_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenFile_Click
    
End Sub

 

[nickellis]

Thanks for your responses, guys. I got it to work like this:

Dim stAppName As String
    Dim docType As Integer
    Dim ext As String
    Dim app As String
    
    docType = DLookup("documentType", "tblDocuments", "documentID = '" & Me.documentID.Value & "'")
    ext = DLookup("extension", "tblDocTypes", "docTypeID = '" & docType & "'")

    stAppName = Me.documentPath.Value & "\" & Me.documentName.Value & "." & ext
    
    Select Case ext
        Case Is = "xls"
            app = "excel"
        Case Is = "doc"
            app = "msword"
        Case Is = "ppt"
            app = "powerpnt"
        Case Is = "pdf"
            app = "AcroRd32"
        Case Else
            MsgBox "No application is specified for this file type", vbOKOnly, "Action Documents"
            Exit Sub
    End Select
    
    Call Shell(app & " " & stAppName, vbNormalFocus)

Which works fine by defining the app (which is guaranteed to be one of those four) and then passing the file path to it in the switch. Or at least, it works fine for MS applications. It doesn't work for Adobe Acrobat files.

Does anybody know how to open a PDF programmatically?

Thanks

Nick

 

[ghudson]

Check out the OpenFile function in my Browse [Find a directory or file] sample. It will allow you to select any file and open it.

 

[ChrisO]

G’day Nick

Once you have the file names sorted out you can use the ShellExecute API function that uses the default application associated with the file extension: -

Option Explicit
Option Compare Text


Public Declare Function ShellExecute Lib "shell32.dll" _
                 Alias "ShellExecuteA" (ByVal lngHwnd As Long, _
                                        ByVal strOperation As String, _
                                        ByVal strFile As String, _
                                        ByVal strParameters As String, _
                                        ByVal strDirectory As String, _
                                        ByVal lngShow As Long) As Long

Public Const conHide As Long = 0
Public Const conShow As Long = 1


Sub Test()

    [color=green]'  To open the file...[/color]
    ShellExecute 0, "Open", "C:\brakes.pdf", "", "", conShow

    [color=green]'  To print the file to the default output device...[/color]
    ShellExecute 0, "Print", "C:\brakes.pdf", "", "", conHide
    
    [color=green]'  To execute an executable file...[/color]
    ShellExecute 0, "Open", "C:\RunCompact.bat", "", "", conHide

End Sub

No need for the application name which might change from version to version.

Hope that helps.

Regards,
Chris.

 

[nickellis]

Thanks Chris

I'm not great with using Modules. I'm guessing that I need to create this as a class module?

Having done that, how do I then call it?

Thanks

Nick

 

[ghudson]

The API Chris listed above is the same as I am using in my Browsing sample I posted a link to above. Chris showed you how to call it. Using your stAppName string name from above, try this...

ShellExecute 0, "Open", stAppName, "", "", conShow

 

[ghudson]

This is the variation of the ShellExecute API I use...

Option Compare Database
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

Public Function Test()
On Error GoTo Err_Test

    'Open file
    ShellExecute 0, "Open", "C:\Temp\123.txt", "", "", 1

    'Print file
    ShellExecute 0, "Print", "C:\Temp\123.txt", "", "", 0
    
    'Execute an executable
    ShellExecute 0, "Open", "C:\Temp\123.bat", "", "", 0

Exit_Test:
    Exit Function

Err_Test:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Test

End Function