Open/Close files from Hyperlinks

R

Revolvr

Hi all,

I need a bit of help understanding hyperlinks. I want to create a VBA
function that opens an Excel file from a hyperlink, and another function
that closes the same file (without saving changes).

What I have is a spreadsheet with a list of file pathnames, which is a named
range. A VBA macro opens each Excel file in the list, extracts some data,
then closes each file. Rather than requiring the list be composed of
pathnames, I would like to allow users to specify hyperlinks to files on
other servers.

If you can offer some advice or point me to examples I would greatly
appreciate it.

Thanks,

-- Rev
 
R

Revolvr

Well I think I have this figured out. Here is some code I am using that will
open and close files based on a PathName that can begin with a driver
letter, server(i.e \\server\file...), or a URL. Feel free to comment, I am
sure there are better ways to do this.

Sub HyperOpen(PathRange)
' This will open a file specified by the hyperlink
ActiveWorkbook.FollowHyperlink Address:=PathRange
End Sub

I still need to add some error handling to this routine, but it works OK.
This will open any file given a string path PathRange, even if this is a
drive letter, server, or if the path is actually a URL.

Closing a file is a little trickier since the close method requires just the
file name, so I have to parse the string representing the path to just the
file name, or keep the whole string if the path is a URL.

Function HyperClose(Fname)
' If this is a hyperlink to URL, I do not need to manipulate the address.
' If this is going to a server or mapped drive, I need to get rid of all
' but the file name needs to be truncated to just file name.
If (InStr(1, Fname, "http") = 0) Then
CleanName = CleanFnameR(Fname)
Else
CleanName = Fname
End If
Workbooks(CleanName).Close SaveChanges:=False
End Function


Function CleanFnameR(Fname)
' Removes any sub directories from name. Recursive implementation
CleanFnameR = Fname
ilen = Len(Fname)
xlen = InStr(1, Fname, "\")
If (xlen > 0) Then CleanFnameR = CleanFnameR(Right(Fname, ilen - xlen))
End Function
 
Top