|
Posted to microsoft.public.excel.programming
|
|
|
Full hyperlink issue, please help - end of my tether lol
On 6 Nov, 15:07, Dave Peterson wrote:
You'll have to fix the existing incorrectly changed hyperlinks first.
Then change the hyperlink base.
David McRitchie has some code that you can use to change the hyperlinks:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
look for:
Fix Hyperlinks (#FixHyperlinks)
If you're new to macros, you may want to read David McRitchie's intro at:http://www.mvps.org/dmcritchie/excel/getstarted.htm
ArmsteR wrote:
On 6 Nov, 12:25, Dave Peterson wrote:
A few people have said that this has resolved the problem for them:
File|Properties|Summary Tab|Hyperlink Base
change it to C:\
(something that's always available)
ArmsteR wrote:
Hello and once again thanks in advance for any help you are willing to
provide me.
My problem is this, I have a script that very simply goes down a list
extracts certain hyperlinks and sends a print command using this
hyperlink via shellexecute(). This worked fine when testing on my own
PC before adding it to a copy of the master spreadsheet on the
network. The only thing that seems to have changed is that the
hyperlinks have now become "relative" rather than "absolute" ie.
instead of showing file:///\\server\files\files\file.pdf the extracted
URL is showing ../../../files/file.pdf
I've been doing research on the internet via the power of google and
google groups primerily and the suggestions seem to be 2:
The use of the Base Hyperlink property in the sheet properties. This
does not seem to work as the hyperlinks are not all located in the
same directory structure.
And
using =Hyperlink() to set the hyperlinks in the cells. I believe this
has already been done as when hovering over the hyperlinks the
complete full path is shown its only when you extract it that its
not.
Please can you help me in at the least pointing me in the right
direction.
Many Thanks
David Armstrong
PS the code is below
Code:
Private Sub OKButton_Click()
Dim Papersizes As String
Dim cell As Range
Addr = RefEdit1.Value
myRegKey = "HKEY_CURRENT_USER\Software\Microsoft\Windows NT
\CurrentVersion\Windows\Device"
sMyDefPrinter = RegKeyRead(myRegKey)
For Each hlnk In Range(Addr).Hyperlinks
If hlnk.Range.Offset(0, 1).Text = "A4" Then
' URL = SSFPath + hlnk.Address()
URL = hlnk.Address
Printer = GetPrinterKey(PaperSizeA4)
RegKeySave myRegKey, Printer
Call ShellExecute(0&, "print", URL, vbNullString, vbNullString,
vbNormalFocus)
End If
Next
For Each hlnk In Range(Addr).Hyperlinks
If hlnk.Range.Offset(0, 1).Text = "A3" Then
URL = hlnk.Address()
Printer = GetPrinterKey(PaperSizeA3)
RegKeySave myRegKey, Printer
Call ShellExecute(0&, "print", URL, vbNullString, vbNullString,
vbNormalFocus)
End If
Next
For Each hlnk In Range(Addr).Hyperlinks
If hlnk.Range.Offset(0, 1).Text = "A2" Then
URL = hlnk.Address
Printer = GetPrinterKey(PaperSizeA2)
RegKeySave myRegKey, Printer
Call ShellExecute(0&, "print", URL, vbNullString, vbNullString,
vbNormalFocus)
End If
Next
For Each hlnk In Range(Addr).Hyperlinks
If hlnk.Range.Offset(0, 1).Text = "A1" Then
URL = hlnk.Address
Printer = GetPrinterKey(PaperSizeA1)
RegKeySave myRegKey, Printer
Call ShellExecute(0&, "print", URL, vbNullString, vbNullString,
vbNormalFocus)
End If
Next
For Each hlnk In Range(Addr).Hyperlinks
If hlnk.Range.Offset(0, 1).Text = "A0" Then
URL = hlnk.Address
Printer = GetPrinterKey(PaperSizeA0)
RegKeySave myRegKey, Printer
Call ShellExecute(0&, "print", URL, vbNullString, vbNullString,
vbNormalFocus)
End If
Next
RegKeySave myRegKey, sMyDefPrinter
Unload UserForm1
End Sub
--
Dave Peterson- Hide quoted text -
- Show quoted text -
when i do that it seems to mess up all my hyperlinks though. like
redirecting them to c:\folder\folder\folder\file.pdf that doesn't
excist
--
Dave Peterson- Hide quoted text -
- Show quoted text -
Thanks for the responnse again,
I've been trying this Hyperlink Base but everytime i set it, save it
close it then reopen the sheet it hyperlink base field is clear!
any ideas why this is happening ?
|