Replacing file name with cell reference

[Ben171]

Hi, I am trying to open PDF's through VBA code, the following piece of code works well..

Function OpenAnyFile(strPath As String)
  Set objShell = CreateObject("Shell.Application")
  objShell.Open (strPath)
End Function

Sub TestPDF()
  Dim pdfPath As String
  pdfPath = "C:\TestPDF\2200487 - 2200546.pdf"
  Call OpenAnyFile(pdfPath)
End Sub

However what i would like to do is change the name of the pdf file it is opening (in this case it is 2200487 - 2200546.pdf) to a cell reference. e.g. Whatever is in the the cell S2 of the sheet "Database". (there will be a file in this location called whatever is in the S column)
I assume this should be fairly straight forward such as taking out the file name from the location and add something like & ("S2") but couldn't get this to work (Im new!)

Any help would be greatly appreciated.

that is the main thing i need help with, but if you would like to go one step further, I would like the user to be able to enter an order number in a different cell, lets say C3 in a sheet called "PrintDocuments", then it searches through the order number column which is column D of the sheet "Database", then if it finds this works order number, open the PDF that is in column S at the end the row.

I began to wrote some code to do this, but again struggling with the open PDF bit. Thanks

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 ("PDF Printed")
Else
MsgBox ("Works Order Number Not Found")
End If

End Sub

 

[steve the fish]

So just add a message box in the true part if you want one there. The pdf opening will be a giveaway its worked though.

 

[Ben171]

So just add a message box in the true part if you want one there. The pdf opening will be a giveaway its worked though.

Thanks for your help this week.

I have now got some working code which prints the correct PDF. Just thought i'd post it here incase anyone was interested,

I switched back to your method, not sure if i implemented this code in the best way? but it works!

Public 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 Function PrintPDF(xlHwnd As Long, FileName As String) As Boolean
Dim X As Long

On Error Resume Next
X = ShellExecute(xlHwnd, "Print", FileName, 0&, 0&, 3)

If Err.Number > 0 Then
    MsgBox Err.Number & ": " & Err.Description
    PrintPDF = False
Else
    PrintPDF = True
End If
On Error GoTo 0
End Function
 

Sub TESTPrintSpecificPDF()

Dim strPath As String
Dim WorksOrder As String

'Get user entered WorksOrder Number

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


If Len(WorksOrder) > 0 Then
    With Sheets("Database")
        myMatch = Application.Match(WorksOrder, .Columns(4), 0)
        If IsNumeric(myMatch) Then
            strPath = "C:\Test\" & .Cells(myMatch, 19)
        Else
        MsgBox "Works Order Number not found"
        End If
    End With
End If

If Not PrintPDF(0, strPath) Then
    MsgBox "Printing failed"
End If

End Sub