PDA

View Full Version : Solved: Hyperlinking to Embedded Object



MWE
11-18-2005, 07:04 AM
Assume a Word doc, say, "A.Doc", has been inserted (as an object) into a spreadsheet on tab "B". It is easy to open the object by double clicking on the Icon, but is there a way to hyperlink to the embedded object?

Thanks

samohtwerdna
11-18-2005, 12:38 PM
I'm a little confused about what your asking here - but very interested.

Would you like to create a hyperlink that navigates to a new worksheet where an embedded object (A.DOC) is ? Or would this also work inside the worksheet that has the object?

I have a similar desire to use hyperlinks to embed objects into a spreadsheet on a temp sheet. So I would love to hear what answers you get.

Is there any way to get a click event on text in a cell without using Hyperlink?
Maybe that would solve our problems :dunno

MWE
11-18-2005, 12:56 PM
I'm a little confused about what your asking here - but very interested.

Would you like to create a hyperlink that navigates to a new worksheet where an embedded object (A.DOC) is ? Or would this also work inside the worksheet that has the object?

I have a similar desire to use hyperlinks to embed objects into a spreadsheet on a temp sheet. So I would love to hear what answers you get.

Is there any way to get a click event on text in a cell without using Hyperlink?
Maybe that would solve our problems :dunno
I primary need is to create a hyperlink from inside the workbook that leads to the inserted object and opens it. There are several workarounds, e.g., A.Doc initially resided outside the workbook; creating a hyperlink to it (manually of with VBA) was trivial. But that means that the "location" of A.Doc must remain consistent relative to the workbook -- not impossible, but a pain. If A.Doc is inside the workbook, all concerns about location and version management disappear. Directly clicking on the object is another workaround, but that is clumsy.

samohtwerdna
11-18-2005, 01:05 PM
MMM - I agree.

Still a little fuzzy - Do you care if the A.Doc is open in Word or embeded into Excel?

I assume that you are looking for something that will work with any embeded object's not just Word Doc's...right?

MWE
11-18-2005, 05:23 PM
MMM - I agree.

Still a little fuzzy - Do you care if the A.Doc is open in Word or embeded into Excel?

I assume that you are looking for something that will work with any embeded object's not just Word Doc's...right?
My preference is that A.Doc would be opened by Word (that makes it easier to make changes ... but also makes it possible for another user to make changes). The current need is Word docs, but you are correct assuming that I ultimately seek a method that will work with any embedded object.

JonPeltier
11-19-2005, 06:53 AM
Try this:

Sub GetEmbeddedWordObject()
Dim lReply As Long
Dim objOLE As OLEObject
On Error Resume Next
Set objOLE = ThisWorkbook.Worksheets("Sheet1").OLEObjects(1)
If Err.Number <> 0 Then
' problem with OLE Object
Exit Sub
End If

lReply = MsgBox("Do you want to Open in Word (Yes), or Edit in Place (No)?", _
vbQuestion + vbYesNoCancel)

Select Case lReply

Case vbYes
' To open in a Word instance
' like Right Click > Document Object > Open
objOLE.Verb xlVerbOpen

Case vbNo
' To activate in Excel as Word object
' like Right Click > Document Object > Edit
objOLE.Verb xlVerbPrimary
End Select

End Sub

MWE
11-19-2005, 08:04 AM
Try this:

Sub GetEmbeddedWordObject()
Dim lReply As Long
Dim objOLE As OLEObject
On Error Resume Next
Set objOLE = ThisWorkbook.Worksheets("Sheet1").OLEObjects(1)
If Err.Number <> 0 Then
' problem with OLE Object
Exit Sub
End If

lReply = MsgBox("Do you want to Open in Word (Yes), or Edit in Place (No)?", vbQuestion + vbYesNoCancel)

Select Case lReply

Case vbYes
' To open in a Word instance
' like Right Click > Document Object > Open
objOLE.Verb xlVerbOpen

Case vbNo
' To activate in Excel as Word object
' like Right Click > Document Object > Edit
objOLE.Verb xlVerbPrimary
End Select

End Sub
thanks. I will play with this and reply back with comments.

MWE
11-22-2005, 09:45 AM
Jon: your procedure was very helpful. It gave me the clues and hints to build a very useful general procedure to display embedded objects via VBA.

Thanks :thumb

MWE