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

 

[Ben171]

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.

Oh, maybe that is where i am going wrong. I assumed if the works order number is matched, it should open the pdf located in column 19?

Is this because we haven't called the OpenAnyfile function?

 

[steve the fish]

Ah ok. You will need to add the code to do whatever you want to do with it. I may add a test first to make sure file exists. Such as:

If Dir(pdfPath) <> "" Then
'your code
End If

 

[Ben171]

Ah ok. You will need to add the code to do whatever you want to do with it. I may add a test first to make sure file exists. Such as:

In ideal world, i would like it to find the correct PDF and send it to the default printer.

However i was happy with taking babysteps to first just open the correct PDF.

However if the first option is easy enough to do that would be fantastic

 

[Ben171]

Ah ok. You will need to add the code to do whatever you want to do with it. I may add a test first to make sure file exists. Such as:

If Dir(pdfPath) <> "" Then
'your code
End If

Can confirm now i have got it to open the correct PDF depending on the works order number entered, by using the following

Dim pdfPath 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)
        MsgBox myMatch
        If IsNumeric(myMatch) Then
            pdfPath = "C:\Test\" & .Cells(myMatch, 19)
            Call OpenAnyFile(pdfPath)
        End If
    End With
End If

Any help with also printing the correct pdf to the default printer would be great.

Thanks

 

[Ben171]

Can confirm now i have got it to open the correct PDF depending on the works order number entered, by using the following

Dim pdfPath 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)
        MsgBox myMatch
        If IsNumeric(myMatch) Then
            pdfPath = "C:\Test\" & .Cells(myMatch, 19)
            Call OpenAnyFile(pdfPath)
        End If
    End With
End If

Any help with also printing the correct pdf to the default printer would be great.

Thanks

I believe I now even have it working with the first piece of code i originally put in the thread, thanks to your help.

Dim pdfPath As String
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
    With Sheets("Database")
        myMatch = Application.Match(WorksOrder, .Columns(4), 0)
        If IsNumeric(myMatch) Then
            pdfPath = "C:\Test\" & .Cells(myMatch, 19)
            Call OpenAnyFile(pdfPath)
        End If
    End With
Found = True


Exit Do

End If

iRow = iRow + 1

Loop



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

But again, any help with printing the found PDF would be amazing.

Thanks

 

[Ben171]

As this has gotten slightly off-topic from the question, i have created a new thread for this.

VBA - Can open PDF's, but how to print?

Hi i have been working on this for a week or so and I am nearly there. I got PDF's that are linked to a works order number. I would like the user to be able to enter the works order number and it will print the desired PDF. I have gotten so far, in the fact that you can enter the works order...

www.mrexcel.com

 

[steve the fish]

But you shouldnt use the code you first used. A loop is unnecessary in this instance. You are looping to find the correct row. The match finds the row.

 

[Ben171]

But you shouldnt use the code you first used. A loop is unnecessary in this instance. You are looping to find the correct row. The match finds the row.

oh whoops! makes sense, i missed that one. Is there any major negatives of doing it my way, or is it just inefficient?

So reverting back to the code you gave. How would I go about provide the message boxes stating certificate found, and works order number not found?

Only way i have experience in doing this is with a loop!

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

 

[steve the fish]

Both will work i suppose. The loop is slow and you will notice once you get past even small amounts of rows. To send a message box if the number is not found then the If IsNumeric line will be false so use an Else.

If IsNumeric(myMatch) Then
    pdfPath = "C:\Test\" & .Cells(myMatch, 19)
    Call OpenAnyFile(pdfPath)
Else
    MsgBox "Not Found"
End If

I dont know what you mean by certificate.

 

[Ben171]

Both will work i suppose. The loop is slow and you will notice once you get past even small amounts of rows. To send a message box if the number is not found then the If IsNumeric line will be false so use an Else.

If IsNumeric(myMatch) Then
    pdfPath = "C:\Test\" & .Cells(myMatch, 19)
    Call OpenAnyFile(pdfPath)
Else
    MsgBox "Not Found"
End If

I dont know what you mean by certificate.

Both will work i suppose. The loop is slow and you will notice once you get past even small amounts of rows. To send a message box if the number is not found then the If IsNumeric line will be false so use an Else.

If IsNumeric(myMatch) Then
    pdfPath = "C:\Test\" & .Cells(myMatch, 19)
    Call OpenAnyFile(pdfPath)
Else
    MsgBox "Not Found"
End If

I dont know what you mean by certificate.

Okay i see, if this could cause a problem in the future as more rows are added, it would be a good idea to change.

And sorry i wasn't clear. The pdf file is the Certificate.

Thanks alot for your help with this