Print code on userform

[ipbr21054]

Hi,
The working code supplied below is what i use to view a saved invoice.
What i am looking for is to now use the same code but with an edit.
Once the button is pressed it will look for the invoice number & print it as opposed to viewing it.
Thanks

Private Sub OpenInvoice_Click()

    Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\"
    
    If txtInvoiceNumber = "N/A" Or Len(txtInvoiceNumber) = 0 Then
                MsgBox "Invoice N/A For This Customer", vbExclamation, "N/A INVOICE NOTICE"
    Else
        If Len(Dir(FILE_PATH & txtInvoiceNumber.Value & ".pdf")) = 0 Then
            If MsgBox("Would You Like To Open The Folder ?", vbCritical + vbYesNo, "Warning Invoice is Missing.") = vbYes Then
                CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\")
            End If
        Else
            CreateObject("Shell.Application").Open (FILE_PATH & txtInvoiceNumber.Value & ".pdf")
        End If
    End If
End Sub

 

[ipbr21054]

I have had a try at this but no joy.
I have applied the following but error message shown is object doesnt support this property or method.

Private Sub CommandButton1_Click()    Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\"
    
    If txtInvoiceNumber = "N/A" Or Len(txtInvoiceNumber) = 0 Then
                MsgBox "Invoice N/A For This Customer", vbExclamation, "N/A INVOICE NOTICE"
    Else
        If Len(Dir(FILE_PATH & txtInvoiceNumber.Value & ".pdf")) = 0 Then
            If MsgBox("Would You Like To Open The Folder ?", vbCritical + vbYesNo, "Warning Invoice is Missing.") = vbYes Then
                CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\")
            End If
        Else
            CreateObject("Word.Application").Open("C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\").PrintOut
        End If
    End If
End Sub

 

[Jaafar Tribak]

Try this and see if it works for you:

Private Sub OpenInvoice_Click()

    Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\"
    
    If txtInvoiceNumber = "N/A" Or Len(txtInvoiceNumber) = 0 Then
                MsgBox "Invoice N/A For This Customer", vbExclamation, "N/A INVOICE NOTICE"
    Else
        If Len(Dir(FILE_PATH & txtInvoiceNumber.Value & ".pdf")) = 0 Then
            If MsgBox("Would You Like To Open The Folder ?", vbCritical + vbYesNo, "Warning Invoice is Missing.") = vbYes Then
                CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\")
            End If
        Else
[COLOR=#008000]'            CreateObject("Shell.Application").Open (FILE_PATH & txtInvoiceNumber.Value & ".pdf")[/COLOR]
            
[COLOR=#ff0000][B]            CreateObject("Shell.Application").Namespace(FILE_PATH).Items. _[/B][/COLOR]
[COLOR=#ff0000][B]            Item(txtInvoiceNumber.Value & ".pdf").InvokeVerb ("Print")[/B][/COLOR]
        End If
    End If
End Sub

 

[ipbr21054]

Many thanks.
That print the pdf great.

It also opens adobe acrobat for some reason,so i need to the close it down each time.

 

[Jaafar Tribak]

It also opens adobe acrobat for some reason,so i need to the close it down each time.

I am not familiar with printing but if I am not mistaken, a document always opens when a printout is requested programmatically.

You could use the ShellExecute API for printing without showing acrobat but the code should still close the acrobat application after printing is done.

I will be leaving shortly, in the meantime do a search for ShellAndWait function which would halt the execution of the code until the printing is finished and then the code can safely close the acrobat application .

Regards.

 

[ipbr21054]

Ok
What its showing is some kind of history of the last x amount of pdf files that have been printed

 

[Jaafar Tribak]

Ok
What its showing is some kind of history of the last x amount of pdf files that have been printed

Where is that file history showing ?

 

[Jaafar Tribak]

Ok- Basically, the pdf acrobat application must be closed after the printing is completed ... I don't work with acrobat so I am not sure if it can open multiple instances at the same time like excel ... If that is the case then if you print repeatedly you can easily end up with numerous hidden acrobat processes left in memory .

Anyway, from what I can see, you are executing the printing code from a userform so maybe instead of waiting for each printing to be completed before closing acrobat (which is not easy) , I think you can run the cleanup code once at the time of closing the userform.

I'll give this a try later on tonight and will post back with any results unless someone finds an easier way.

 

[ipbr21054]

This is what happens.
I select the customer from the database.
The print now button is pressed.
The PDF invoice is now shown.
The PDF invoice now seems to dissapear but actually it just hides behind the open userform & worksheet etc.
The print is executed fine.
I close the userform.
I then close the worksheet
I now see Adobe acrobat reader with the history,this is something that just appears.

The actual PDF invoice that was shown in step 3 above is now nowhere to be seen.
The only left on t6he screen is the Adobe print hisory.
Actually ive never seen this history before & when i view the any invoices using the code issued in my first post i press the button,the invoice is seen,i then close it.
Never do i see this history at any time when viewing an invoice "ONLY WHEN I PRINT AN INVOICE"

 

[Jaafar Tribak]

@ipbr21054

Ok - The following hack "should" allow you to print the pdf file(s) without displaying the accrobat application and should also clear the accrobat process from memory after the printing is completed.

The code will only work if you send the pdf to a actual printer... It won't work if you print the document to a file.


1- Add a new standard module to your project and paste in it the following code:

Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Type SHELLEXECUTEINFO
            cbSize As Long
            fMask As Long
            hwnd As LongPtr
            lpVerb As String
            lpFile As String
            lpParameters As String
            lpDirectory As String
            nShow As Long
            hInstApp As LongPtr
            lpIDList As LongPtr
            lpClass As String
            hkeyClass As LongPtr
            dwHotKey As Long
            hIcon As LongPtr
            hProcess As LongPtr
    End Type
    Private Declare PtrSafe Function TerminateProcess Lib "kernel32" (ByVal hProcess As LongPtr, ByVal uExitCode As Long) As Long
    Private Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As LongPtr) As Long
    Private Declare PtrSafe Function ShellExecuteEx Lib "shell32.dll" Alias "ShellExecuteExA" (lpExecInfo As SHELLEXECUTEINFO) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Type SHELLEXECUTEINFO
            cbSize As Long
            fMask As Long
            hwnd As Long
            lpVerb As String
            lpFile As String
            lpParameters As String
            lpDirectory As String
            nShow As Long
            hInstApp As Long
            lpIDList As Long
            lpClass As String
            hkeyClass As Long
            dwHotKey As Long
            hIcon As Long
            hProcess As Long
    End Type
    Private Declare Function TerminateProcess Lib "kernel32" (ByVal hProcess As Long, ByVal uExitCode As Long) As Long
    Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
    Private Declare Function ShellExecuteEx Lib "shell32.dll" Alias "ShellExecuteExA" (lpExecInfo As SHELLEXECUTEINFO) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Enum Printing_Status
    Idle
    Printing
    Err
End Enum

Private Const SEE_MASK_NOCLOSEPROCESS = &H40



Public Sub PrintFile(ByVal PathFileName As String)

    Static bPrinting As Boolean
    Dim tShEx As SHELLEXECUTEINFO
    Dim hProcId As Long, lRet As Long
    
    If bPrinting = False Then
        bPrinting = True
        With tShEx
            .cbSize = LenB(tShEx)
            .fMask = SEE_MASK_NOCLOSEPROCESS
            .hwnd = Application.hwnd
            .lpVerb = "PRINT"
            .lpFile = PathFileName
            .lpParameters = ""
            .nShow = 0
        End With
        lRet = ShellExecuteEx(tShEx)
        If lRet Then
            Do
                DoEvents
            Loop Until PrinterStatus = Printing_Status.Printing Or PrinterStatus = Printing_Status.Err
            Do
                DoEvents
            Loop Until PrinterStatus = Printing_Status.Idle Or PrinterStatus = Printing_Status.Err
            Call TerminateProcess(tShEx.hProcess, 0)
            Call CloseHandle(tShEx.hProcess)
            bPrinting = False
            If PrinterStatus = Err Then MsgBox "Printing Failed!", vbCritical, "Error"
        End If
    End If
  
End Sub


Private Function PrinterStatus() As Printing_Status

    Dim oWMIService As Object, oPrinter As Object, oColInstalledPrinters As Object
    Dim sComputer As String
    
    sComputer = "."
    Set oWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\" & sComputer & "\root\cimv2")
    Set oColInstalledPrinters = oWMIService.ExecQuery("Select * from Win32_Printer")
    If Not oColInstalledPrinters Is Nothing Then
        For Each oPrinter In oColInstalledPrinters
            If InStr(1, Application.ActivePrinter, oPrinter.Name, vbTextCompare) Then
                PrinterStatus = oPrinter.PrinterStatus - 3
            End If
        Next
    Else
        PrinterStatus = Err
    End If

End Function

2- Call the PrintFile routine in your OpenInvoice_Click procedure as follows :

Private Sub OpenInvoice_Click()

    Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\"
    
    If txtInvoiceNumber = "N/A" Or Len(txtInvoiceNumber) = 0 Then
                MsgBox "Invoice N/A For This Customer", vbExclamation, "N/A INVOICE NOTICE"
    Else
        If Len(Dir(FILE_PATH & txtInvoiceNumber.Value & ".pdf")) = 0 Then
            If MsgBox("Would You Like To Open The Folder ?", vbCritical + vbYesNo, "Warning Invoice is Missing.") = vbYes Then
                CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\")
            End If
        Else
[B][COLOR=#0000ff]            Call PrintFile(FILE_PATH & txtInvoiceNumber.Value & ".pdf")[/COLOR][/B]
        End If
    End If
End Sub