Print an External file from Access

[msyoung01]

I have an Access Database - "Access App"; I need to be able to print a PDF file from a server location - "\\Server\Path\FileName.pdf" - to a specific network printer - "Printer on Network" - from inside the application.

I have looked around quite a bit, and not found a way to select an external file and then print it to a specific printer (not necessarily the default).

Any and all suggestions would be appreciated.

 

[Andrew Fergus]

Hi

I have done a few tests with this and it seems to work ok with Office 2003 on Windows XP. Copy and paste the following code into the VBE screen behind your form:

Option Compare Database
Option Explicit

'Written by Andrew Fergus
'set a reference to Windows Script Host Object Model

Private Declare Sub Sleep Lib "kernel32" _
    (ByVal dwMilliseconds As Long)

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

Const SW_SHOWNORMAL = 1

Private Sub PrintDoc_Click()

Dim dfltPrinter As String
Dim newPrinter As New WshNetwork

    'Get the name of the default printer
    dfltPrinter = Me.Printer.DeviceName

    'Set the default to the new printer
    'USE YOUR ACTUAL PRINTER NAME BELOW
    newPrinter.SetDefaultPrinter ("New Printer Name")

    'Pause
    Sleep 100

    'Print the pdf document
    'USE YOUR ACTUAL DOCUMENT NAME & PATH BELOW
    Call ShellExecute(Me.hwnd, "print", "c:\MyDocument.pdf", "", 0, SW_SHOWNORMAL)

    'Pause
    Sleep 2000 'wait for 2 seconds - increase this if the code doesn't appear to work

    'kill the acrobat process, if it is still running
    KillProcess ("acrobat")

    'Pause
    Sleep 100

    'Set the printer back to the original default
    newPrinter.SetDefaultPrinter (dfltPrinter)

End Sub


Public Sub KillProcess(ByVal processName As String)

Dim oWMI As Variant
Dim ret As Long
Dim sService As Variant
Dim oWMIServices As Variant
Dim oWMIService As Variant
Dim oServices As Variant
Dim oService As Variant
Dim servicename As String
         
    Set oWMI = GetObject("winmgmts:")
    Set oServices = oWMI.InstancesOf("win32_process")

    For Each oService In oServices
        servicename = LCase(Trim(CStr(oService.Name) & ""))
        If InStr(1, servicename, LCase(processName), vbTextCompare) > 0 Then
            ret = oService.Terminate
        End If
    Next
         
    Set oServices = Nothing
    Set oWMI = Nothing

End Sub

You will need to set a reference to "Windows Script Host Object Model" under the VBE menu option Tools > References. If you click the button and nothing happens then increase the values I used in the sleep functions. I introduced the pauses because the code would run too fast and the process was being terminated before the document had printed.

Make sure you use your actual document name and printer name where I have indicated within the code. You could set the sub up as a function and do 2 things : a) pass the document and printer name into the sub and b) return a true/false value if it was successful.

Please note I haven't used any error checking - you may want to introduce some to handle any unexpected events.

HTH, Andrew