Hidden Hyperlink

[Srinivas Chundi]

Using VB6, I have created an excel spreadsheet. Some cells contain a
hyperlink to TIF image files on a specific server within the LAN. When the
user clicks on any one of these cells, it launches a copy of Imaging
software and displays the image the hyperlink is pointing to.

Is it possible to let the users view the images, like they are currently
doing, without giving them direct read access to the specific location where
the image files are located? I need to do this to ensure that the users do
not view/access the images by any means other than clicking on the
hyperlink. Also, the location of the images should be hidden from them and
they should not be able to edit or copy/paste the hyperlink to a different
software, such as word. In essance, the users should not know where the
images are actually located and they should not be able to get to them by
any other means, say using windows explorer or word.

It is really important that I accomplish something as close to this as
possible.
Any help is greatly appreciated.
Thanks
-- M

 

[Tomm]

I presume you are not working through a web-server? Then I guess protected
client-code (custom excel-function in COM add-in or Add-in) would have to
copy the file to a local temp directory and open from that location.

 

[Srinivas Chundi]

Thank for the response. Nope. No web server.

Being a complete stranger to the field/technique you allude to, I request
you to point me to some links/examples. Thanks.

 

[Tomm]

In Excel I would make sure the user double-clicks a cell.

I would trap the event :

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)

End Sub

Based on info in cell double-clicked I would in code copy the TIFF into a
local path.


Function CopyFile(SourceName As String, DestName As String) As Integer
'
' Copies a single file SourceName to DestName
'
' Calling convention:
' X = CopyFile("C:\This.Exe", "C:\That.Exe")
' X = CopyFile("C:\This.Exe", "C:\Temp\This.Exe")
'
Const BufSize = 8192
Dim Buffer As String * BufSize, TempBuf As String
Dim SourceF As Integer, DestF As Integer, i As Long
On Error GoTo CFError
SourceF = FreeFile
Open SourceName For Binary As #SourceF
DestF = FreeFile
Open DestName For Binary As #DestF
For i = 1 To LOF(SourceF) \ BufSize
Get #SourceF, , Buffer
Put #DestF, , Buffer
Next i
i = LOF(SourceF) Mod BufSize
If i > 0 Then
Get #SourceF, , Buffer
TempBuf = Left$(Buffer, i)
Put #DestF, , TempBuf
End If
Close #SourceF
Close #DestF
CopyFile = True

CFExit:
Exit Function

CFError:
Close
MsgBox "Error " & Err.Number & " copying files" & Chr$(13) & Chr$(10) &
Error
CopyFile = False
Resume CFExit
End Function


Then add the hyperlink to the local file in the cell next to double-clicked
cell

With Worksheets(1)
.Hyperlinks.Add .ActiveCell.Offset(0, 1), "address of local file"
End With

Then Follow the hyperlink in code.

Worksheets(1).Hyperlinks(1).Follow



Hope this helps

 

[Srinivas Chundi]

Thanks a lot indeed!! I appreciate your kindness.
Let me try this.
-- M

 

[Tomm]

You could probably need a function to create a temp filename to copy your
files from your "hidden" folder to local machine.

Using a technique suggested here the hyperlink you create will work in all
applications but against a temp file on the user's machine.


Declare Function l_api_GetTempFileName_32 Lib "kernel32" Alias
"GetTempFileNameA" (ByVal lpszPath As String, ByVal lpPrefixString As
String, ByVal wUnique As Long, ByVal lpTempFileName As String) As Long


'\==========================================================================
'\ Function sGetTempFileName
'\
'\ Purpose: Gets a real temp file name and converts it according to
options
'\ Arguments: Optional sPrefix As String - File Prefix (max 3 chars)
'\ Optional sExtension As String - File extension (max 3
chars)
'\
'\ Example: sGetTempFileName("RES", "MPX") could return
'\ "C:\Windows\Temp\RES2160.MPX".
'\
'\ Returns: Full path and filename to tempfile
'\ Called By:
'\ Notes: Prefix and Extension is useful to find the file in temp dir.
'\ Keywords: Temporary file
'\ Originated: 05/30/96 th
'\ Rev Hist:
'\==========================================================================
Function sGetTempFilename(Optional sPrefix As Variant, Optional sExtension
As Variant) As String
Dim bPrefix As Boolean
Dim bExtension As Boolean
Dim sBuffer As String
Dim lRetval As Long
Dim nRetVal As Integer
Dim sFilename As String
Dim sCustomFileName As String

On Error GoTo sGetTempFileName_Error

'\ Optional Prefix, max 3 characters
If IsMissing(sPrefix) Then
bPrefix = False
sPrefix = ""
Else
bPrefix = True
sPrefix = Left(sPrefix, 3)
End If

'\ Optional extension
If IsMissing(sExtension) Then
bExtension = False
sExtension = "TMP"
Else
bExtension = True
sExtension = Left(sExtension, 3)
End If

sBuffer = Space(255)
lRetval = l_api_GetTempFileName_32(sGetTempPath, sPrefix, 0, sBuffer)
sFilename = sFixNull(sBuffer)

If (bPrefix Or bExtension) Then
sCustomFileName = Left$(sFilename, Len(sFilename) - 3) & sExtension
Name sFilename As sCustomFileName
sGetTempFilename = sCustomFileName
Else
sGetTempFilename = sFilename
End If
Exit Function
sGetTempFileName_Error:
'\The file exists, so recurse
sGetTempFilename = sGetTempFilename()
Exit Function
End Function