0

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.

Community
  • 1
  • 1
ClydeHopper
  • 11
  • 1
  • 2
  • 6

2 Answers2

0

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
rollstuhlfahrer
  • 3,988
  • 9
  • 25
  • 38
  • Thank you! I tried both and it seems like I must be missing something. The last one didn't do anything but the first one produced a "file not found" error message. – ClydeHopper Apr 02 '18 at 09:10
  • So say the name of my PDF file is "Copy" and it is in the same file folder as the excel file that I am running the VBA macros in. Which following variation is the correct if any? 1) strFilename = "../copy.pdf" 2) strFilename = "../totals/copy.pdf" 3) something entirely different. Also, are these slashes facing the right direction? Shouldn't I be using the backwards slashes? – ClydeHopper Apr 02 '18 at 09:12
0

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

Chronocidal
  • 6,827
  • 1
  • 12
  • 26
  • That makes sense but I have to admit that most of that goes over my head. How should the path script portion look if the PDF file is in the same folder as the excel file where the macro will be run? – ClydeHopper Apr 02 '18 at 08:53
  • I have adobe acrobat Pro automatically open my PDF files if that makes a difference. The name of the file in this case is just "copy.pdf" – ClydeHopper Apr 02 '18 at 08:55
  • If it's in the same folder as the workbook, then the path is just `ThisWorkbook.Path & "\copy.pdf"`. The above macro would produce that result from `RelativeToAbsolutePath("copy.pdf")` – Chronocidal Apr 03 '18 at 04:28
  • Thank you! So how should my command look to actually open the file using your function? – ClydeHopper Apr 03 '18 at 14:18
  • To open the file "copy.pdf" from the same directory as the Workbook: `CreateObject("Shell.Application").Open RelativeToAbsolutePath("copy.pdf")`, to open it from a folder within the same directory `CreateObject("Shell.Application").Open RelativeToAbsolutePath("FolderName\copy.pdf")` and to open from the folder above `CreateObject("Shell.Application").Open RelativeToAbsolutePath("..\copy.pdf")` – Chronocidal Apr 03 '18 at 16:01