Hyperlink to embedded pdf

[atwoodmk]

I would like to use hyperlinks to open embedded pdf files. If I insert a pdf as an embedded object it creates an icon. I cannot find a way to change this icon to a hyperlink. I can easily create hyperlnks to pdf's that are in other folders but I need to embed the files so the spreadsheet is portable without worrying about broken links. This is my first post so I apologize in advance if this is to vague. Any help would be greatly appreciated.

Thanks

 

[vconfused]

You'd need to use code. You can embed the object in a different sheet. Then insert a hyperlink to go to that sheet.

Then you can right-click on the new sheet's tab and choose View Code and insert something like the following:

Private Sub Worksheet_Activate()
ActiveSheet.Shapes("Object 1").Select
Selection.Verb Verb:=xlPrimary
End Sub

What this will do is after the hyperlink is clicked, the new sheet will open and as it opens it selects "Object 1" and then opens it. Change "Object 1" to whatever is in the Name box after you embed the pdf.

 

[atwoodmk]

Thank you very much for the reply. This half-solved my problem. When I click on the worksheet tab that contains the embedded pdf, it opens automatically however, the hyperlink opens the other worksheet but the pdf does not open without also clicking on the embedded pdf icon. Once again, thanks for the advice and any further help would be greatly appreciated.

 

[vconfused]

That doesn't make sense. You are saying that after embedding the pdf onto the second sheet, adding the code to the second sheet, and adding a link from the first sheet to the second sheet, then click on the link, it takes you to the second sheet but it does not open the PDF? But after doing all of this, if you click on the second sheet's tab, then it automatically opens the PDF? That doesn't make sense. Either way (clicking on the sheet tab or clicking on the link to open the second sheet) should open the PDF as the code runs when the sheet is activated. Putting the link on the sheet to itself will NOT work -- the link must be from one sheet to another, with the second having the pdf.

Is it possible that it was already opened before trying the link?

 

[atwoodmk]

Thanks again for the help. Yes, I believe I've done exactly as you suggest. The hyperlink is on the first worksheet and the pdf is embedded in the second worksheet. The hyperlink opens the second worksheet but does not open the pdf. If the first worksheet is open and I click on the second worksheet's tab, the pdf automatically opens. If you are willing, I could send a copy of the spreadsheet to evaluate. It is 127k in size. Once again, thanks for the help.

 

[vconfused]

Hmm. Looks like clicking on a hyperlink in Excel 97 and 2000 do not activate the sheet. It does in 2003, not sure about 2002. Using code is a workaround. Adding:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Selection.Address
Case "$B$2": Call openPDF(Selection.Value)
Case "$B$3": Call openPDF(Selection.Value)
End Select
End Sub

to the Main sheet with new cases for each cell and inserting a module with:

Sub openPDF(strSheet As String)
Sheets(strSheet).Select
ActiveSheet.Shapes("Object 1").Select
Selection.Verb Verb:=xlPrimary
Sheets("Main").Select
End Sub

will work. You can modify the code so that it will run when any cell in column B is clicked, but then you will get an error (unless you modify it further) if you click on a cell that doesn't contain the text of a sheet name. Since you are using code, you could change it around so that you can insert all of the pdf's in one sheet, but that would get pretty messy. Since you have code, you could even hide all of the sheets and modify the code to unhide and rehide the sheets. (See the example I sent back to you.)