Thread: Link to Embedded Object (OLE)

Hi,
This question has been raised numerous times on different forums but has not been given an adequate answer.

I have embedded objects - a number of pdf files - on an empty worksheet in excel. I would like to create a number of hyperlinks (or similar) on a seperate worksheet within the same file that open each individual file.
i.e.:
link 1 - opens pdf1
link 2 - opens pdf2
etc.

A normal hyperlink simply links to a designated cell - however the embedded objects are an icon that floats above the cell so excel doesnt recognise the object.

is it possible to be able to click on the link which will directly open up the document, but not divert you to the seperate worksheet where the .pdfs are embedded?

im thinking i could create a macros/VBA button to do it? this i dont know how to do so would appreciate some help - otherwise if theres any other solutions please try to help - im desperate!

I could definitely see the value of this question. You are trying to containerize all your pdf's inside a workbook, so that the user doesn't have a million files to look at.

I was wondering if this question had been answered. But maybe there is another way to ask the question. Is there a way for excel to recognize an embedded object so that a hyperlink could point to it? Could the embedded object be assigned a name so that it appears as an object when you try to create a hyperlink in the same document?

Does anyone have the answer to these questions?

Here's an OK, not great, way to do this. The user clicks the cell referring to the file, then clicks a "View File" button. If you make changes to the list of files (other than adding to the end), it starts to fall apart.

1. Create a list of the file names in column A on Sheet 1.
2. Create this VBA macro:

Code:

Sub ActivateObj()

Dim myCell As Range
Dim mySheet As Object
Dim myFile As Object
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set myCell = ActiveCell
Set mySheet = ActiveSheet
If mySheet.Name <> "Sheet1" Or myCell.Column <> 1 Or myCell.Value = "" Then
    MsgBox ("Please select a file in column A on Sheet 1")
    Exit Sub
End If
On Error GoTo noObject
Set myFile = ThisWorkbook.Sheets(2).OLEObjects(Evaluate("R_" & myCell.Row))
On Error Resume Next
myFile.Verb Verb:=xlVerbOpen
Set myFile = Nothing
mySheet.Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub

noObject:
MsgBox ("No file embedded for " & ActiveCell.Value)

End Sub

3. Place a button on Sheet 1 labeled "View File", and associate it with the macro.
4. For each file name,

a. Note the row number that the file is on

b. Embed the file on Sheet 2

c. Single click the embedded file icon

d. Define a name in the form "R_n", where n is the row number from 4.a.

Another way to do this that doesn't break if you change the list is to use the name of the file as the defined name of the embedded object in step 4d. In that case, you'd replace this line:

Code:

Set myFile = ThisWorkbook.Sheets(2).OLEObjects(Evaluate("R_" & myCell.Row))

with this:

Code:

Set myFile = ThisWorkbook.Sheets(2).OLEObjects(Evaluate(myCell.Value))

The problem with this approach is that defined names can only contain letters, underscores (_), and numbers; file names can have many other characters in them.