Trouble with hyperlinks (repost)

  • Thread starter Thread starter a7yvm109gf5d1
  • Start date Start date
A

a7yvm109gf5d1

I didn't get any answers, not even flames the last time I posted this,
so here goes again.

I use hyperlinks that point to a bunch of PDFs on a server somewhere.
So I set up the 'hyperlink base' property in file->properties such that
the base is p:\, which is the drive letter we always map the server to.

Now the first problem I have is that when I add a hyperlink by hand via
ctrl-K, the file requester never opens at the p drive, I have to
navigate all over the place to get there. Is there an option somewhere
so that this requester at least remembers the last path I opened? It
would be nice if the hyperlink base was understood as being the
preferred place to look first!

The second problem is that it looks like Excel enjoys 'expanding' the
path names fully, so rather than having a hyperlink like this
"datasheets\manufacturer\blabla.pdf" with the p:\ coming from the base,
I have
"file:///\\pcbserver\pcbdata\datasheets\manufacturer\blabla.pdf".
Wasn't the whole point of entering a base to not end up with this
lengthy and unportable name?

The third problem is now I have to edit all the hyperlink names by hand
to remove the extraneous info.

The fourth problem is now all the hyperlinks that point to sheet names
now get a p:\ prepended to them, these links don't work anymore.

What am I missing?
 
Just a guess...

Try changing the hyperlink base
(file|properties|Summary tab|Hyperlink base)
to a path that always exists (C:\ ???).

Maybe the links won't be changed.

But my preference is to use the =hyperlink() worksheet function.

=hyperlink("\\pcbserver\pcbdata\datasheets\manufacturer\blabla.pdf","click")
or
put \\pcbserver\pcbdata\datasheets\manufacturer\
in A1
and
=hyperlink($a$1&a2,a2)
with the filename in A2.

Then you can just change one cell if/when the location changes.
 
I am using Excel 2003. I wrote the following macro to fix a bunch of
hyperlinks in a spreadsheet. Maybe it has some info you can use>
Sub ReplaceHyperlinks()

' This macro totally replaces the hyperlinks in the Design/1 spreadsheets.
' The cells containing the document name must contain an existing
hyperlink
' The resulting hyperlinks have the format :
' SheetTab/DocumentName.Extension
'

Const HyperlinkExtension As String = "txt"

Dim ActiveSheetName As String
Dim LastCell As Range
Dim RowNbr As Long
Dim D1Name As Range
Dim HoldHyperlink As Hyperlink
Dim HyperlinkAddress As String
Dim CharPos As Integer
Dim HoldCell As Range
Dim DelimPos As Variant
Dim HoldErrMsg As String
Dim Response As Integer
Dim ValidSw As Integer
Dim CrossRefOK As Integer

ActiveSheetName = UCase(Trim(ActiveSheet.Name))
Set LastCell = ActiveCell.SpecialCells(xlCellTypeLastCell)
'MsgBox LastCell.Column & ", " & LastCell.Row


For RowNbr = 2 To LastCell.Row
' MsgBox "row nbr " & RowNbr
Set D1Name = Cells(RowNbr, 1)
D1Name = UCase(Trim(D1Name)) ' drop leading & trailing spaces,
convert to uppercase
'If D1Name.Value = "" Then
If D1Name = "" Then
'MsgBox "name blank " & RowNbr
Else
'MsgBox "name not blank " & RowNbr
GoSub ReplaceTheHyperlink
End If
Next

End
'------------------------------------------
ReplaceTheHyperlink:

HyperlinkAddress = ActiveSheetName & "/" & D1Name & "." &
HyperlinkExtension
'MsgBox "HyperLink=" & HyperlinkAddress
D1Name.Hyperlinks(1).Address = HyperlinkAddress

Return
'------------------------------------------

End Sub

====================================================
 
Back
Top