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]

Thanks Ivan. I'll give it a go and let you know how I get on.

 

[musoguy]

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 '// 

Dim strPrgm As String    '// 

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.
'////////////////////////////////////////////////////////////////////////////////

'// 
 /A "<open parameter="">=OpenActions" "
"
'// 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

Ivan, I've just tried the code you wrote on the link you sent me. It blows my mind! I have no idea why it works, but it does! Brilliant! In fact it is so ahead of where I am that I'm not even sure how to isolate the program path from the rest of the information in the MsgBox! I presume I want the variable strPrgm,which currently reads:
</open></open>

strPrgm = "C:\Program Files\Adobe\Reader 8.0\Reader\AcroRd32.exe"

<open parameter=""><open parameter="">instead to equal the file path that is found in the other macro you wrote, but I can't find it in the code:

Completely in awe by the way!
</open></open><open parameter=""><open parameter="">

</open></open>

 

[Ivan F Moala]

Use it like this...

     strPrgm = fnGetExePath(".pdf")

There are shorter ways to get the Path, but use this any way.

 

[musoguy]

Thanks Ivan. That worked great

James