Hyperlinking to PDF documents

[musoguy]

My question is threefold, and concerning open PDF files (in acrobat, not Excel) using Excel.

Thanks to another thread I have the basic code I need. This code will take the value of the active cell (which is a filename) and open the corresponding PDF document of the same name.

Sub OpenPDF()

Dim fName As String
Dim fExt As String
Dim fPath As String
Dim fFullPath As String
fName = ActiveCell.Value
fPath = "M:\Books\"
fExt = ".pdf"
fFullPath = fPath & fName & fExt
ActiveWorkbook.FollowHyperlink Address:=fFullPath, NewWindow:=True

End Sub

However there are a couple of instances where I am getting an error.

Firstly, there are close to a thousand books titles listed (in column A). Some of these, though copying and pasting, have ended up with a space after the final word. This means for example that the macro would try to open book_1 .pdf (space after the 1), while the file is actually called book_1.pdf (no space after the 1). This is throwing back an error. Rather than having to go through all the book titles one at a time to see which have a space after the final word and then delete the spaces, is there any way to tell the code to ignore a space if it appears at the end of the cell's value? Or if it is easier to delete a space if it finds one at the end? (Bearing in mind that there are spaces often in between words in the titles that need to stay).

Secondly, Once I have finished the document, it will go out to a number of people. The excel document lives in the same folder as the PDF library. However, as different people open the excel document either on the DVD they receive or after copying the folder from the DVD, the hyperlink path will presumably no longer link correctly as while it is on the M drive on my system, it is unlikely it will be on theirs. As the excel document will always be in the same folder as the destination PDF files, is there any way to specify this with the link, rather than have to give a drive letter. Hope that wasn't too convoluted!

Finally, I have a feeling the answer to this question is no as I am talking about two unrelated programs, but it's worth a shot! Is there any way to not only open a PDF from Excel, but also specify what bookmark within he PDF to open it at? For example if I have all the chapters in a PDF bookmarked, can I specify a link in excel for Book 1 chapter 5?

Thanks in advance for any help you can offer!

James

 

[musoguy]

I just remembered my question was meant to be fourfold! If for some reason the pdf that the user hyperlinks to does not exist, I really need a fail safe, where a msgbox would pop up telling the user that the pdf does not exist. Thanks! James

 

[musoguy]

I have figured out the first part of my question: How to delete the spaces. I have also figured out the fourth part on how to let the user know if the pdf file doesn't exist (thanks to Sal Paradise for this part, from another thread)
So my code now reads:

Sub OpenPDF()

Dim fName As String
Dim fExt As String
Dim fPath As String
Dim fFullPath As String

    
strOld = ActiveCell
strNew = WorksheetFunction.Trim(strOld)
ActiveCell = strNew

On Error GoTo NoFind
    
fName = ActiveCell.Value
fPath = "M:\Books\" 
fExt = ".pdf"
fFullPath = fPath & fName & fExt
ActiveWorkbook.FollowHyperlink Address:=fFullPath, NewWindow:=True

Exit Sub

NoFind:
    Range("$A$10").Select
    MsgBox ("This book cannot be found. Please make sure the catalogued name corresponds precisely with the file name of the PDF document.")
    
End Sub

I am still completely stumped by parts 2 and 3 though. Any thoughts?

 

[Ivan F Moala]

If workbook and PDFs in same Dir then Use

fPath = ThisWorkbook.Path

To get the Path.

 

[musoguy]

Thanks for your reply Ivan. I've just tried the code you recommended and it is not finding any of the PDFs. The workbook and PDFs are in the same folder. Not sure why as what you wrote makes perfect sense to me!

James

 

[Ivan F Moala]

try

fPath = ThisWorkbook.Path & Application.PathSeparator

 

[Ivan F Moala]

As for your last Q, there are a number of ways ....

I'll give you the easiest, although it is not what I would use as it hard codes the Adobe program location.

Sub ShellPDF()
'// Opens PDF @ Numbered Page
'// Ivan F Moala
Dim RetVal As Long

Dim strPDFFile As String '// <path to PDF file>
Dim strPrgm As String    '// <path to Acrobat>
Dim strParam As String   '// <open parameter>=OpenActions>

'//////////////////////////////////////////////////////////////////////////////
'vbHide             0 Window is hidden and focus is passed
                      'to the hidden window. The vbHide constant
                      'is not applicable on Macintosh platforms.
'vbNormalFocus      1 Window has focus and is restored to its
                      'original size and position.
'vbMinimizedFocus   2 Window is displayed as an icon with focus.
'vbMaximizedFocus   3 Window is maximized with focus.
'vbNormalNoFocus    4 Window is restored to its most recent size and position.
                      'The currently active window remains active.
'vbMinimizedNoFocus 6 Window is displayed as an icon. The currently
                      'active window remains active.
'////////////////////////////////////////////////////////////////////////////////

'// <path to Acrobat> /A "<open parameter>=OpenActions" "<path to PDF file>"
'// Note SPACES

strPrgm = "C:\Program Files\Adobe\Reader 8.0\Reader\AcroRd32.exe"
strPDFFile = " C:\ExcelFiles\Help\PDFToOpen.pdf"
strParam = " /A page=6=OpenActions" '// Opens @ Page 6

RetVal = Shell(strPrgm & strParam & strPDFFile, 1)


End Sub

 

[musoguy]

Thank you so much Ivan. As is usually the case, when you see the solution it makes perfect sense! Is there any way you can show me a solution that doesn't hard code the Adobe program location? The reason I ask is that the workbook and library will be distributed to a number of users, and they may well have different versions of Acrobat, or indeed only have a version of Acrobat reader. They may also have a 64bit system in which case the file would be somewhere in the Program Files (x86) folder as opposed to the regular Program Files folder Therefore it would be great if the macro had a way to search for the program location, and open Acrobat if it exists, if it doesn't exist look for Acrobat Reader, and as a last resort show a download hyperlink to the reader on the Adobe website if none of the above are on their system. If this is a very complicated thing to do (as it looks to me), and you don't have the time or inclination to write the code, I could always write a number of Macros that will each work dependent on a different computer setup. That way each macro could just call the next one if acrobat cannot be found using that macros settings.

Thanks again Ivan for the help so far,

James

 

[musoguy]

try

fPath = ThisWorkbook.Path & Application.PathSeparator

I forgot to say that this code works great. Thank you!

 

[Ivan F Moala]

Hi James

To get the users Adobe path
Have a look @ the write up I did here

http://www.dailydoseofexcel.com/archives/2005/07/29/get-executable-path/