A
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
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