Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Full hyperlink issue, please help - end of my tether lol
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Junior Member
  
Posts: 13
Default 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



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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
Junior Member
  
Posts: 13
Default 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




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
Junior Member
  
Posts: 13
Default 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 ?

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Hyperlink does not show full address Jennifer Excel Discussion (Misc queries) 1 February 8th 10 06:44 PM
HELP!!! using full hyperlink filepath within VBA/API ArmsteR Excel Programming 2 October 23rd 07 11:04 AM
How to I display the full path of an hyperlink in MS Excel 2000? Hyperlinks Excel Discussion (Misc queries) 0 May 10th 07 03:55 PM
How to convert a hyperlink to its target full-text URL Excel Discussion (Misc queries) 2 July 7th 05 01:40 PM
Printing Word Document from Hyperlink -- Can't get the full path!!! Please Help JadPlane Excel Programming 0 April 17th 05 11:45 AM


All times are GMT +1. The time now is 05:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement