VBA code - Print a PDF document in corresponding Cell

[Ben171]

I have a database which stores entries from user.

in Cells D2:D100 are Works Order Numbers,
in Cells R2:R100 are hyperlinks to a PDF that is corresponding to that works order number.

I would like to write some code in which users enters a works order number in cell C3 of the "print documents sheet"

It will then search the "Database" sheet for that works order number, and then when it has found it, it prints the PDF which is in Cell R at the end of the row in which the works order number is in.

I have started writing some code to do this, I have written some code that loops through the rows and finds the work order number. However i am not sure how to do the inbetween bit, which is to print the pdf at the end of the row.

any help would be greatly appreciated, see starter code below

Dim iRow As Long 'Variable to hold the starting row and loop through all records in database
Dim sh As Worksheet 'worksheet variable to refer  to where database is stored
Dim myValue As Variant
Dim WorksOrder As String
Dim Found As Boolean



'Get user entered WorksOrder Number

WorksOrder = ThisWorkbook.Sheets("PrintDocuments").Range("C3").Value


'Set worksheet

Set sh = ThisWorkbook.Sheets("Database")

iRow = 2 'row in which data starts from in database

Found = False

Do While sh.Range("A" & iRow).Value <> ""  'loop through until no data is found (last row of database)
 
If WorksOrder = sh.Range("D" & iRow).Value Then

Found = True

'Insert print PDF code here











Exit Do

End If

iRow = iRow + 1

Loop



If Found = True Then
MsgBox ("Document Printed")
Else
MsgBox ("Works Order Number Not Found")
End If

End Sub

 

[John_w]

I do have Adobe reader installed, setting that as my default application seemed to have fixed it, it's printing now!

Yes, I was wrong about not needing to change the default PDF viewer, because the code looks for the .exe associated with PDF files and expects that to be Acrobat.exe or AcroRd32.exe because it uses their /t command line option to print the file. It should really check the PDFexe variable to see if it ends with Acrobat.exe or AcroRd32.exe and warn the user if not.

If you don't want to change the default PDF viewer the code can be changed to explicitly use Adobe Reader or Acrobat Pro. In Shell_Print_PDF replace:

        If PDFexe = "" Then PDFexe = ExePath(PDFfullName)

with one of these lines, depending on your Adobe Reader/Acrobat Pro version:

        PDFexe = "C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"
        PDFexe = "C:\Program Files (x86)\Adobe\Acrobat 11.0\Acrobat\Acrobat.exe"

 

[Ben171]

Yes, I was wrong about not needing to change the default PDF viewer, because the code looks for the .exe associated with PDF files and expects that to be Acrobat.exe or AcroRd32.exe because it uses their /t command line option to print the file. It should really check the PDFexe variable to see if it ends with Acrobat.exe or AcroRd32.exe and warn the user if not.

If you don't want to change the default PDF viewer the code can be changed to explicitly use Adobe Reader or Acrobat Pro. In Shell_Print_PDF replace:

        If PDFexe = "" Then PDFexe = ExePath(PDFfullName)

with one of these lines, depending on your Adobe Reader/Acrobat Pro version:

        PDFexe = "C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"
        PDFexe = "C:\Program Files (x86)\Adobe\Acrobat 11.0\Acrobat\Acrobat.exe"

I see! yep that changed that

Works like a charm now.

 

[Ben171]

Wondering if you can help me with a similar problem - or whether i would have to create a new thread for this.

This is working perfectly for my Safety Certificates, however I have receiver Certificates that are slightly more complicated. The user enters a serial number, and the receiver certificate given depends on whether the serial number is between that range or not.

E.g. these are my certificate ranges (see below). if the user enters 2200490, then they need the first certificate.

The safety certificate works in the following way... the user enters a batch number, which is stored in the database sheet under the column L. the following formula is then ran to display the correct certificate to use for that entry.

=HYPERLINK(CONCATENATE("T:\pe_projects\Engineering\Receiver Mounted Stationary Screw Compressors\9_RM Database\Safety Valve Certs\",L2,".pdf"),L2)

This then displays at the end of the row.

How can i do this for the Receiver certificates, when it is depending on the Serial number (located in column G of the database sheet) being in between the ranges.

 

[John_w]

This is working perfectly for my Safety Certificates, however I have receiver Certificates that are slightly more complicated. The user enters a serial number, and the receiver certificate given depends on whether the serial number is between that range or not.

Similar to the previous macro - just the way it searches for the Certificate is different. Please start a new thread if it doesn't work.

Public Sub Print_Certificate_PDF()

    Dim CertificateNumber As String
    Dim lastRow As Long, r As Long, foundRow As Long
    Dim parts As Variant
    Dim PDFfile As String, printStatus As String
    Dim printerName As String
    
    printerName = ""                    'use default printer
    'printerName = "The printer name"    'use specific printer

    CertificateNumber = ThisWorkbook.Worksheets("PrintDocuments").Range("C6").Value
    With ThisWorkbook.Worksheets("Database")
        'Look for Certificate in column H
        lastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
        r = 2
        foundRow = 0
        While r <= lastRow And foundRow = 0
            parts = Split(.Cells(r, "H").Value, "-")
            If CertificateNumber >= CLng(parts(0)) And CertificateNumber <= CLng(parts(1)) Then foundRow = r
            r = r + 1
        Wend
        If foundRow <> 0 Then
            PDFfile = HyperlinkLocation(.Cells(foundRow, "R"))
            printStatus = Shell_Print_PDF(PDFfile, printerName)
            If printStatus = "" Then
                MsgBox "Printed " & PDFfile & " for Certificate Number " & CertificateNumber, vbInformation
            Else
                MsgBox "Not printed " & PDFfile & " for Certificate Number " & CertificateNumber & vbCrLf & vbCrLf & _
                       printStatus, vbExclamation
            End If
        Else
            MsgBox "Certificate Number " & CertificateNumber & " not found", vbExclamation
        End If
    End With
    
End Sub

 

[Ben171]

Similar to the previous macro - just the way it searches for the Certificate is different. Please start a new thread if it doesn't work.

Public Sub Print_Certificate_PDF()

    Dim CertificateNumber As String
    Dim lastRow As Long, r As Long, foundRow As Long
    Dim parts As Variant
    Dim PDFfile As String, printStatus As String
    Dim printerName As String
   
    printerName = ""                    'use default printer
    'printerName = "The printer name"    'use specific printer

    CertificateNumber = ThisWorkbook.Worksheets("PrintDocuments").Range("C6").Value
    With ThisWorkbook.Worksheets("Database")
        'Look for Certificate in column H
        lastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
        r = 2
        foundRow = 0
        While r <= lastRow And foundRow = 0
            parts = Split(.Cells(r, "H").Value, "-")
            If CertificateNumber >= CLng(parts(0)) And CertificateNumber <= CLng(parts(1)) Then foundRow = r
            r = r + 1
        Wend
        If foundRow <> 0 Then
            PDFfile = HyperlinkLocation(.Cells(foundRow, "R"))
            printStatus = Shell_Print_PDF(PDFfile, printerName)
            If printStatus = "" Then
                MsgBox "Printed " & PDFfile & " for Certificate Number " & CertificateNumber, vbInformation
            Else
                MsgBox "Not printed " & PDFfile & " for Certificate Number " & CertificateNumber & vbCrLf & vbCrLf & _
                       printStatus, vbExclamation
            End If
        Else
            MsgBox "Certificate Number " & CertificateNumber & " not found", vbExclamation
        End If
    End With
   
End Sub

Thanks for this, unfortunately it doesn't run has a mismatch error. Don't think it's exactly what i am looking for anyway.

I would like it to be the exact same as we did for the previous PDFs. e.g. User enters a works order number in cell C3 in PrintDocuments sheet, then it finds that row of database and prints the correct Certificate.

I managed to get these PDF's across to the database using the following formula.

=IFERROR(HYPERLINK("T:\pe_projects\Engineering\Receiver Mounted Stationary Screw Compressors\9_RM Database\Receiever Certs\"&LOOKUP(2,1/((--G2)>=(--ReceiverData!$A$2:$A$6))/((--G2)<=( --ReceiverData!$B$2:$B$6)),ReceiverData!$A$2:$A$6)&" - "&LOOKUP(2,1/((--G2)>=(--ReceiverData!$A$2:$A$6))/((--G2)<=( --ReceiverData!$B$2:$B$6)),ReceiverData!$B$2:$B$6)&".pdf",LOOKUP(2,1/((--G2)>=(--ReceiverData!$A$2:$A$6))/((--G2)<=( --ReceiverData!$B$2:$B$6)),ReceiverData!$A$2:$A$6)&" - "&LOOKUP(2,1/((--G2)>=(--ReceiverData!$A$2:$A$6))/((--G2)<=( --ReceiverData!$B$2:$B$6)),ReceiverData!$B$2:$B$6)&".pdf"),"")

So now my last two rows of the database looks like this.

The code for printing Column R you helped me with works great. How do i edit this to cater for Column S?

I have tried simply changing the column R to S in the code but sadly does not work. Opens Adobe but does not open the PDF nor print it

 

[John_w]

I would like it to be the exact same as we did for the previous PDFs. e.g. User enters a works order number in cell C3 in PrintDocuments sheet, then it finds that row of database and prints the correct Certificate.

It can't be the same, because you showed column H, which the macro searches, contains start and end numbers separated by "-", hence the Split function is used to separate the two numbers and see if the certificate number (read from C6, not C3, in PrintDocuments) is within the range and then gets the certificate pdf file from column R.

I think it's best to start a new thread.

 

[mageeg]

It can't be the same, because you showed column H, which the macro searches, contains start and end numbers separated by "-", hence the Split function is used to separate the two numbers and see if the certificate number (read from C6, not C3, in PrintDocuments) is within the range and then gets the certificate pdf file from column R.

I think it's best to start a new thread.

Thank you no problem I will start a new thread.

I think i've thought of an easier method anyway, which would be to just lookup the correct pdf name through vlookup, and store it in the database as plain text. Then just create a macro to go to the location where these certificates are stored, and then just finds the correct name which was stored through the lookup, then open and prints it.