Looking for a macro to open a pdf file based on a cell value please

[A440]

In my worksheet I have a macro that saves the data to a pdf file.
I need a macro that will open one of the saved pdf files based on the value in a cell.
I have tried searching for code but I can't find one that does exactly what I want.
Sample file attached. Many thanks in advance.

[bakerman2]

For starters.

Sub Call_PDF()
On Error Resume Next
With Sheets("Sheet1")
    fName = "PO#" & " " & .Range("F6") & " " & .Range("G6") & " " & .Range("H6") & ".pdf"
End With
ThisWorkbook.FollowHyperlink "C:\Users\Al\Dropbox\Backup\PO\" & fName
End Sub

[A440]

Hi bakerman2, Thanks for your reply but sadly nothing at all happens!!!

[bakerman2]

If fName doesn't reflect the correct filename then nothing happens indeed.

Sub Call_PDF()
On Error Resume Next
With Sheets("Sheet1")
    fName = "PO#" & " " & .Range("F6") & " " & .Range("G6") & " " & .Range("H6") & ".pdf"
    msgbox fName
End With
ThisWorkbook.FollowHyperlink "C:\Users\Al\Dropbox\Backup\PO\" & fName
End Sub

Does the msgbox reflect correct filename ?

[A440]

Hi bakerman2, Yes, message box displays the correct file name.

[bakerman2]

Another one that works for me.

Sub tst()
    fPath = "C:\Users\Al\Dropbox\Backup\PO"
    With Sheets("Sheet1")
        fName = "PO#" & " " & .Range("F6") & " " & .Range("G6") & " " & .Range("H6") & ".pdf"
    End With
    ShowPDF = Shell("C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe" & " " & fPath & "\" & fName, 4)
End Sub

[A440]

Hi bakerman2, Sorry. This time it will start acrobat reader dc but with the error message "There was an error opening this document. This file cannot be found.
I have tried all your suggestions in two workbooks, each macro gives the same result for each workbook.

[bakerman2]

I'm 200% certain that both codes work so either your filename isn't correct or the path you provided in your example file is wrong.

[A440]

Ok, I'll do some investigation. Thank you for your help today, much appreciated.

[A440]

Hi bakerman2, I had a good look at this last night and did find one or two issues with the file names, trailing spaces and the routine I use to save my files to pdf actually adds - in between the ranges. However I have tried using the insert hyperlink method only to find that they will not work either, error message saying "Cannot open the specified file", I also tried using hyperlinks to numerous pdf files not created by the Excel macro I use to save to pdf and they all open fine. Do you think that the macro I use to save Excel to pdf can be rectified to make them work?

[mc84excel]

Does this work?

Option Explicit

Private Const mstrcPATH_SEPARATOR As String = "\"

Public Sub Test()
    Dim strErrMsg As String
    Dim strPathPDFs As String
    Dim strFileNamePDF As String
    Dim strFullNameReader As String

    ' set values
    With Worksheets("Sheet1")
        strPathPDFs = .Range("E12").Value2
        strFullNameReader = .Range("E10").Value2
        strFileNamePDF = "PO#" & " " & .Range("F6").Value & " " & .Range("G6").Value & " " & .Range("H6").Value & ".pdf"
    End With

    ' verify exist
    strErrMsg = "PDF Reader program not found at " & strFullNameReader
    If Not fnblnExistsFileFolder(strFullNameReader) Then
        GoTo ExitProcedure
    End If
    strErrMsg = "PDF output path not found at " & strPathPDFs
    If Not fnblnExistsFileFolder(strPathPDFs) Then
        GoTo ExitProcedure
    End If

    ' ensure path separatored and fix for spaces when used in Shell
    strPathPDFs = fnstrGetSeparatoredPath(strPathPDFs)
    strFullNameReader = """" & strFullNameReader & """"

    ' ensure pdf exists
    strErrMsg = "PDF file was not found at " & strPathPDFs & strFileNamePDF
    If Not fnblnExistsFileFolder(strPathPDFs & strFileNamePDF) Then
        GoTo ExitProcedure
    End If
    strErrMsg = vbNullString

    ' open Pdf
    Shell strFullNameReader & " " & """" & strPathPDFs & strFileNamePDF & """", vbNormalFocus

ExitProcedure:
    If LenB(strErrMsg) Then
        MsgBox strErrMsg, vbExclamation
    End If
End Sub

Public Function fnblnExistsFileFolder(ByRef strFullName As String) As Boolean
'adapted from function written by Ken Puls (www.excelguru.ca)
    If LenB(strFullName) Then
        On Error Resume Next
        fnblnExistsFileFolder = LenB(Dir(strFullName, 31))
        On Error GoTo 0
    End If
End Function

Public Function fnstrGetSeparatoredPath(ByVal strDirPath As String, Optional ByVal blnInvert As Boolean) As String
'/ ensures folder path ends in path separator (aka trailing backslash)
'/ doesn't detect garbage input, requires a Path arg
    Dim blnChange As Boolean

    strDirPath = Trim$(strDirPath)
    If LenB(strDirPath) Then
        blnChange = ((Right$(strDirPath, 1) = mstrcPATH_SEPARATOR) = blnInvert)
        If Not blnChange Then
            fnstrGetSeparatoredPath = strDirPath
        ElseIf Not blnInvert Then
            'add trailing separator
            fnstrGetSeparatoredPath = strDirPath & mstrcPATH_SEPARATOR
        Else
            'remove last character
            fnstrGetSeparatoredPath = Left$(strDirPath, Len(strDirPath) - 1)
        End If
    End If
End Function

[bakerman2]

Post the code U use to save to pdf.

[A440]

Hi bakerman2, I have attached a workbook with the code in a module as I don't seem to be able to post the code for some reason. The cell refs will all be different to the sample file I sent you in the first instance.

[A440]

mc84excel, Thanks for your code, but no luck, I will open other files but not ones created by the macro I have used to create pdf from my worksheet. Many thanks anyway.

[bakerman2]

1. Where does 'currentmonth' come from ??

2. Where is 'AlwaysOverwritePDF' set ??

[A440]

Hi bakerman2, I am so sorry I posted the wrong code in the workbook this morning, please see new Revised Save2PDF file.
I'm afraid I don't know what any of the code really mean, I can change the cell refs and file paths, that's about it.

[A440]

bakerman2, mc84excel, I have actually got mc84excel's code to work this evening, have renamed all my files without the hyphens and removed the hyphens from the macro that saves to pdf from worksheet. I will do further checks before I mark thread as solved. Fingers x

[A440]

mc84excel, Many thanks for your code, I now have it working. Much appreciated.

[A440]

bakerman2, Thank you for your help I have it sorted now with mc84excel's code. Thank you for your time.

[bakerman2]

You're welcome and thanks for rep+.