Full hyperlink issue, please help - end of my tether lol
 

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

Full hyperlink issue, please help - end of my tether lol
 

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

Full hyperlink issue, please help - end of my tether lol
 

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

Full hyperlink issue, please help - end of my tether lol
 

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

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 ?

Full hyperlink issue, please help - end of my tether lol
 

Just a guess...

Are you sure you're saving to the same location that you're opening?

ArmsteR wrote:

<<snipped
- 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 ?

--

Dave Peterson