In Excel VBA, open .txt with Word?

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

I have a macro in my workbook that returns a(ll) hyperlink(s) to the
requested Word document(s) using the ActiveCell.Text as the document name.
It works great - except now I have to apply it to a bunch of .txt files.
These open in Notepad - I need Word for functionality when the document is
open.

Is there a way I can set a "When Clicked" event so that, when one of these
hyperlinks is clicked on, a macro will launch Word and open the selected
file? I'd probably have to remove the hyperlink formatting, I think, so my
returned file name could be used as the doc name.

Am I asking too much? Is there an easier way?

Ed
 
You could set the hyperlink to the cell with the hyperlink. Then use the
followHyperlink event to do what you want.
 
Please excuse my slowness, Tom, but I'm not following this too well. The
Help files show the FollowHyperlink event as a complete and separate macro
Sub. So I take a "helper" cell, and make it point to the hyperlink, and set
an On_Click in that cell that calls the FollowHyperlink sub? Sorry if I've
managed to mangle something that should be easy.

Ed
 
FollowHyperlink is an event that fires when a hyperlink is clicked on that
page

From help:
Occurs when you click any hyperlink on a worksheet. For application- and
workbook-level events, see the SheetFollowHyperlink event.

Syntax

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Target Required Hyperlink. A Hyperlink object that represents the
destination of the hyperlink.

--------------------------



I put in a hyperlink and had it point to the cell containing the hyperlink.



I put in this event for the sheet

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
MsgBox Target.SubAddress
End Sub


It displayed Sheet1!E6 which is the address of the cell with the
hyperlink. I could use that information in a case statement to open a
specific text file in word using automation.

Beyond triggering the FollowHyperlink event, there was not change in the
worksheet, since the focus stayed on that cell. If the focus was not on that
cell, it moved to that cell.

--

Regards,

Tom Ogilvy
 
Thanks, Tom. I appreciate your assistance and patience. I'll work on this
one and scream if my spreadsheet starts smoking!

Ed
 
Back
Top