Problems generating an html link based on worksheet values

Problems generating an html link based on worksheet values

Post by Frederik Roman » Wed, 02 Jul 2003 22:24:06



I want to generate a hyper link based on text in a cell, for example
the cells D16 and D17 generate the text strings in F16 and F17, which
are valid filenames.  I have two macros
        MakeLink_file
        MakeLink_datNum
that should both generate hyperlinks for me (source for these below),
however MakeLink_file does not work, whereas MakeLink_datNum does (I
want to use the more generic MakeLink_file version).  Any ideas why
the simpler macro does not work?

Fred

D16 = 4383
D17 = 1267

F16 = \\FDR-server\General Pool\Beatrix\DATA SHEETS\FDR_PDF\4383 DAT
01.pdf
F17 = \\FDR-server\General Pool\Beatrix\DATA SHEETS\FDR_PDF\1267 DAT
01.pdf

This hyperlink does not work
E16 = MakeLink_file(F16)
= \\FDR-server\General Pool\Beatrix\DATA SHEETS\FDR_PDF\4383 DAT
01.pdf

This hyperlink does work
E17 =MakeLink_datNum(D17)
= \\FDR-server\General Pool\Beatrix\DATA SHEETS\FDR_PDF\1267 DAT
01.pdf

Public Function MakeLink_datNum( datNum)
'
' Macro recorded 2003-07-01 by Fred

        MakeLink_datNum = "\\FDR-server\General Pool\Beatrix\DATA
SHEETS\FDR_PDF\" _
    &   datNum _
    &   " DAT 01.pdf"

    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=
MakeLink_datNum

    Selection.Font.ColorIndex = 0

End Function

Public Function MakeLink_file( file)
'
' Macro recorded 2003-07-01 by Fred

    MakeLink_file = file
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= file
    Selection.Font.ColorIndex = 0

End Function

 
 
 

Problems generating an html link based on worksheet values

Post by Dave Peterso » Wed, 02 Jul 2003 23:06:45


Excel has a builtin function called =hyperlink() that might do what you want.  


> I want to generate a hyper link based on text in a cell, for example
> the cells D16 and D17 generate the text strings in F16 and F17, which
> are valid filenames.  I have two macros
>         MakeLink_file
>         MakeLink_datNum
> that should both generate hyperlinks for me (source for these below),
> however MakeLink_file does not work, whereas MakeLink_datNum does (I
> want to use the more generic MakeLink_file version).  Any ideas why
> the simpler macro does not work?

> Fred

> D16 = 4383
> D17 = 1267

> F16 = \\FDR-server\General Pool\Beatrix\DATA SHEETS\FDR_PDF\4383 DAT
> 01.pdf
> F17 = \\FDR-server\General Pool\Beatrix\DATA SHEETS\FDR_PDF\1267 DAT
> 01.pdf

> This hyperlink does not work
> E16 = MakeLink_file(F16)
> = \\FDR-server\General Pool\Beatrix\DATA SHEETS\FDR_PDF\4383 DAT
> 01.pdf

> This hyperlink does work
> E17 =MakeLink_datNum(D17)
> = \\FDR-server\General Pool\Beatrix\DATA SHEETS\FDR_PDF\1267 DAT
> 01.pdf

> Public Function MakeLink_datNum( datNum)
> '
> ' Macro recorded 2003-07-01 by Fred

>         MakeLink_datNum = "\\FDR-server\General Pool\Beatrix\DATA
> SHEETS\FDR_PDF\" _
>     &   datNum _
>     &   " DAT 01.pdf"

>     ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=
> MakeLink_datNum

>     Selection.Font.ColorIndex = 0

> End Function

> Public Function MakeLink_file( file)
> '
> ' Macro recorded 2003-07-01 by Fred

>     MakeLink_file = file
>     ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= file
>     Selection.Font.ColorIndex = 0

> End Function

--

Dave Peterson


 
 
 

Problems generating an html link based on worksheet values

Post by David McRitchi » Thu, 03 Jul 2003 02:55:07


If you have a (preexisting, for instance) object hyperlink it will override the
HYPERLINK Worksheet function,  remove the
hyperlink if any by right-click on cell and remove hyperlink,
if not an option then that is not the problem.
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

> Excel has a builtin function called =hyperlink() that might do what you want.


> > I want to generate a hyper link based on text in a cell, for example
> > the cells D16 and D17 generate the text strings in F16 and F17, which
> > are valid filenames.  I have two macros
> >         MakeLink_file
> >         MakeLink_datNum
> > that should both generate hyperlinks for me (source for these below),
> > however MakeLink_file does not work, whereas MakeLink_datNum does (I
> > want to use the more generic MakeLink_file version).  Any ideas why
> > the simpler macro does not work?

> > Fred

> > D16 = 4383
> > D17 = 1267

> > F16 = \\FDR-server\General Pool\Beatrix\DATA SHEETS\FDR_PDF\4383 DAT
> > 01.pdf
> > F17 = \\FDR-server\General Pool\Beatrix\DATA SHEETS\FDR_PDF\1267 DAT
> > 01.pdf

> > This hyperlink does not work
> > E16 = MakeLink_file(F16)
> > = \\FDR-server\General Pool\Beatrix\DATA SHEETS\FDR_PDF\4383 DAT
> > 01.pdf

> > This hyperlink does work
> > E17 =MakeLink_datNum(D17)
> > = \\FDR-server\General Pool\Beatrix\DATA SHEETS\FDR_PDF\1267 DAT
> > 01.pdf

> > Public Function MakeLink_datNum( datNum)
> > '
> > ' Macro recorded 2003-07-01 by Fred

> >         MakeLink_datNum = "\\FDR-server\General Pool\Beatrix\DATA
> > SHEETS\FDR_PDF\" _
> >     &   datNum _
> >     &   " DAT 01.pdf"

> >     ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=
> > MakeLink_datNum

> >     Selection.Font.ColorIndex = 0

> > End Function

> > Public Function MakeLink_file( file)
> > '
> > ' Macro recorded 2003-07-01 by Fred

> >     MakeLink_file = file
> >     ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= file
> >     Selection.Font.ColorIndex = 0

> > End Function

> --

> Dave Peterson