About Us

ArmsteR

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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

Menu

About Us