Convert Cell Text to Matching Hyperlink?????

[infiniteMPG]

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]

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!")

 

[Gord Dibben]

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

 

[infiniteMPG]

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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[infiniteMPG]

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

 

[infiniteMPG]

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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

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