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
546 Views

Similar Articles

[PageSpeed] 3

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:

Inserting a cell in the header.
We are trying to get a cell in the header of a sheet. Can this be done? Example would be in our sheet in D9 is TFF. We want whatever is in D9 to go in the header. I'm not sure this makes sense. Thanks Wendy You could do this with a macro linked to the worksheet_change event. Insert the following code into the macro sheet for the sheet with the cell if interest. Private Sub Worksheet_Change(ByVal Target As Range) For Each Cell In Target If Cell.Address = "$D$9" Then ActiveSheet.PageSetup.CenterHeader = Cell.Value End If Next Cell End Sub -- mrice Research Scientist with ...

Hyperlink behavior Word 2007: correct page but wrong part of page
I've searched a lot of place but have not even seen anyone else having this problem. Document is a multi-page Word 2007 form template with hyperlinks for navigation. In most cases clicking a hyperlink will take you to the correct page, and the top of the page displays. However on some pages, it either displays the bottom of the page and/or the top of the next page. Sometimes the cursor is in the first form field of the following page. With the template open, view field codes, show bookmarks and show text boundaries, I still can't see what might be causing this behavi...

Format cells #7
I have Excel 2000, Win ME. Recently the pulldown command to format cells does not always respond - ie, no box appears, so I can't apply borders or control number format. The shortcut approach doesn't work either. I've tried Help..Detect and Repair but the fault remains. Any suggestions welcome. ...

Convert text to time value
I have a series of time values in a 'General' format. They are of the type: 184525 Which is 18:45:25 or 6:45:25 pm. A time which is am would be of the type: 12345 Which is 1:23:45 am. Is there a way to convert those 'General' values to an Excel serial so that I can figure out the difference between two times? I've seen a bunch of examples on the net, but none of those that I have found deal with this format that I can tell. Thanks. Hi try =--TEXT(A1,"00:00:00") -- Regards Frank Kabel Frankfurt, Germany "Andy" <amelton@gmail.com> schrieb...

how to convert date
Hi, I'm looking for some method to convert mail date, in format: eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. I tried CTime but without resoults. m. Have you tried COleDateTime::ParseDateTime()? m.wski21.usunto@aust.com wrote: > Hi, > > I'm looking for some method to convert mail date, in format: > eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. > I tried CTime but without resoults. > > m. >I'm looking for some method to convert mail date, in format: >eg. "Sun, 18 Sep 2005 20:57:08 +0200&qu...

How to Convert UTC to localTIme(C# )
I have got the value of user account's lastlogon time. Its type is Int64. This value is stored as a large integer that represents the number of 100 nanosecond intervals since January 1, 1601 (UTC)(Refer to MSDN). I don't know how to convert this value to localTime. The following is my code. ################################################################ DirectoryEntry deUser = new DirectoryEntry(ldappath); DirectorySearcher src = new DirectorySearcher(deUser); src.Filter = "(&(objectClass=user)(SAMAccountName=" + accountNa...

How do I convert dates to text keeping the format?
I'm trying to convert a column of data in date format *m/d/yyyy to a text format without converting to serial numbers. Ie: I want to retain the mm/dd/yyyy format. Is there a way to do this? =TEXT(A1,"MM/DD"/YYYY") "sprlarry" <sprlarry@discussions.microsoft.com> wrote in message news:69669AA6-FD15-47D7-843D-FC768728BF7A@microsoft.com... > I'm trying to convert a column of data in date format *m/d/yyyy to a text > format without converting to serial numbers. Ie: I want to retain the > mm/dd/yyyy format. Is there a way to do this? That ...

Auto filter hidden cells
Hello, Yesterday I created a table and by using the custom format ;;; I managed to hide all but the first line of each category and still get all lines of the same instance to show in an auto filter. Today I open the sheet and try to use the auto filter. All I get now is the visible line only. eg Yesterday I made: Bob 1 Bob 2 Bob 3 Look Like Bob 1 2 3 Today I now only get Bob 1 No edits have been made whatsoever. How can I get it back? I really need this back as I want my print format to hide the repetitions but the auto filter to still be fully...

Excel: Auto converting text to numbers
I am downloading an Excel sheet, and the numbers come in as text. It basically comes in as "33 %" but Excel registers this as text, not a percentage. I have a cell that will be used to add the numbers, but since they are text it doesn't work. Given this information, is there a way to convert the imported data into numbers. I would prefer to include this into my formula. The potential numbers are: 0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A I would prefer a function, again if possible, that could convert any number. Please note, the space between the nu...

diagonal text
HOW DO I MAKE MY TEXT GO IN A DIAGONAL WAY ACROSS THE PAGE On Sun, 30 Oct 2005 11:06:07 +0100, SCOTT wrote (in article <8844C60C-FD08-4FB5-B289-D530CAA285DA@microsoft.com>): > HOW DO I MAKE MY TEXT GO IN A DIAGONAL WAY ACROSS THE PAGE Lose the caps. It's considered yelling, is incredibly rude and makes your posts very hard to read. Please explain what it is you're attempting to accomplish and your Publisher version. By the way, questions end with question marks (?). What version of Publisher are you using? In 2000, arrange, rotate or flip, custom rotate, input your n...

1 Cell 2 Values?
Hello All, Is it possible in excel to have it return two values in one cell separated by a comma and spaces? For example I want it to count the number of times in a month the value was positive and the number of times a value was negative and in the cell I want it to show A1: +, - Is this possible? Thanks to all, Shhhh Shhhh wrote... >Is it possible in excel to have it return two values in one cell separated >by a comma and spaces? > >For example I want it to count the number of times in a month the value was >positive and the number of times a value was negative a...

Cell Formats (like StyleSheets)
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I cannot find the way to store some characteristics (font, size, ....) of cell-formatting to own formats (like stylesheets, for reusing them) <br> There are just 6 standards and I cannot add more. <br><br>Any ideas? Sure you can :-) Where are you trying? The easiest way is to format a cell the way you want the style configured then while the cell is selected go to Format> Style. Just type the name you want the style to have then click 'OK'. Alternatively, go to Format> S...

how to convert excel's .cvf file to .csv file
...

Search end/beginning of cell
I know I can do this with functions but is there anyway to do search/replace indicating this? In word I just do ^pWord or Word^p but not sure how/if I can do this in Excel For searching, you can look at the whole cell (match entire cell contents) And look for something like: MSNY* This will find the cells that start with MSNY *MSNY will find the cells that end with MSNY The replace is gonna be a problem for you (I bet). What do you want to start with and what do you want to end with? (save your work before you start. I'm guessing that it won't do what ...

match rows from spreadsheets-Please help.
how do i match rows from different spreadsheets to a directory: Directory ZIP No. 80001 1 80002 2 80003 3 80004 4 80005 5 80006 6 80007 7 Sheet 1: ZIP POP 80001 134 80003 9890 80006 9489 80009 883 =>: Directory ZIP No. POP 80001 1 134 80002 2 80003 3 9890 80004 4 80005 5 80006 6 9489 80007 7 Use VLOOKUP If you tell us where each block of data resides, I will give a more detailed answer. -- Bernard V Liengme www.stfx.ca/people/bliengme r...

Graph and Cell References
I am looking to create a graph, that references cells from two different worksheets for the same data set. I would use part of one of the sheets (or a third possibly hidden sheet) to consolidate the data with formulas such as =Sheet1!A5 and =Sheet2!G2 Now make the charts anywhere you wish from the consolidated data. best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "balandino" <balandino@discussions.microsoft.com> wrote in message news:CF079347-7492-4BD9-9A3D-A483F5B1E962@microsoft.com... >I am looking to create a graph, that r...

Convert Access97 to 2000
Hello, we're currently running access97 and would like to convert it to 2000, but we don't know what is the administrator password for this database. Also this database is running on multi user and have difference permission for diffence users. Could someone help me how to do make this happen but keep the currently permission retaint. Thanks ...

shade one cell that will shade multiple cells
I want to shade one cell that would go into other cell by it's self Not easy to do. You will require custom VBA code to do that. Are you Ok with macros? -- HTH... Jim Thomlinson "Walt" wrote: > I want to shade one cell that would go into other cell by it's self > ...

Convert 2000 Calendar to web page
Greetings, When I convert my calendar for 2005 to a webpage, the page is off by 1 day. Is there a template or fix available to fix this? Thanks, Duane I can edit the html file but this should not be the case. Fixes? Suggestions...other than use Apple? "Duane Perry" <dlp_sr@yahoo.com> wrote in message news:yZRtd.5561$0r.1710@newsread1.news.pas.earthlink.net... > Greetings, > > When I convert my calendar for 2005 to a webpage, the page is off by 1 day. > Is there a template or fix available to fix this? > > Thanks, > > Duane > > Duane, ...

Add help text to a form field?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I'd like to give people instructions about how to fill in form fields in the form itself. I tried &quot;adding help text,&quot; but nothing shows up in the form field. Is what I am trying to do possible, and, if so, what am I doing wrong? What you're using appears in the Status Bar when the field is accessed. Unless people know to look there it does practically no good. IMHO it's more effective to include instructional text as default content for the field or provide explicit labeling in an adjacent table c...

change case of text #2
Some recent data was imported to an Excell worksheet that had a large amount of text in capital letters. Is there a way to change this data to "PROPER" throughout the entire worksheet? Thank you Gloria Anderson at Habitat for Humanity of Key West and the Lower Florida Keys =proper(range) will do the trick Mike "habikeys" <anonymous@discussions.microsoft.com> wrote in message news:017801c39f21$a016ece0$a501280a@phx.gbl... > Some recent data was imported to an Excell worksheet that > had a large amount of text in capital letters. Is there a > way to c...

Hyperlinks in 2000
I am having a problem with hyperlinks in publisher 2000. I have check the help and it says to go to insert and then hyperlink only that option does not exist? Any ideas how I can create a hyperlink using 2000...Any help would be greatly appreciated. J That only applies in a web publication not a print publication. Either create a web publication or convert to web the print publication. And for future reference visit http://www.davidbartosik.com/pub2k.htm -- David Bartosik - MS MVP for Publisher help: www.davidbartosik.com enter to win Pub 2003: www.davidbartosik.com/giveaway.aspx &qu...

XML Note convert to DataSet
Hello, I have this function: object acmResponse = acmLogin.acmString("4001", "", paramFormLogin + paramUserBasics);System.Xml.XmlNode[] acmNodes = (System.Xml.XmlNode[])acmResponse; What I have todo, to convert the XML Object in the DataSet Object? Thank you Matthias ...

Convert
Is it possible to convert a Money file created in the USA version to that of the UK version? Thanks in advance The general way is QIF Export then Import. It's involved and has limitations like loan accounts don't QIF. See http://www.bollar.org/msmoney/#Q1. "Crispy" <nowayspammers@hotmail.com> wrote in message news:uQKSfzfyDHA.2500@TK2MSFTNGP09.phx.gbl... > Is it possible to convert a Money file created in the USA version to that of > the UK version? ...

Format a concantated text string
Sorry if this is boring but I'm stuck. I have a cell that counts something, it also has a piece of text at the front of the count but I want the count to be formatted as currency and it won't. ="Major "&COUNTIF(C:C,"Major")*245 I've tried ="Major "& Format(COUNTIF(C:C,"Major")*245****) but I can't get it right and think it's a VBA command! Help please! Thanks Dean -- dkso@ntlworld.com http://www.dkso.co.uk/ http://homepage.ntlworld.com/dkso Thank you! Dean "Bob Phillips" <bob.phillips@notheretisca...