How to create hyperlink from 2 cells, then open Word

[Phil]

Hello,

The purpose of this endeavour is to create a cell that when the user clicks
on it, it will open up a Word file that THAT particular cell represents.

FIRST STEP:
Take the values from Column D (Tract Number), then add a ".doc" extension to
it, then put the UNC filepath (\\server\data\reports) in front of all of that
and put it in Column E. See me example below:

File path plus Tract Number plus Extension

\\server\data\reports\ 7-5-065-085 .doc

.... to generate something like this:
\\server\data\reports\7-5-065-085.doc

SECOND STEP:
Use the value (only when the user clicks on the hyperlink) from the cell in
Column E and start up Word.

Can this be done?

Thanks in advance for all of your replies.

Phil.

 

[Miguel Zapico]

You may use HYPERLINK and string concatenation, like:
=HYPERLINK("\\server\data\reports\" & D1 & ".doc")
That will create a link that the user can click. You can even substitute
the text in quotes for other cell references.

Hope this helps,
Miguel.

 

[Gary''s Student]

In E1 enter:

=HYPERLINK("\server\data\reports\" & D1 & ".doc", "7-5-065-085")

 

[Phil]

Hi Miguel,

That gets me to the folder, but does not open the file in Word. Any other
ideas?

Phil

 

[Kevin Vaughn]

I have done something similar (but not exactly this) in one of my
spreadsheets. First, the formula I use (warning, it's long)

=IF(ISNA(LOOKUP(2,1/(1-ISBLANK(ColCount)))),"",HYPERLINK($B$1&LOOKUP(2,1/(1-ISBLANK(ColCount)),ColCount)&INDEX($B$2:$D$2,MATCH(LOOKUP(2,1/(1-ISBLANK(ColCount)),ColCount),ColCount,0)),LOOKUP(2,1/(1-ISBLANK(ColCount)),ColCount)))

One of the differences is instead of just dealing with .doc extensions, I
have .xls extensions and (just for the sake of having more than 2 types to
deal with) .mdb extension. I have these in seperate columns and I use a
dynamic named range to let me know how many columns I am dealing with. My
named range looks something like this (shouldn't it look exactly like this???)

=OFFSET(Links!$B9,,,1,COUNTA(Links!$2:$2))

The lookup part was something I adapted from previous posts on this forum
that was originally was intended to show the last value in a column (or row,
I can't remember) Mine just looks to see which of the 3 (or more) columns
is populated so that the formula will know which extension to use.

The UNC path I am using is in cell B1. The file extensions are in row 2.
And what is displayed in the cell is the filename (no path or extension.)

That seems to be the crux of it. Perhaps you can adapt it for your needs.

 

[Phil]

Hello Gary,

That only gets me to the folder. It does not open up Word with that
particular document, which is the second step that I need done.

Any other ideas?

Phil.

 

[Miguel Zapico]

It should open the word file, try to hard code the document name in the
formula, and check if that works. If so, you may then build that name in a
different cell and use the formula over that cell, but the formula should
work as it is if the document exists.

Miguel.

 

[Phil]

Hi Kevin,

In as much as I apprieciate your answer and its complexity, I was only able
to grasp about 10% of what you said. Sadly, I wish I could use what you've
provided me, but I wouldn't know where to begin (or end).

If you could steer me a little further with some more hints or suggestions,
I'd greatly apprieciate it!

Phil.

 

[Kevin Vaughn]

I apologize for the complexity of the solution. Based on the other replies
to you (though I guess they didn't work for you, but I'm not sure why not)
you probably don't need such a complex solution. Unfortunately, I don't have
internet access except at work, and, as busy as I've been lately, I usually
can only pop in during lunch. And as it's past the time I am supposed to
leave, I can't attempt to answer you now. If I get time during lunch
tomorrow, and you still haven't got an answer that will work for you, I'll
try breaking it down a little more.

 

[Phil]

Hi Kevin,

I don't know either why they didn't work. I have a feeling that it might
require some kind of VBA event handler to make it work, but I'd be lying if I
said that wasn't just a guess.

I DO apprieciate your extra effort and am looking forward to hearing what
you have to say, when you get a chance to do so.

Phil.

 

[Kevin Vaughn]

I doubt if this is going to be substantively different than the solutions
proferred, but I changed my formula so that it only went to the .doc column.
I started out this morning with Word not open and when I clicked on my link,
word opened to my document. My file name is in column C (I think, let me
paste my formula

=HYPERLINK(B1&C27&C2,C27)

Yes, UNC path (with trailing \) in B1, formula in A27, FileName in C27,
friendly name is just the File name. Extension in C2 (.doc)

HTH but given your previous problems, I am doubtful. Good luck.