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]

To create the string you can do it like this as an example:

pdfPath = "C:\TestPDF\" & Sheets("Sheet1").Range("A1").Value & ".pdf"

 

[Ben171]

To create the string you can do it like this as an example:

pdfPath = "C:\TestPDF\" & Sheets("Sheet1").Range("A1").Value & ".pdf"

Thank you, that works great. Any ideas on how i can set the range to whatever row in which the order number is found in?

 

[steve the fish]

Do you mean like this?

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
            pdfPath = "C:\TestPDF\" & .Cells(myMatch, 19) & ".pdf"
        End If
    End With
End If

 

[Ben171]

Do you mean like this?

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
            pdfPath = "C:\TestPDF\" & .Cells(myMatch, 19) & ".pdf"
        End If
    End With
End If

This looks like the sort of thing i am looking for!! been struggling with this for a days.

I have just tried this code but nothing is happening when i run it.

Could you help me integrate this with my sample code above, so that it loops through the database to find the works order number, which then opens the PDF at the end of that row in column S

 

[steve the fish]

You dont need a loop. If it does nothing then id suggest whatever is in C3 of the PrintDocuments sheet isnt present in column D of the database sheet.

 

[Ben171]

You dont need a loop. If it does nothing then id suggest whatever is in C3 of the PrintDocuments sheet isnt present in column D of the database sheet.

Perhaps i am doing something wrong?

Dim WorksOrder As String

'Get user entered WorksOrder Number

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


'Set worksheet
If Len(WorksOrder) > 0 Then
    With Sheets("Database")
        myMatch = Application.Match(WorksOrder, .Columns(4), 0)
        If IsNumeric(myMatch) Then
            pdfPath = "C:\TestPDF\" & .Cells(myMatch, 19)
        End If
    End With
End If

You can see below in C3 this works order number is entered:

and this number is also located in column D of the database sheet

the following pdf is located in column S

 

[steve the fish]

Add this line after the myMatch line:

MsgBox myMatch

Do you get a message box and if so what does it say?

 

[Ben171]

Add this line after the myMatch line:

MsgBox myMatch

Do you get a message box and if so what does it say?

Yes i get a message box, and it says "2", i assume this is because it has found it in the second row of the database sheet? (which is correct)

 

[steve the fish]

By the way i wouldnt expect the code to do anything as such until you insert the code to do whatever you want to do with the pdf string.