'How do you open a pdf file with VBA code for a relative file path?

I am trying to find the command and correct coding to open a PDF file with a relative file path to the active excel file. The code below works fine as a link directly to the file. However, I just need this code snippet to find the PDF file that is sitting in the same file as the opened excel file and open accordingly.

Sub OpeningPDF()
    'ThisWorkbook.FollowHyperlink "C:\Users\Michael\My Documents\totals\copy.pdf"
End Sub

I tried working with ThisWorkbook.path but nothing I tried with that worked or seemed to be outdate. Any help in this matter would be much appreciated.

excel pdf vba

Solution 1:^[1]

I have found two solutions to this:

The first one is using the built-in Shell() function. This should automatically resolve the relative path (relative to the applications current working directory):

Public Sub StartExeWithArgument()
    Dim strFilename As String

    strFilename = "../folder/file.pdf"

    Call Shell(strFilename, vbNormalFocus)
End Sub

The second one uses the Shell.Application COM Object and will basically do the same as the first one.

Sub runit()
   Dim Shex As Object
   Set Shex = CreateObject("Shell.Application")
   tgtfile = "../folder/file.pdf"
   Shex.Open (tgtfile)
End Sub

Solution 2:^[2]

If you start with ThisWorkbook.Path and your relative-reference, then trim a layer off for every "..\" in the relative reference, you'll get the path.

Function RelativeToAbsolutePath(ByVal RelativePath As String) AS String
    Dim TempStart AS String, TempEnd AS String
    TempStart = ThisWorkbook.Path
    TempEnd = RelativePath
    If Left(TempEnd,1) = "\" Then TempEnd = Mid(TempEnd,1)

    RelativeToAbsolutePath = ""
    On Error GoTo FuncErr

    While Left(TempEnd,3)="..\" AND InStrRev(TempStart,"\")>0
        TempStart = Left(TempStart,InStrRev(TempStart,"\")-1) 'Remove 1 layer from Workbook path
        TempEnd = Mid(TempEnd,4) 'Remove 1 instance of "..\"
    Wend

    RelativeToAbsolutePath = TempStart & "\" & TempEnd 'Stitch it all together
FuncErr: 'You may want a DIR(..) check to see if the file actually exists?
End Function

You can then open it with Shell

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	rollstuhlfahrer
Solution 2	Chronocidal

'How do you open a pdf file with VBA code for a relative file path?

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]