Replacing file name with cell reference

Ben171

New Member
Joined
Jul 2, 2021
Messages
33
Hi, I am trying to open PDF's through VBA code, the following piece of code works well..

VBA Code:
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

Code:
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

New Member
Joined
Jul 2, 2021
Messages
33
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?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,539
Office Version
  1. 365
Platform
  1. Windows
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:

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

Ben171

New Member
Joined
Jul 2, 2021
Messages
33
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

New Member
Joined
Jul 2, 2021
Messages
33
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:

VBA Code:
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

VBA Code:
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

New Member
Joined
Jul 2, 2021
Messages
33

ADVERTISEMENT

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

VBA Code:
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.

VBA Code:
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

New Member
Joined
Jul 2, 2021
Messages
33
As this has gotten slightly off-topic from the question, i have created a new thread for this.

 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,539
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jul 2, 2021
Messages
33
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!

VBA Code:
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,539
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
Jul 2, 2021
Messages
33
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.

VBA Code:
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.

VBA Code:
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
 

Forum statistics

Threads
1,137,061
Messages
5,679,388
Members
419,825
Latest member
MegastarMagus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top