Link to home
Start Free TrialLog in
Avatar of Thirt
Thirt

asked on

How to print a linked document

I'm using a link data field to link to physical documents on the network as described here: https://www.experts-exchange.com/questions/22955821/How-to-update-Hyperlink-field.html

I have the link working and when clicked, the appropriate application will launch and then load the document.  Now the user wants a button to print the document automatically. is this possible.
SOLUTION
Avatar of GRayL
GRayL
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Thirt
Thirt

ASKER

That's what I tried to explain, but they are insistent to have a button on the access form that prints the document from the default printer. File types of Word, Excel and powerpoint have some hope, no? But printing a TIF or PDF....ugh.  Any thoughts...no matter how messy?
Avatar of Jeffrey Coachman
GRayL,
<OT>
Messy indeed!

I tought about using SendKeys (already messy!) to activate File-->Print on the Native App's screen, buy using a Print button on a form in Access, But once you open the App, the Access form cannot get the focus automatically (Even if you set it to Popup)

Oh well...

Jeff
Thirt,

(Again, no points wanted)

The Print command in Access will only print what is IN access.

Even If your External doc was in an OLE field, access would only print the image of that doc as it appears in the form.

JeffCoachman
Avatar of Thirt

ASKER

Here's what I was able to put together based on other post here at EE.


Dim strfiletype
Dim strfilelink
 
Const strcProcName As String = "BtnPrintFile_Click"
    
On Error GoTo ErrorHandler
 
 
 
strfiletype = Right(txtfilename, 3)
strfilelink = Me.FileLink.Hyperlink.Address
 
 
    Select Case strfiletype
    
    Case "doc", "RTF", "txt"
    '* Word API code here
        'Need a reference to office library, this may be an issue if different office's are used
        Dim WordObj As Object
        Set WordObj = CreateObject("Word.Application")
        WordObj.Documents.Open strfilelink
        WordObj.PrintOut Background:=False
        WordObj.Quit
        Set WordObj = Nothing
        
    Case "xls"
        Dim objxlsheet As Object
 
        Set objxlsheet = GetObject(strfilelink)
        
        With objxlsheet
           ' .Sheets("Data Access").Select
            .ActiveSheet.PrintOut Copies:=1
        End With
        
    Case "PDF"
    '* PDF API code here
        PrintFilePDF strfilelink
        
    Case "tif"
    '* used any preferred paint type program
        Dim myshell
        Set myshell = CreateObject("WScript.Shell")
        myshell.Run ("mspaint.exe " & strfilelink & " /p")
    
    Case "ppt"
    RunApp strfilelink, , , , "print"
    
    Case Else
    '* Tell user we don't have a supported API for this file ext
    MsgBox "File Type ." & strfiletype & " not supported for direct printing. " & _
            "Please use the link option to open the file and print it."
    
    End Select
    
    MsgBox "Document sent to your Windows Default Printer"
    
    
ExitHere:
Exit Sub
 
ErrorHandler:
    Call ShowError(mstrcModuleName, strcProcName, _
                   Err.Number, Err.Description)
    Resume ExitHere
    
End Sub
 
Module: modprintPDF
Private 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 Sub PrintFilePDF(ByVal sFile As String)
    Call ShellExecute(Application.hWndAccessApp, "print", sFile, "", "", 0)
End Sub
 
Module: printPPT
 
Private 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
 
 
Private Declare Function ShellExecuteForExplore Lib "shell32.dll" Alias "ShellExecuteA" _
  (ByVal hWnd As Long, ByVal lpOperation As String, _
  ByVal lpFile As String, lpParameters As Any, _
  lpDirectory As Any, ByVal nShowCmd As Long) As Long
 
Public Enum EShellShowConstants
    essSW_HIDE = 0
    essSW_MAXIMIZE = 3
    essSW_MINIMIZE = 6
    essSW_SHOWMAXIMIZED = 3
    essSW_SHOWMINIMIZED = 2
    essSW_SHOWNORMAL = 1
    essSW_SHOWNOACTIVATE = 4
    essSW_SHOWNA = 8
    essSW_SHOWMINNOACTIVE = 7
    essSW_SHOWDEFAULT = 10
    essSW_RESTORE = 9
    essSW_SHOW = 5
End Enum
 
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&
Private Const SE_ERR_ACCESSDENIED = 5        ' access denied
Private Const SE_ERR_ASSOCINCOMPLETE = 27
Private Const SE_ERR_DDEBUSY = 30
Private Const SE_ERR_DDEFAIL = 29
Private Const SE_ERR_DDETIMEOUT = 28
Private Const SE_ERR_DLLNOTFOUND = 32
Private Const SE_ERR_FNF = 2                ' file not found
Private Const SE_ERR_NOASSOC = 31
Private Const SE_ERR_PNF = 3                ' path not found
Private Const SE_ERR_OOM = 8                ' out of memory
Private Const SE_ERR_SHARE = 26
 
 
Public Function RunApp(ByVal sFile As String, _
                       Optional ByVal eShowCmd As EShellShowConstants = essSW_SHOWDEFAULT, _
                       Optional ByVal sParameters As String = "", _
                       Optional ByVal sDefaultDir As String = "", _
                       Optional sOperation As String = "open", _
                       Optional Owner As Long = 0) As Boolean
    Dim lR As Long
    Dim lErr As Long, sErr As Long
    
    If (InStr(UCase$(sFile), ".EXE") <> 0) Then
        eShowCmd = 0
    End If
    
    On Error Resume Next
    If (sParameters = "") And (sDefaultDir = "") Then
        lR = ShellExecuteForExplore(Owner, sOperation, sFile, 0, 0, essSW_SHOWNORMAL)
    Else
        lR = ShellExecute(Owner, sOperation, sFile, sParameters, sDefaultDir, eShowCmd)
    End If
    
    If (lR < 0) Or (lR > 32) Then
        RunApp = True
    Else
        ' raise an appropriate error:
        lErr = vbObjectError + 1048 + lR
        Select Case lR
            Case 0
            lErr = 7: sErr = "Out of memory"
            Case ERROR_FILE_NOT_FOUND
                lErr = 53: sErr = "File not found"
            Case ERROR_PATH_NOT_FOUND
                lErr = 76: sErr = "Path not found"
            Case ERROR_BAD_FORMAT
                sErr = "The executable file is invalid or corrupt"
            Case SE_ERR_ACCESSDENIED
                lErr = 75: sErr = "Path/file access error"
            Case SE_ERR_ASSOCINCOMPLETE
                sErr = "This file type does not have a valid file association."
            Case SE_ERR_DDEBUSY
                lErr = 285: sErr = "The file could not be opened because the target application is busy. Please try again in a moment."
            Case SE_ERR_DDEFAIL
                lErr = 285: sErr = "The file could not be opened because the DDE transaction failed. Please try again in a moment."
            Case SE_ERR_DDETIMEOUT
                lErr = 286: sErr = "The file could not be opened due to time out. Please try again in a moment."
            Case SE_ERR_DLLNOTFOUND
                lErr = 48: sErr = "The specified dynamic-link library was not found."
            Case SE_ERR_FNF
                lErr = 53: sErr = "File not found"
            Case SE_ERR_NOASSOC
                sErr = "No application is associated with this file type."
            Case SE_ERR_OOM
                lErr = 7: sErr = "Out of memory"
            Case SE_ERR_PNF
                lErr = 76: sErr = "Path not found"
            Case SE_ERR_SHARE
                lErr = 75: sErr = "A sharing violation occurred."
            Case Else
                sErr = "An error occurred occurred whilst trying to open or print the selected file."
        End Select
    
        Err.Raise lErr, , Application.hWndAccessApp & ".GShell", sErr
        RunApp = False
    End If
    
End Function

Open in new window

Avatar of Thirt

ASKER

I wish I could give due credit for the post and code that I found here, so please forgive. If this is a post that should hang around for future consumption, then I will track that info down. But thanks for the help as it did point me in the right direction. EE is great place indeed!!
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Thirt

ASKER

Well crap, the client just called to tell me how happy they were with it, But.....

They would like the current Date and Time Stamp along with text to be printed at the bottom of the page. Something to the effect: "This is a printed copy of a controlled document valid for operations today only"

Is it too wishful to think there is a way to place a document in another wrapper document of sorts? Egad!
Thirt,

No matter what you do...*They* always want more!
:O

I (and I'm sure GRayL does to) get this all the time.
    "Jeff, I love the database, everything is perfect, But...."
:O

Good Luck!

JeffCoachman
Avatar of Thirt

ASKER

I'm suggesting to the client that I include an Access Report along with the printed document. If they can make the biz rule that a document is not valid unless a boiler page report is included, then I'm home freeeeeee!
Avatar of Thirt

ASKER

Thanks for the input.