VBA - How to locate and print file

[mageeg]

I have an excel userform in which users enter data then it is transferred to a sheet called "Database"

One field I have in the database is Column S which is "Certificate". This is a PDF file that needs to be automatically printed.

This certificate is stored in the following location: "T:\Engineering\Certs\"

What i want is a button which automatically finds the certificate and prints it. i figured the best way to find the right certificate is by using a unique field in the database which is "works order number". Therefore i would like users to enter Works Order number in Cell "C3" of the "PrintDocuments" sheet. Like below.

I will then have a button, which finds this works order number in the database, then prints the certificate from column S by opening the location "T:\Engineering\Certs\" then finds the name of the PDF as it is stored in column S. opens it and prints it. I have made a start to this code, but i'm not sure how to do the bit in the middle, which is to go to the location and print the correct pdf

Any help would be appreciated, see 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)
 
'works order number is found in column D of database sheet

If WorksOrder = sh.Range("D" & iRow).Value Then

Found = True

'insert pdf code here




Exit Do

End If

iRow = iRow + 1

Loop


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

End Sub

 

[GWteB]

Your request is very similar to the request in the thread below, you really should have a look at the code that has become available there.

VBA code - Print a PDF document in corresponding Cell

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...

www.mrexcel.com