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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,539
Office Version
  1. 365
Platform
  1. Windows
To create the string you can do it like this as an example:

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

Ben171

New Member
Joined
Jul 2, 2021
Messages
33
To create the string you can do it like this as an example:

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

Well-known Member
Joined
Oct 20, 2009
Messages
8,539
Office Version
  1. 365
Platform
  1. Windows
Do you mean like this?

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

New Member
Joined
Jul 2, 2021
Messages
33

ADVERTISEMENT

Do you mean like this?

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

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

New Member
Joined
Jul 2, 2021
Messages
33

ADVERTISEMENT

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?

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

1626775959028.png


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

1626775991933.png


the following pdf is located in column S

1626776020336.png
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,539
Office Version
  1. 365
Platform
  1. Windows
Add this line after the myMatch line:

VBA Code:
MsgBox myMatch

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

Ben171

New Member
Joined
Jul 2, 2021
Messages
33
Add this line after the myMatch line:

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

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

Forum statistics

Threads
1,137,059
Messages
5,679,369
Members
419,824
Latest member
Mercy kiara

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