Convert Cell Text to Matching Hyperlink?????

We have an Excel spreadsheet that contains information to scanned
images being populated into our engineering database.  In the
spreadsheet there is information as to the creation time and date,
size, title and other related information.  One of the columns is the
name and location of the actual TIFF image file.  This field is just
text.  What we would like to do is convert the cell from just text
stating the location and filename to a hyperlink pointing to the
specific file.

For example, the cell contents state :  G:
\folder2007\subfolder0320\00001234.tif

We would like to still have the text in the cell but add to the cell a
hyperlink that actually points to the file at G:
\folder2007\subfolder0320\00001234.tif so when we click on the cell it
will launch our associated image viewing application and view the
image.  We tried creating a macro to do this one call at a time and
copied the text in the cell to the clipboard and then pasting back in
as a hyperlinlk.  Somehow the cell address gets tagging into this and
when you run the macro it always jumps back to the original cell the
macro was written at.

Any way to automate this process or any shortcuts to converting these
cells to matching hyperlinks????

Any and all help is greatly appreciated,
Scott

0
57classic (7)
3/21/2007 3:42:03 PM
excel 39879 articles. 2 followers. Follow

5 Replies
649 Views

Similar Articles

[PageSpeed] 42

I'd use another column with a formula like:

=hyperlink("file:////" & x2,"ClickMe!")

where x2 is the address of the cell containing:
G:\folder2007\subfolder0320\00001234.tif

If that G: is in a different cell:
=hyperlink("file:////" & w2 & x2,"ClickMe!")



infiniteMPG wrote:
> 
> We have an Excel spreadsheet that contains information to scanned
> images being populated into our engineering database.  In the
> spreadsheet there is information as to the creation time and date,
> size, title and other related information.  One of the columns is the
> name and location of the actual TIFF image file.  This field is just
> text.  What we would like to do is convert the cell from just text
> stating the location and filename to a hyperlink pointing to the
> specific file.
> 
> For example, the cell contents state :  G:
> \folder2007\subfolder0320\00001234.tif
> 
> We would like to still have the text in the cell but add to the cell a
> hyperlink that actually points to the file at G:
> \folder2007\subfolder0320\00001234.tif so when we click on the cell it
> will launch our associated image viewing application and view the
> image.  We tried creating a macro to do this one call at a time and
> copied the text in the cell to the clipboard and then pasting back in
> as a hyperlinlk.  Somehow the cell address gets tagging into this and
> when you run the macro it always jumps back to the original cell the
> macro was written at.
> 
> Any way to automate this process or any shortcuts to converting these
> cells to matching hyperlinks????
> 
> Any and all help is greatly appreciated,
> Scott

-- 

Dave Peterson
0
petersod (12004)
3/21/2007 3:48:21 PM
Sub MakeHyperlinks()
'David McRitchie
Dim cell As Range
    For Each cell In Intersect(Selection, _
            Selection.SpecialCells(xlConstants, xlTextValues))
        With Worksheets(1)
            .Hyperlinks.Add anchor:=cell, _
                    Address:=cell.Value, _
                    ScreenTip:=cell.Value, _
                    TextToDisplay:=cell.Value
        End With
    Next cell
End Sub


Gord Dibben  MS Excel MVP

On 21 Mar 2007 08:42:03 -0700, "infiniteMPG" <57classic@gmail.com> wrote:

>We have an Excel spreadsheet that contains information to scanned
>images being populated into our engineering database.  In the
>spreadsheet there is information as to the creation time and date,
>size, title and other related information.  One of the columns is the
>name and location of the actual TIFF image file.  This field is just
>text.  What we would like to do is convert the cell from just text
>stating the location and filename to a hyperlink pointing to the
>specific file.
>
>For example, the cell contents state :  G:
>\folder2007\subfolder0320\00001234.tif
>
>We would like to still have the text in the cell but add to the cell a
>hyperlink that actually points to the file at G:
>\folder2007\subfolder0320\00001234.tif so when we click on the cell it
>will launch our associated image viewing application and view the
>image.  We tried creating a macro to do this one call at a time and
>copied the text in the cell to the clipboard and then pasting back in
>as a hyperlinlk.  Somehow the cell address gets tagging into this and
>when you run the macro it always jumps back to the original cell the
>macro was written at.
>
>Any way to automate this process or any shortcuts to converting these
>cells to matching hyperlinks????
>
>Any and all help is greatly appreciated,
>Scott

0
Gord
3/21/2007 4:08:27 PM
WORKS PERFECT!!!!!!!!!!!   We have to check about 600 files a day and
it used to take about 6 individual clicks to open the file to view and
check and with this is take ONE, which equates to a savings of about
3,000 mouse clicks a day!!!!  THANKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--------------------------------------------------------------------------------------------------------------------------------------------------------

On Mar 21, 12:08 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> Sub MakeHyperlinks()

0
57classic (7)
3/21/2007 5:44:43 PM
Now that we have that working we have a hyperlink to the file (for
example)

G:\folder2007\subfolder0320\00001234.tif

When we click on this hyperlink we get the warning that "Some files
can conatin viruses or otherwise be harmful..... blah-blah-blah".
And we have to click OK every single time we click a file.  Is there
any way to disable this warning or at least identify that these files
are "safe"??????

Thanks again,
Scott



On Mar 21, 1:44 pm, "infiniteMPG" <57clas...@gmail.com> wrote:
> WORKS PERFECT!!!!!!!!!!!   We have to check about 600 files a day and
> it used to take about 6 individual clicks to open the file to view and
> check and with this is take ONE, which equates to a savings of about
> 3,000 mouse clicks a day!!!!  THANKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
> -------------------------------------------------------------------------=
--=AD----------------------------------------------------------------------=
-----=AD--
>
> On Mar 21, 12:08 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
>
>
>
> > Sub MakeHyperlinks()- Hide quoted text -
>
> - Show quoted text -


0
57classic (7)
3/22/2007 3:23:23 PM
Found some MS help on disabling the warning message when trying to
hyperlink directly to a TIF image file and we did the following :

regedit to HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common and
created a new DWORD named DisableHyperlinkWarning and set it to 1 -->
no change

regedit and created HKEY_CURRENT_USER\Software\Policies\Microsoft
\Office\11.0\Common and created a new DWORD named
DisableHyperlinkWarning and set it to 1 -->  no change

regedit to HKEY_CLASSES_ROOT\WMVFile\EditFlags and set EditFlags to
10000 (hex) --> no change

Went to My Computer/Tools/Folder Options/File Types/TIF and TIFF and
was told to click the ADVANCED button but there is no ADVANCED button
for TIF or TIFF.

Can't get ride of this dang warning
message!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

---------------------------------------------------------------------------=
---------------------

On Mar 22, 11:23 am, "infiniteMPG" <57clas...@gmail.com> wrote:
> Now that we have that working we have a hyperlink to the file (for
> example)
>
> G:\folder2007\subfolder0320\00001234.tif
>
> When we click on this hyperlink we get the warning that "Some files
> can conatin viruses or otherwise be harmful..... blah-blah-blah".
> And we have to click OK every single time we click a file.  Is there
> any way to disable this warning or at least identify that these files
> are "safe"??????
>
> Thanks again,
> Scott
>
> On Mar 21, 1:44 pm, "infiniteMPG" <57clas...@gmail.com> wrote:
>
>
>
> > WORKS PERFECT!!!!!!!!!!!   We have to check about 600 files a day and
> > it used to take about 6 individual clicks to open the file to view and
> > check and with this is take ONE, which equates to a savings of about
> > 3,000 mouse clicks a day!!!!  THANKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
> > -----------------------------------------------------------------------=
----=AD=AD-----------------------------------------------------------------=
---------=AD-=AD--
>
> > On Mar 21, 12:08 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
>
> > > Sub MakeHyperlinks()- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -


0
57classic (7)
3/22/2007 5:58:16 PM
Reply:

Similar Artilces:

add two cells from seperate work sheets into a cell on seperate wo
I have two cells on one work sheet that I want to add together in a cell on another work sheet. And is it possible to add together two cells on seperate work sheets in a cell on third work sheet Hi, When you say add do you mean sum and different worksheets are different files? if the answer is yes and let's assume that the worksheet names are A, B and C and the information is in that sheets in the tab called Sheeet1 and the information is to be summarize in worksheet C =+[A]Sheet1!$A$1+[B]Sheet1!$A$2 "lar" wrote: > I have two cells on one work sheet that...

convert appointment to opportunity : link ?
Hi everybody, There is a feature that allows us to "convert" an appointment to an opportunity. But there isn't any field that remember the link between the appointment and the opportunity... Do you know a way to get it? Isn't the activity listed in the Activities/History area of the Opportunity? When you convert an appointment to an opportunity that appointment should be listed as the only activity listed in the Activities (if you didn't close the appointment when converting) or History (if you did close the appointment when converting). Kind regards, Alex &...

Filling cells in between pairs of numbers
Hello all, I am trying to find an easy way to fill in cells in between pairs of numbers each of which is exactly the average of the pair's difference averaged by the number of these cells greater than the previous. So I have 1 1 2 3 4 8 5 6 7 8 20 9 10 12 13 14 15 45 I want: 1 1 2 3.333333333 3 5.666666667 4 8 5 11 6 14 7 17 8 20 9 23.57142857 10 27.14285714 11 30.71428571 12 34.28571429 13 37.85714286 14 41.42857143 15 45 I know I can do a Fill, Series, but then even with a macro that does a "control shift up fill series enter",...

How do you add text to custom columns created thru the Field Chooser pls?
Hello, If I create a custom text column via Field Chooser - New, how can I enter text into it afterwards? I'm looking to add an "annotation" column and not have it sent if/when I forward or reply to the message. Outlook 2003 with Exchange 2003. Thanks, - Alan. If you directly want to fill it out in the message list you'll have to enable "allow in-cell editing" for your view. The technique would be similar to; http://www.msoutlook.info/question/150 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://ww...

fixed length within the counta and some text
hello! I would like to generate some incremental number if the cell on the right is not empty e.g. Order0001 Order0002 ..... Order0015 How I can do this with this formula ? How i can convert the output of counta to have 4 digits? =IF(D13<>"","Order"&COUNTA($D$4:D13)&"","") thanks, uma =IF(D13<>"","Order"&TEXT(COUNTA($D$4:D13),"0000"),"") -- Regards, Peo Sjoblom "Uma Shanker" <asdf834@yahoo.com> wrote in message news:d0n7e7$qdk$01$1@news.t-...

Converter #2
How can I open a Publisher 2000 version 6.0 document in XP? As long as you are talking about Publisher 2002, you don't need to do anything except open it. -- JoAnn Paules MVP Microsoft [Publisher] "jkingym" <jkingym@discussions.microsoft.com> wrote in message news:367D7249-0707-4AFC-9AEA-46864A508715@microsoft.com... > How can I open a Publisher 2000 version 6.0 document in XP? > "JoAnn Paules [MSFT MVP]" wrote: > As long as you are talking about Publisher 2002, you don't need to do > anything except open it. > > -- > ...

Formatting changes when copying or inserting text from other word
Hi All, I have 2 documents... I need to combine the top portion of the first and bottom portion of the second... they both have their own formattings. When I combine them (Copy/paste or insert pages) the formating of the one I copy or insert changes. I want the formatting of each of the decuments remain as they were. As if I am combining 2 pdf documents. How can I do this? I am using MS Word 2000. Thank you Be sure that the two documents don't have any Style names in common (unless styles with the same name are absolutely identical in both documents). And put a S...

Various Outlook 2003 issues
Hello, I've been using OL2003 w/ WinXP since it was released. I used various other versions of OL for many years before that. In all that time, I have never experienced the issues that have come up lately. I'm hoping someone can help me resolve them. 1. In the midst of composing a new message or replying to a message, the message format will change from HTML to plain text all by itself. This started happening with replies to one person specifically but has happened with increasing frequency and probably occurs with more than 50% of all ougtoing messages now. When I switch the message...

How do I delete the "=" from a cell to give me the phone number I.
I have some data that I copied off a website and pasted into an excel spreadsheet but I do not get the value that is displayed on the website. For example.... The information that was copied was a phone and fax number 1-555-555-5555 but when I put this information into my excel sheet I get -4500. If I look into the cell the cell looks like =1-555-555-5555. I need a way to remove the "=" from each one of these cells. Thank You, Hi, what I do in cases like this is to paste the info into Word first, do a replace - stripping out the = sign and then copy and paste into exel. ...

Publisher: Why won't my text wrap around a photo I have copied?
In Publisher 2000, it was dead straight forward... you pasted your image inside a text box and the text went around it. In 2003, the text either dissapears behind the image or gets transposed over the image. Any ideas? >-----Original Message----- >In Publisher 2000, it was dead straight forward... you pasted your image >inside a text box and the text went around it. In 2003, the text either >dissapears behind the image or gets transposed over the image. > >Any ideas? > > >. > Layers!! Select either the image or text box, then choose Order from the Arr...

Book/doc on converting XP program to work nicely on Vista?
Any good books or documentation, things I should know about what worked fine on XP that won't work on Vista, what needs to be modified in the program to get it to work nicely on Vista, umm, stuff like that? What's a better way than Journal Hooks for a program to record it's own keystrokes? Journal hooks was always problematic if other programs were running that didn't process their messages correctly, and now on Vista I hear journal hooks have been disabled. try Microsoft Application Verifier. "David Deley" <deleyd@gte.net> wrote in message news:vTqVi.1...

Using HYPERLINK Function in array formula
I've spent a good part of the day working on a few pretty complex array formulas. I'd now like to add one that will create an array of hyperlinks. I thought I could do something equivalent to: =HYPERLINK({"http://google.com","http://yahoo.com","http:// microsoft.com"}, {"google","yahoo","microsoft"}) ....but I just end up with an array filled with google links... Anyone have any ideas? Thanks, James hi, James ! > I've spent a good part of the day working on a few pretty complex array formulas. > I'd now l...

VLOOKUP for a cell with both letters and numbers
Hello I am trying to use VLOOKUP to find an exact match, the problem is that the cell contains data that is a series of letters and numbers. I have used vlookup to find and then return data successfully if the search cells are all letters OR all numbers, but can't get it to work when both are present. I have also tried formatting the cells to read as 'text', or 'general', neither of which made a difference. PLEASE HELP! Thanks Please give examples of the data you are using, the value you are using as a key and the LOOKUP formula itself Regards Trevor "So...

rewoving text boxes from a resume
my resume was scanned and emailed to me. When I opened the file word 2003 there are text boxes all over the document. I cannot cut and paste my document. How do I reformat my document so I can edit and makes changes. thanks sue -- suetauber ------------------------------------------------------------------------ suetauber's Profile: http://forums.techarena.in/members/163050.htm View this thread: http://forums.techarena.in/ms-office-support/1281312.htm http://forums.techarena.in http://word.tips.net/Pages/T001690_Removing_All_Text_Boxes_In_a_Document.html ...

split text in cell into 2
I am needing help with formula. I have a cell that takes values from several other cells to create one long line text (for a command used in other app). For ex: a1=jack b1=ran c1="There was a person named"&a1&"who was hungry and"&b1&" to Mcdonalds." This process works great except when the line is greater than 72. I need an if statment that says if c1 length is greater than 72 then to split / replace line AFTER name(a1). Where the split occurs a +, and carriage return needs to be added. So in the example above (pretend length will be over 72,...

Hyperlinks #3
I have buttons on my toolbar that hyperlink to oft templates. I've changed the name, and that works if I show text on the toolbar, but the tooltip always shows the full path - is there any way to edit the tooltip? OL2003 ...

Excel formula for seperating two words in the same cell.
How do I seperate two words in the same cell, seperated by a comma into to seperate cells. eg. John,Smith (in cell A1) converted to John (in cell B1) and Smith (in cell C1)? Select column A and do Data|Text to columns Delimited by comma and make sure you put the output in column B Mim wrote: > > How do I seperate two words in the same cell, seperated by a comma into to > seperate cells. eg. John,Smith (in cell A1) converted to John (in cell B1) > and Smith (in cell C1)? -- Dave Peterson In cell B1 put =MID(A1,1,FIND(",",A1,1)-1) In cell C1 put =MID(A1,FIND(...

Ranking Only Visible Cells
I have a spreasheet with store numbers in column A and the store's sales in column B. Example: 1 $500.00 2 $497.38 3 $557.15 etc. There are 70 stores but only 64 of them are OFFICIALLY generating sales. I have hidden the stores that are generating sales UNOFFICIALLY and want to rank the visible stores only. Is there a way to rank only the visible cells? The hidden stores are scattered throughout and will become "official" at different times, so "hard-coding" the formula would be a pain (there are many of these spreadsheets with different informati...

how to change text of a part of a sentence automatically
Hi, I would like to know, how can I select part of a sentence Range object and change it's text automatically. Hi, The following macro demonstrates the simplest way to change part of the sentence in which the cursor is located. Note that it doesn't do anything if the search text is not found. Sub ReplaceTextInSentence() Dim OldText As String Dim NewText As String OldText = InputBox("Type the text that you want to replace.") NewText = InputBox("Type the new text.") With Selection.Sentences(1).Find .Text = Ol...

Convert from GUID to String and vice versa
Hi Happy New Year, how to convert from GUID to CString and vice versa ..A piece of code will be appreciated.. Thanks with regards, Sanjeev. Sanjeev UuidToString and its counterpart UuidFromString are but a couple of options available. Here is a link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rpc/rpc/uuidtostring.asp Make sure you understand the memory allocation for the returned string - pay special attention to the reference to RpcStringFree note. regards roy fine "San" <feelsanjeev@hotmail.com> wrote in message news:eLgraA57EHA.1452@TK2...

VALIDATION DATA IN A CELL
Hi, can some one tell me if there's any way I put put some validation or enforcement in a cell, where users HAVE to input something and it CANT be left blank ? Thanks What I usually do in a case like that is to use Conditional Formatting to color the cells RED if they are blank, (ie = <> )and they turn white when data is entered in them, then on the next function that depends on that data, say a SUM function, use an IF to condition it, such as......... =IF(COUNT(A1:A10)=10,SUM(A1:A10),"Red Cells must contain values") Vaya con Dios, Chuck, CABGx3 "Chris" &l...

Unable to use right-click on cells; standard and formatting toolbars disabled
Hello - I'm not sure what happened but a while back I started having problems with excel. First, the Standard and Formatting toolbars are no longer displaying in the toolbar area. When I use Customize toolbar they appear on the Customize-Toolbar tab, but the check-boxes to the left are disabled (i.e I can't check/uncheck them) . Second, and really important for me because I use a lot of this feature, in the working area, right-clicking no longer works. When I right-click a cell, row, or column, no right menu appears with the traditional copy, cut, paste etc or whatever. Noth...

Excel 2007 text sorting problem
I apologize in advance for decidedly noob lingo... In short, Excel is doing a very odd sort of last name/first name columns in a worksheet containing data from two separate lists. Here's an example: Sandra H Bailey Ward Bailey B B BAILEY BARBARA BAILEY BARBARA BAILEY DEBBIE BAILEY ELEANOR BAILEY ELOISE BAILEY JAMES BAILEY JANET BAILEY JANET BAILEY JANET BAILEY JESSIE BAILEY JIMMIE BAILEY JOHN S BAILEY JON...

Add formula to cell to sum range of cells
I am trying to write a macro that selects a range of cells and then format a nearby cell to show the sum of that range. The code i have does so by selecting the range, naming it, and using the name in the formula. The problem is that I want to run this for multiple sheets in the workbook. I can't figure out how to use a variable to name the range and use it in the sum formula. Using ActiveSheet.name + "Days" in the sum formula creates an error. Range("B11").Select Range(Selection, Selection.End(xlDown).Offset(-1#)).Select ActiveWorkbook.N...

converting lotus macro
I am an old lotus guru that is lost in VBA. Can someone convert the following simple lotus macro to VBA? It compares two values and moves one set or the other if not matched to align two series of data. "\b" compares debits, "\c" then compares the credits. 5 minutes work in lotus, years of frustration in excel! Hope there are still some old 123pro's out there who'll understand this. \a {goto}e17~ \b /xi@cellpointer("contents"}=""~{r}/xg/c~ /c~hold~ {right 4}~ /xi@cellpointer("contents")=hold~{down}{left 4}~/xg\b...