Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ShellExecute (Access 2000)

    I have a table storing paths and filenames of documents that need to be printed along with certain reports. I am using the ShellExecute function with the "Print" parameter to achieve this. However, if the file is already open, the procedure fails. Is there anyway of testing if the file is already open? The documents can be of any type so I don't know the application in advance.

    Cheers
    Graham

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

    Re: ShellExecute (Access 2000)

    See XL2000: Macro Code to Check Whether a File Is Already Open. Although the MSKB article is for Excel, the IsFileOpen function can be used anywhere.

  3. #3
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ShellExecute (Access 2000)

    Hans
    Just the ticket, as usual - many thanks
    Graham

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

    Re: ShellExecute (Access 2000)

    Without knowing the code you are using, it is impossible to give advice.

  5. #5
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ShellExecute (Access 2000)

    The PrintAttachments() function is called from a form supplying the directory where the files are located. The tAttachments table contains the filename ATFNME, the reference number ATSNUM and wheter it should be printed ATPRNT. It then loops through the recordset, tests if the file is open and then prints using Shellexecute.

    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

    Function PrintAttachments(strSER As String)
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQl As String
    Dim strFName As String
    Dim blnPrintOK As Boolean

    On Error GoTo PrintAttachmentsErr

    blnPrintOK = True
    Set conn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    strSQl = "SELECT ATFNME FROM tAttachments WHERE ATSNUM = '" & strSER & "' AND ATPRNT = -1"
    rs.Open strSQl, conn, adOpenStatic, adLockReadOnly
    If Not rs.EOF Then
    rs.MoveFirst
    Do Until rs.EOF
    strFName = gstrPath & "" & strSER & "" & rs![ATFNME]
    If IsFileOpen(strFName) Then
    blnPrintOK = False
    Else
    ShellExecute Application.hWndAccessApp, "Print", strFName, "", gstrPath, SW_SHOWNORMAL
    End If
    rs.MoveNext
    Loop
    End If
    If Not blnPrintOK Then
    MsgBox "Not all attachments printed properly. Please check and print missing files manually", vbInformation + vbOKOnly, gstrAppTitle
    End If

    PrintAttachmentsExit:
    On Error Resume Next
    Set rs = Nothing
    Set conn = Nothing
    Exit Function

    PrintAttachmentsErr:
    gstrObject = "Functions"
    gstrProcedure = "PrintAttachments()"
    glngErrNum = Err.Number
    gstrErrDesc = Err.Description
    gstrErrLine = Erl
    ReportError
    Resume PrintAttachmentsExit
    Exit Function
    End Function

    Public Function IsFileOpen(strFName As String) As Boolean
    Dim iFNum As Integer
    Dim iENum As Integer

    On Error Resume Next
    iFNum = FreeFile()
    Open strFName For Input Lock Read As #iFNum
    Close iFNum
    iENum = Err

    On Error GoTo IsFileOpenErr

    Select Case iENum
    Case 0
    IsFileOpen = False
    Case 70
    IsFileOpen = True
    Case Else
    Error iENum
    End Select

    IsFileOpenExit:
    On Error Resume Next
    Exit Function

    IsFileOpenErr:
    MsgBox Err.Num & " : " & Error
    Resume IsFileOpenExit
    End Function

  6. #6
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ShellExecute (Access 2000)

    Edited by HansV to provide link to MSKB article.

    I am getting error 49 Bad DLL calling convention when running a procedure calling ShellExecute. <!mskb=213554>Microsoft Knowledge Base Article 213554<!/mskb> doesn't enlighten me and the error doesn't occur everytime. Could someone please help me understand.

    Thanks
    Graham

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

    Re: ShellExecute (Access 2000)

    Try replacing

    ShellExecute Application.hWndAccessApp, "Print", strFName, "", gstrPath, SW_SHOWNORMAL

    with

    ShellExecute Application.hWndAccessApp, "Print", strFName, 0&, 0&, SW_SHOWNORMAL

    Also, are you sure that strFName always results in a valid path+filename?

  8. #8
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ShellExecute (Access 2000)

    I've made the modification you suggested and put in error trapping for invalid filenames. All seems fine now - Thanks
    Graham

Posting Permissions

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