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
 

steve the fish

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

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Ben171

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

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

Forum statistics

Threads
1,137,060
Messages
5,679,387
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